3 простих способи відняти дати в Excel

У якийсь момент під час роботи в Excel вам потрібно буде знайти кількість днів між двома датами.

На щастя, Excel пропонує кілька способів віднімання дат, щоб отримати різницю в днях між ними.

Є три простих способи, як відняти дати в Excel, це:

1. Просте віднімання
2. Використання функції DAYS().
3. Використання функції NETWORKDAYS().

Є один старіший спосіб, функція DATEDIF(), який Microsoft більше не підтримує. Проте перераховані три методи надійні та точні. Ви швидко знайдете різницю між датами для таких завдань, як графіки проектів або бізнес-аналіз.

До кінця цієї статті ви зможете застосувати кожен метод у своїй роботі, щоб легко відняти дати, щоб отримати кількість днів.

Давайте вникнемо в це.

Зміст

• Розуміння дат у Excel
• 1. Використання простого віднімання для отримання кількості днів
  • Додавання спеціальної мітки
• 2. Використання функції DAYS для обчислення кількості днів
• 3. Використання функції NETWORKDAYS
  • Визначення свят
  • Визначення різних днів для вихідних
• Старіша функція DATEDIF (не рекомендовано)
• Використання поточної дати в розрахунках
• Як відняти місяці або роки, щоб знайти день
• Що робити, якщо дата початку після дати завершення?
• Поради щодо точного віднімання дати
  • Порада 1. Використовуйте правильну формулу
  • Порада 2. Правильно форматуйте дати
  • Порада 3. Облік високосних років
  • Порада 4. Відображайте чітко дати та результати
• Поширені помилки та усунення несправностей
  • Помилка 1: #VALUE!
  • Помилка 2: Неправильні результати
• Висновок

Розуміння дат у Excel

Дати в Excel іноді можуть збивати з пантелику, оскільки вони діють і як числа, і як текст. Це має більше сенсу, коли ви розумієте, що зберігає дати як серійні номери.

Система дат Excel починає відлік з 1 січня 1900 року, якому присвоюється порядковий номер 1. Кожному наступному дню присвоюється наступний номер.

Наприклад, 2 січня 1900 року має порядковий номер 2.

Ймовірно, ви бачите, що це спрощує віднімання двох дат і знаходження днів між ними. Розглянемо рекомендовані способи для цього.

Я пропоную вам запустити новий робочий зошит. Кожен приклад, який я наводжу, є коротким посібником.

На моєму робочому аркуші для зразків дат використовуються клітинки B2 і C2. Ви можете використовувати приклади як шаблони для своєї роботи.

1. Використання простого віднімання для отримання кількості днів

Перший спосіб знайти кількість днів між двома датами — це за допомогою простого віднімання (знак мінус). Це формат:

=Кінець-Початок

Виконайте такі дії:

1. Введіть дві дати в окремі комірки.
2. У новій клітинці запишіть таку формулу:=C2-B2
3. Натисніть enter.

Отримане значення буде кількістю цілих днів між двома датами.

У нашому прикладі я ввів «3 березня 2023» у клітинку B2 і «20 березня 2023» у клітинку C2. Результат розрахунку 17.

Комірка A2 на ілюстрації нижче є просто описом використаного методу.

Додавання спеціальної мітки

Якщо ви хочете відобразити свій результат із спеціальною міткою, скористайтеся такою формулою:

=C2-B2 & "дні"

Ця формула об’єднує розраховану різницю дат посилань на клітинки з текстом «дні». Це забезпечує більш описовий результат.

Наприклад, якщо між двома датами є 17 днів, результат відображатиметься як «17 днів».

2. Використання функції DAYS для обчислення кількості днів

Функція DAYS Excel — це вбудована функція дати, яка обчислює дні між датами. Це формат:

=DAYS(кінець, початок)

Виконайте такі дії:

1. Введіть дві дати в окремі комірки.
2. У новій клітинці запишіть таку формулу:=DAYS(C2, B2)
3. Натисніть enter.

Отримане значення буде кількістю днів між двома датами.

Я використав ті самі дати з попереднього прикладу, щоб перевірити функцію. Отримане число було таким самим.

Результат завжди буде цілим числом. Якщо ви бажаєте, щоб воно відображалося як десяткове число (з нулями в кінці), скористайтеся вкладкою форматування, щоб змінити формат числа.

3. Використання функції NETWORKDAYS

Функція дати NETWORKDAYS обчислює кількість робочих днів між двома датами. Оскільки це робочі дні, розрахунок не включає вихідні та святкові дні.

Це формат:

= NETWORKDAYS(Початок, Кінець, [Свята])

