Обчислення різниці між двома датами

Попередження!: В Excel функція DATEDIF використовується для підтримки старіших книг, створених у Lotus 1-2-3. Функція DATEDIF може за певних умов повертати хибні результати. Докладні відомості див. в розділі відомих проблем статті функції DATEDIF .

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

Примітка.: Якщо дата_початку більша за дату_завершення, результат буде #NUM!.

Різниця в днях

У цьому прикладі дата початку в клітинці D9, а дата завершення – в E9. Формула міститься в клітинці F9. "d" повертає кількість повних днів між двома датами.

Різниця в тижнях

=(DATEDIF(D13;E13;"d")/7) і результат: 122,29

У цьому прикладі дата початку в клітинці D13, а дата завершення – в E13. "d" повертає кількість днів. Однак зверніть увагу на /7 в кінці. Кількість днів ділиться на 7, оскільки тиждень складається із 7 днів. Зауважте, що цей результат також потрібно відформатувати як число. Натисніть клавіші Ctrl+1. Потім натисніть Число > Кількість знаків після коми: 2.

Різниця в місяцях

У цьому прикладі дата початку в клітинці D5, а дата завершення – в E5. У формулі "m" повертає кількість повних місяців між двома днями.

Різниця в роках

У цьому прикладі дата початку в клітинці D2, а дата завершення – в E2. "y" повертає кількість повних років між двома датами.

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

Ви також можете обчислити вік чи чийсь стаж. Приклад результату: "2 роки, 4 місяці, 5 днів".

1. Скористайтеся функцією DATEDIF, щоб підрахувати загальну кількість років.

У цьому прикладі дата початку в клітинці D17, а дата завершення – в E17. У формулі "y" повертає кількість повних років між двома датами.

2. Щоб підрахувати місяці, знову скористайтеся функцією DATEDIF з "ym".

В іншій клітинці скористайтеся формулою DATEDIF із параметром "ym". "ym" повертає кількість місяців, що залишилися за останній повний рік.

3. Щоб підрахувати дні, скористайтеся іншою формулою.

Тепер потрібно дізнатися кількість днів, що залишилися. Для цього необхідно написати іншу формулу, показану вище. Формула віднімає перший день поточного місяця (01.05.2016) від дати завершення періоду в клітинці E17 (06.05.2016). Спочатку функція DATE створює дату, 01.05.2016. Для цього вона використовує значення року та місяця в клітинці E17. Число 1 відповідає першому дню цього місяця. Функція DATE повертає результат 01.05.2016. Потім формула віднімає це значення від дати завершення періоду в клітинці E17, тобто 06.05.2016. Результат становить 5 днів.

Попередження!: Не радимо використовувати аргумент "md" для DATEDIF, оскільки він може обчислювати неточні результати.

4. Необов’язково. Об’єднайте три формули в одній.

=DATEDIF(D17,E17,"y")&" р., "&DATEDIF(D17,E17,"ym")&" міс., "&DATEDIF(D17,E17,"md")&" дн." і результат: 2 роки, 4 місяці, 5 днів

Ви можете розмістити всі три обчислення в одній клітинці, як у цьому прикладі. Використовуйте амперсанди, лапки та текст. Це довша формула, але вона об’єднує всі попередні. Порада. Натисніть клавіші Alt+Enter, щоб ввести розриви рядків у формулі. Так її буде простіше читати. Крім того, якщо вся формула не відображається, натисніть клавіші Ctrl+Shift+U.

Завантаження прикладів

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

Інші обчислення дати й часу

Як показано вище, функція DATEDIF обчислює різницю між датами початку та завершення. Однак замість введення певних дат ви також можете використовувати функцію TODAY() у формулі. Коли ви використовуєте функцію TODAY(), Excel застосовує поточну дату на комп’ютері. Пам’ятайте, що дані зміняться, коли файл знову буде відкрито в інший день.

