Excel розглядає дати як цілі числа. Це означає, що ви можете додавати та віднімати їх, що може бути корисно, щоб дізнатися, скільки днів залишилося до наступного терміну чи події. У цій статті ми використовуватимемо функції Excel ДАТА, РІК, МІСЯЦЬ, ДЕНЬ і СЬОГОДНІ, щоб показати вам, як обчислити кількість днів до наступного дня народження або будь-якої іншої щорічної події.
Excel зберігає дати як цілі числа. За замовчуванням Excel використовує «1» для позначення 01/01/1900, і кожен день після цього стає на один більший. Введіть 01/01/2000 і змініть формат на «Число», і ви побачите, що з’явиться «36526». Якщо від 36526 відняти 1, то можна побачити, що в 20 столітті було 36525 днів. Крім того, ви можете ввести майбутню дату та відняти результат функції СЬОГОДНІ, щоб побачити, скільки днів залишилося до цієї дати від сьогоднішньої.
Короткий огляд функцій, пов’язаних з датою
Перш ніж ми зануримося в деякі приклади, нам потрібно ознайомитися з кількома простими функціями, пов’язаними з датою, включаючи функції Excel СЬОГОДНІ, ДАТА, РІК, МІСЯЦЬ і ДЕНЬ.
СЬОГОДНІ
Синтаксис: =TODAY()
Результат: поточна дата
ДАТА
Синтаксис: =DATE(рік, місяць, день)
Результат: дата, визначена введеним роком, місяцем та днем
РІК
Синтаксис: =YEAR(дата)
Результат: рік введеної дати
МІСЯЦЬ
Синтаксис: =MONTH(дата)
Результат: числовий місяць введеної дати (від 1 до 12)
ДЕНЬ
Синтаксис: =DAY(дата)
Результат: день місяця введеної дати
Деякі приклади розрахунків
Ми розглянемо три події, які відбуваються щорічно в один і той же день, обчислимо дату їх наступного виникнення та визначимо кількість днів від цього моменту до наступного.
Ось наші зразки даних. Ми налаштували чотири стовпці: «Подія», «Дата», «Наступний_випадок» і «Дні_до_наступного». Ми ввели дати випадкової дати народження, дату сплати податків у США та Хеллоуїн. Такі дати, як дні народження, ювілеї та деякі свята, припадають на певні дні кожного року і добре працюють у цьому прикладі. Інші свята, як-от День подяки, припадають на певний день тижня певного місяця; цей приклад не охоплює такі типи подій.
Існує два варіанти заповнення стовпця «Наступний_випадок». Ви можете ввести кожну дату вручну, але кожен запис потрібно буде вручну оновлювати в майбутньому, коли дата мине. Замість цього давайте напишемо формулу оператора IF, щоб Excel міг виконувати роботу за вас.
Давайте подивимося на день народження. Ми вже знаємо місяць =МІСЯЦ(F3) і день =ДЕНЬ(F3) наступної події. Це легко, але як щодо року? Нам потрібен Excel, щоб знати, настав день народження в цьому році вже чи ні. Спочатку нам потрібно розрахувати дату народження в поточному році за цією формулою:
=DATE(YEAR(TODAY()),MONTH(F3),DAY(F3))
Далі, нам потрібно знати, чи ця дата вже пройшла, і ви можете порівняти цей результат із TODAY(), щоб дізнатися. Якщо це липень, а день народження припадає на вересень, то наступна подія буде в поточному році, показана як =YEAR(TODAY()) . Якщо це грудень, а день народження припадає на травень, то наступна подія буде в наступному році, тож =YEAR(TODAY())+1 дасть наступний рік. Щоб визначити, що використовувати, ми можемо використовувати оператор ‘IF’:
=IF(DATE(YEAR(TODAY()),MONTH(F3),DAY(F3))>=TODAY(),YEAR(TODAY()),YEAR(TODAY())+1)
Тепер ми можемо об’єднати результати оператора IF з МІСЯЦЕМ і ДЕНЬМ дня народження, щоб визначити наступне подія. Введіть цю формулу в клітинку G3:
=DATE(IF(DATE(YEAR(TODAY()),MONTH(F3),DAY(F3))>=TODAY(),YEAR(TODAY()),YEAR(TODAY())+1),MONTH(F3),DAY(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.
Тепер ми можемо легко визначити кількість днів до наступної події, віднімаючи результат функції TODAY() з результатів Next_Occurrence, які ми щойно обчислили. Введіть таку формулу в клітинку H3:
=G3-TODAY()
Натисніть Enter, щоб побачити результат, а потім заповніть цю формулу в клітинки нижче, виділивши клітинки та натиснувши Ctrl+D.
Ви можете зберегти робочий зошит із формулами в цьому прикладі, щоб відстежувати, чий день народження наступний, або знати, скільки днів у вас залишилося, щоб закінчити костюм на Хеллоуїн. Кожного разу, коли ви використовуєте книгу, вона буде перераховувати результати на основі поточної дати, оскільки ви використовували функцію СЬОГОДНІ().
І так, це досить конкретні приклади, які можуть бути вам корисними чи ні. Але вони також служать для ілюстрації того, що ви можете робити з функціями, пов’язаними з датою в Excel.