Третій параметр (Свята) необов'язковий. Якщо ви не використовуєте його, формула виключатиме вихідні дні.

Виконайте такі кроки, щоб використати найпростішу формулу без зазначення свят:

1. Введіть дві дати в окремі комірки.
2. У новій клітинці введіть таку формулу:=NETWORKDAYS(B2, C2)
3. Натисніть enter.

Взявши дати, які я використовував у попередньому прикладі (3 січня та 20 січня 2023 року), результат цієї функції Excel дорівнює 14.

Це на три дні менше, ніж за допомогою простого віднімання. Це тому, що в цей проміжок часу є два вихідних (я ще раз перевірив).

Визначення свят

Ви можете надати формулі список свят, які потрібно виключити з розрахунку.

Це можна зробити, ввівши кожне свято в діапазон комірок і вказавши діапазон у параметрі свята.

Це легше зрозуміти на прикладі.

1. Дві дати знаходяться в клітинках B2 і C2.
2. Перша дата свята міститься в клітинці D2, а друга – у клітинці E2.
3. У клітинці F2 використовуйте формулу:=NETWORKDAYS(B2, C2, D2:E2)
4. Натисніть клавішу Enter, щоб побачити результат.

У нашому прикладі результат дорівнює 12. Це на два дні менше, ніж у попередньому розрахунку, оскільки формула знайшла список дат у наданому діапазоні клітинок.

( Я не показую клітинки A1 і A2 на зображенні. Комірка A1 порожня, а A2 містить опис. ).

До речі, якщо у вас є довгий список святкових дат і ви не впевнені, що він містить дублікати, у відео нижче показано, як перевірити дані.

Він також показує, як видалити дублікати!

Визначення різних днів для вихідних

Зверніть увагу, що NETWORKDAYS використовує суботу та неділю як вихідні дні за замовчуванням для розрахунку.

Якщо у вас різні вихідні, ви можете скористатися функцією NETWORKDAYS.INTL, щоб налаштувати вихідний день.

Якщо ви хочете дізнатися більше про формат, ось документація Microsoft .

Старіша функція DATEDIF (не рекомендовано)

Ви не знайдете функцію DATEDIF у списку вбудованих функцій у поточній версії Microsoft Excel.

Однак використовувати його все ж можна. Microsoft не вказує його в списку, оскільки компанія більше не підтримує цю функцію, за винятком особливих випадків.

Проблема в тому, що він не завжди точний. Тому я рекомендую вам уникати використання цієї функції.

Я опишу це, якщо ви використовуєте старішу версію програмного забезпечення. Ви також можете знайти його в старих шаблонах, з якими ви працюєте. Синтаксис:

= DATEDIF(Початок, Кінець, “d”)

Використання «d» у третьому параметрі повідомляє функції обчислення в днях. Обов’язково помістіть «d» у подвійні лапки.

Як альтернативне використання, «m» обчислює кількість місяців, тоді як «y» означає кількість років.

Ось кроки:

1. Введіть дві дати в сусідні клітинки.
2. У новій клітинці введіть таку формулу: =DATEDIF(Start_Date, End_Date, "d").
3. Натисніть enter.

Це поверне дні між двома датами.

Використовуючи комірки в наших попередніх прикладах, функція має такий вигляд: DATEDIF(B2, C2, “d”).

На відміну від інших функцій, ця показує #NUM! помилка, якщо перша дата стоїть після другої.

Використання поточної дати в розрахунках

Якщо ви хочете, щоб одна з дат у розрахунку була поточною датою, ви отримаєте зручну функцію «Сьогодні».

Ви просто замінюєте одне з посилань на клітинку на TODAY(). У моєму робочому аркуші мені потрібен лише стовпець B для зберігання дат. Ось приклад:

=ДНІ(СЬОГОДНІ(), B2)

Дещо заплутано, еквівалентна функція у VBA має іншу назву. Використовуйте функцію Date(), якщо ви кодуєте макрос.

Як відняти місяці або роки, щоб знайти день

Якщо ви шукаєте дату рівно місяць тому, у вас може виникнути спокуса відняти 30 днів із виводу функції TODAY().

Однак це буде точним лише для підмножини місяців, тому це не дуже хороший метод.

Функція EDATE призначена саме для цієї мети. Синтаксис:

= EDATE(початок, місяці)

Для мого конкретного прикладу рівно місяць тому я б використав:

=EDATE(Сьогодні(), -1)

Це дає мені дату на цілий місяць у минулому. Це найкращий спосіб віднімання місяців.

Якщо вам потрібно відняти роки, функція YEAR обчислює роки від заданої дати.

