Стандартне використання функцій

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

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

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

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

=DATEDIF(D9;E9;"d") з результатом 856

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

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

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

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

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

=DATEDIF(D5;E5;"m") і результат: 28

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

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

=DATEDIF(D2;E2;"y") і результат: 2

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

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

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

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

=DATEDIF(D17;E17;"md") і результат: 2

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

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

=DATEDIF(D17;E17;"md") і результат: 4

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

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

=DATEDIF(D17;E17;"md") і результат: 5

Тепер потрібно дізнатися кількість днів, що залишилися. Для цього необхідно написати іншу формулу, показану вище. Формула віднімає перший день поточного місяця (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. Відніміть два значення часу.

=E80-D80 і результат: 9:15 AM

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

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. Відніміть два значення.

=E84-D84 і результат 1,041666667

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

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

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

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

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

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

Потрібна додаткова довідка?

Потрібні додаткові параметри?

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

Спільноти допомагають ставити запитання й відповідати на них, надавати відгуки та дізнаватися думки висококваліфікованих експертів.