Excel оперує датами як числовими значеннями. Це відкриває можливості для математичних операцій, таких як додавання і віднімання, що є корисним при визначенні кількості днів, що залишилися до певної події або терміну. У цьому посібнику ми розглянемо, як використовувати функції Excel ДАТА, РІК, МІСЯЦЬ, ДЕНЬ та СЬОГОДНІ для обчислення кількості днів до наступного дня народження чи будь-якої іншої щорічної події.
В Excel дати зберігаються у вигляді цілих чисел. За умовчанням, 1 січня 1900 року відповідає числу 1, і кожен наступний день збільшує це значення на одиницю. Наприклад, якщо ввести 1 січня 2000 року і змінити формат комірки на “Числовий”, ви побачите число 36526. Віднімаючи 1 від цього числа, можна встановити, що 20 століття нараховувало 36525 днів. Також, ви можете обчислити кількість днів до майбутньої дати, віднімаючи від неї значення, отримане за допомогою функції СЬОГОДНІ.
Огляд основних функцій для роботи з датами
Перш ніж перейти до практичних прикладів, давайте розглянемо основні функції, пов’язані з датами в Excel: СЬОГОДНІ, ДАТА, РІК, МІСЯЦЬ та ДЕНЬ.
СЬОГОДНІ
Синтаксис: =СЬОГОДНІ()
Результат: Повертає поточну дату.
ДАТА
Синтаксис: =ДАТА(рік; місяць; день)
Результат: Створює дату, використовуючи вказані рік, місяць і день.
РІК
Синтаксис: =РІК(дата)
Результат: Витягує рік з вказаної дати.
МІСЯЦЬ
Синтаксис: =МІСЯЦЬ(дата)
Результат: Повертає номер місяця (від 1 до 12) зі вказаної дати.
ДЕНЬ
Синтаксис: =ДЕНЬ(дата)
Результат: Витягує день місяця зі вказаної дати.
Практичні приклади обчислень
Розглянемо на прикладі трьох щорічних подій, як обчислити дату їх наступного настання та кількість днів до них.
У нас є чотири стовпці: “Подія”, “Дата”, “Наступна_дата” і “Днів_до_наступної”. Ми ввели дати дня народження, податкової сплати у США та Хелловіну. Цей приклад добре підходить для дат, які щороку припадають на одне й те ж число (дні народження, річниці). Однак, він не підходить для подій, що припадають на певний день тижня певного місяця (наприклад, День подяки).
Для заповнення стовпця “Наступна_дата” є два способи. Можна вводити дати вручну, але їх доведеться оновлювати кожного разу, коли подія пройде. Замість цього скористаємося формулою IF, щоб автоматизувати процес.
Розглянемо день народження. Ми знаємо місяць =МІСЯЦЬ(F3) і день =ДЕНЬ(F3) наступної події. Тепер визначимо рік. Потрібно щоб Excel розумів, чи день народження вже настав у цьому році. Для цього розрахуємо дату дня народження в поточному році за формулою:
=ДАТА(РІК(СЬОГОДНІ());МІСЯЦЬ(F3);ДЕНЬ(F3))
Далі потрібно порівняти цю дату з функцією СЬОГОДНІ(), щоб перевірити, чи вже вона минула. Якщо зараз липень, а день народження у вересні, то наступна подія буде в поточному році, =РІК(СЬОГОДНІ()). Якщо ж зараз грудень, а день народження у травні, то вона буде в наступному році, =РІК(СЬОГОДНІ())+1. Для цього можна використати оператор IF:
=IF(ДАТА(РІК(СЬОГОДНІ());МІСЯЦЬ(F3);ДЕНЬ(F3))>=СЬОГОДНІ();РІК(СЬОГОДНІ());РІК(СЬОГОДНІ())+1)
Тепер об’єднаємо результати оператора IF, функцій МІСЯЦЬ і ДЕНЬ для визначення наступної події. Введіть цю формулу у комірку G3:
=ДАТА(IF(ДАТА(РІК(СЬОГОДНІ());МІСЯЦЬ(F3);ДЕНЬ(F3))>=СЬОГОДНІ();РІК(СЬОГОДНІ());РІК(СЬОГОДНІ())+1);МІСЯЦЬ(F3);ДЕНЬ(F3))
=TODAY(),YEAR(TODAY()),YEAR(TODAY())+1),MONTH(F3),DAY(F3))” в клітинку F3′ ширина=”650″ висота=”250″ onload=” pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);” onerror=”this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);”>
Натисніть Enter, щоб побачити результат. (Оскільки ця стаття була написана наприкінці січня 2019 року, дати будуть застарілі.)
Заповніть цю формулу в клітинки нижче, виділивши їх і натиснувши Ctrl+D.
Тепер визначимо кількість днів до наступної події, віднімаючи результат функції СЬОГОДНІ() від “Наступної_дати”. Введіть таку формулу в комірку H3:
=G3-СЬОГОДНІ()
Натисніть Enter, щоб побачити результат, а потім заповніть формулу в інші клітинки за допомогою Ctrl+D.
Збережіть файл Excel із цими формулами, щоб відстежувати, чий день народження наступний, або скільки днів залишилося до Хелловіну. При кожному відкритті файлу результати автоматично оновлюватимуться на основі поточної дати завдяки функції СЬОГОДНІ().
Ці приклади є досить специфічними, але вони демонструють можливості функцій для роботи з датами в Excel.