=DATEDIF(TODAY();D28;"y") і результат: 984

Зверніть увагу, що на момент написання цієї статті була дата 6 жовтня 2016 року.

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

Перш ніж почати: Укажіть, чи потрібно виключити дати вихідних. У такому разі введіть список дат вихідних в окремій області або таблиці. Помістіть кожну дату свята в окрему клітинку. Потім виділіть ці клітинки, виберіть Формули > Надання імені. Назвіть діапазон MyHolidays та натисніть кнопку OK. Потім створіть формулу, як описано нижче.

1. Введіть дати початку та завершення.

Дата початку в клітинці D53 – 1/1/2016, дата завершення в клітинці E53 – 12/31/2016

У цьому прикладі дата початку в клітинці D53, а дата завершення – у клітинці E53.

2. В іншій клітинці введіть таку формулу:

=NETWORKDAYS. INTL(D53;E53;1) і результат: 261

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

3. За потреби змініть 1.

Список Intellisense, де показано 2 – неділя, понеділок; 3 – понеділок, вівторок і т.д.

Якщо субота та неділя не вихідні дні, змініть 1 на інше число зі списку IntelliSense. Наприклад, 2 визначає неділю та понеділок як вихідні дні.

4. Введіть назву діапазону вихідних.

=NETWORKDAYS.INTL(D53,E53,1,MyHolidays) і результат: 252

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

Час, що минув, можна обчислити, віднявши один показник від іншого. Спочатку помістіть час початку в одну клітинку, а час завершення – в іншу. Обов’язково введіть повний час, зокрема години, хвилини та пробіл перед AM або PM. Ось як це зробити:

1. Введіть час початку та час завершення.

Дата/час початку – 7:15 AM, дата/час завершення – 4:30 PM

У цьому прикладі час початку в клітинці D80, а час завершення – в E80. Обов’язково введіть години, хвилини та пробіл перед AM або PM.

2. Установіть формат h:mm AM/PM.

Діалогове вікно "Форматувати клітинки", спеціальна команда, тип h:mm AM/PM

Виберіть обидві дати та натисніть клавіші Ctrl+1 (або Зображення піктограми кнопки Command на комп’ютері MAC +1 на комп’ютері Mac). Обов’язково виберіть Спеціальний час > h:mm AM/PM, якщо його ще не встановлено.

3. Відніміть два значення часу.

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

4. Установіть формат h:mm.

Діалогове вікно "Форматувати клітинки", спеціальна команда, тип h:mm

Натисніть клавіші Ctrl+1 (або Зображення піктограми кнопки Command на комп’ютері MAC +1 на комп’ютері Mac). Виберіть Спеціальний час > h:mm, щоб у результаті не включався параметр AM і PM.

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

1. Введіть дві повні дати й час.

Дата початку 1/1/16 1:00 PM; дата завершення 1/2/16 2:00 PM

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

2. Установіть формат 3/14/12 1:30 PM.

Діалогове вікно "Форматувати клітинки", команда Date, тип 3/14/12 1:30 PM

Виберіть обидві клітинки, а тоді натисніть клавіші Ctrl+1 (або Зображення піктограми кнопки Command на комп’ютері MAC +1 на комп’ютері Mac). Потім виберіть Дата > 3/14/12 1:30 PM. Це не та дата, яку ви встановите, а лише зразок формату. Зверніть увагу, що у версіях до Excel 2016 цей формат може мати інший зразок дати, наприклад 3/14/01 1:30 PM.

3. Відніміть два значення.

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

4. Установіть формат [h]:mm.

Натисніть клавіші Ctrl+1 (або Зображення піктограми кнопки Command на комп’ютері MAC +1 на комп’ютері Mac). Виберіть Спеціальний час. У полі Тип введіть [h]:mm.

Пов’язані теми

Функція DATEDIF Функція NETWORKDAYS.INTL NETWORKDAYS Інші функції дати й часу Обчислення різниці між двома часами