Що робити, якщо дата початку після дати завершення?

Якщо перша дата більша за другу, рекомендовані методи не сприйматимуть це як помилку. Вони зроблять розрахунок і нададуть від’ємне число.

Наприклад, якщо я помилково поміняв дати в прикладах, результат буде -17.

Завжди корисно запобігати помилкам. Ви можете використовувати функцію IF, щоб перевірити недійсний діапазон дат і замість цього відобразити повідомлення про помилку.

Ось приклад того, як об’єднати функцію IF із функцією DAYS:

=IF(B2 > C2; "Недійсні дати", DAYS(B2; C2; "d"))

Ця формула перевіряє, чи перша дата більша за другу. Якщо це так, відображається текст «Недійсні дати».

В іншому випадку різницю в днях обчислюватиметься, як і раніше.

У прикладі нижче я поміняв дві дати. Функція відображає текст, який я вказав для функції IF().

Поради щодо точного віднімання дати

Працюючи з датами в , важливо забезпечити точність ваших розрахунків.

Ось мої найкращі поради, які допоможуть вам використовувати обчислення дат.

Порада 1. Використовуйте правильну формулу

Я рекомендую вам не використовувати функцію DATEDIF(). Це не завжди надійно.

Замість цього виберіть один із трьох інших методів, описаних у цій статті.

Порада 2. Правильно форматуйте дати

Переконайтеся, що комірки початкової та кінцевої дати відформатовано як дати.

Для цього виконайте такі дії:

1. клацніть правою кнопкою миші на комірці.
2. виберіть «Формат клітинок» (або натисніть ctrl + 1, щоб відкрити діалогове вікно).
3. виберіть «Дата» на вкладці «Число».

Це допоможе уникнути можливих помилок у ваших розрахунках.

Порада 3. Облік високосних років

Майте на увазі, що високосні роки можуть впливати на кількість днів між двома датами.

Excel автоматично враховує високосні роки під час обчислення дат, тому, якщо ви дотримуєтесь правильної формули та форматування, ви повинні отримати точні результати.

Ви можете знайти список високосних років тут .

Порада 4. Відображайте чітко дати та результати

Щоб покращити читабельність даних і результатів, розгляньте можливість використання деяких функцій форматування Excel:

• Відрегулюйте ширину стовпців відповідно до ширини вмісту.
• Вирівняйте дані дати по центру кожної клітинки.
• Застосуйте такі стилі, як жирний або курсив, щоб зробити заголовки стовпців більш чіткими.

Поширені помилки та усунення несправностей

Працюючи з датами в Excel, ви можете зіткнутися з кількома типовими помилками. Ось кілька порад щодо усунення несправностей, які допоможуть вирішити ці проблеми.

Помилка 1: #VALUE!

Ця помилка виникає, коли значення клітинок не розпізнаються як належні дати.

Щоб вирішити цю проблему:

• Переконайтеся, що обидві комірки дати відформатовано як дати.
• Перевірте налаштування мови, щоб правильно тлумачити дати.

Різні локалі мають різні формати дати (наприклад, дд/мм/рр або мм/дд/рр).

Помилка 2: Неправильні результати

Якщо ви отримуєте неправильні результати, врахуйте наступне

• Ще раз перевірте, як ви ввели дати. Переплутування місяця та дня може призвести до помилок у розрахунках.
• Використовуйте мережеві дні, якщо ви хочете рахувати лише робочі дні між двома датами за допомогою функції NETWORKDAYS().
• Послідовно використовуйте той самий спосіб форматування дат на аркуші, щоб уникнути плутанини та помилок.
• Уникайте ручних обчислень, використовуючи методи, описані в цій статті.

Висновок

У цій статті ми дослідили різні методи віднімання дат у Excel і обчислення різниці в днях між двома датами.

Наразі ви маєте впевнено використовувати функцію DAYS, функцію NETWORKDAYS і просте віднімання для отримання точних результатів.

Не забувайте завжди ретельно перевіряти свої дані, оскільки неправильні або недійсні формати можуть призвести до неочікуваних результатів.

Застосовуючи будь-який із розглянутих методів, переконайтеся, що дата початку та дата завершення мають правильний формат, щоб програма Excel розпізнала їх.

Тепер, маючи ці знання, ви можете ефективно відстежувати тривалість проектів, аналізувати тенденції та керувати термінами, серед інших завдань.

Продовжуйте практикуватися та експериментувати з різними форматами дати та функціями, щоб використовувати весь потенціал можливостей дати Excel. Вдалих розрахунків!

Готові підняти свої навички Excel на новий рівень? Перевірте наш