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

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

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

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

=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;"y") і результат: 2

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

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

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

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

3. Скористайтеся іншою формулою, щоб знайти кількість днів.

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

Тепер потрібно знайти кількість днів, що залишилися. Створимо для цього формулу іншого типу, як показано вище. Вона віднімає перший день поточного місяця (1.5.2016) від дати завершення періоду в клітинці E17 (6.5.2016). Спочатку функція DATE створює дату, 1.5.2016. Для цього вона використовує значення року та місяця в клітинці E17. Число 1 відповідає першому дню цього місяця. Функція DATE повертає результат 1.5.2016. Потім формула віднімає це значення від дати завершення періоду в клітинці E17, тобто 6.5.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 (). У такому випадку Excel використовуватиме поточну дату комп’ютера. Зверніть увагу, що це значення зміниться, якщо відкрити файл знову в інший день.

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

Зверніть увагу, що цю статтю створено 6 жовтня 2016 року.

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

Підготовка. Вирішіть, чи слід виключати святкові дні. Якщо це слід зробити, введіть їх список в окремій області аркуша. Укажіть кожну святкову дату в окремій клітинці. Виділіть ці клітинки, виберіть Формули > Визначити ім’я. Ім’я діапазону МоїСвята та натисніть кнопку OK. Потім створіть формулу, виконавши наведені нижче дії.

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

Дата початку в клітинці D53 – 1.1.2016, дата завершення в клітинці E53 – 31.12.2016

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

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

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

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

Примітка. В Excel 2007 немає функції NETWORKDAYS.INTL. Однак є NETWORKDAYS. В Excel 2007 наведений вище приклад має виглядати ось так: =NETWORKDAYS(D53;E53). Не вказуйте 1, тому що функція NETWORKDAYS виконує обчислення з розрахунку, що субота й неділя – вихідні.

3. За необхідності змініть 1 на інше значення.

Список IntelliSense, у якому 2 – неділя, понеділок; 3 – понеділок, вівторок і так далі

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

Якщо ви використовуєте Excel 2007, пропустіть цей крок. В Excel 2007 використовується функція NETWORKDAYS, яка завжди виконує обчислення з розрахунку, що вихідні дні – це субота й неділя.

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

=NETWORKDAYS.INTL(D53;E53;1;МоїСвята) і результат: 252

Якщо ви назвали діапазон святкових днів, як описано в розділі "Підготовка" вище, введіть це ім’я в кінці як показано нижче. Якщо у вас немає святкових днів, можете прибрати крапку з комою та "МоїСвята". Якщо ви використовуєте Excel 2007, наведений вище приклад має виглядати ось так: =NETWORKDAYS(D53;E53;МоїСвята).

Порада. Щоб не посилатися на ім’я діапазону святкових днів, можна ввести натомість сам діапазон, наприклад D35:E39. Крім того, можна ввести кожен святковий день у формулі. Наприклад, якщо святкові дні – 1 та 2 січня 2016 р., їх потрібно ввести ось так: =NETWORKDAYS.INTL(D53;E53;1;{"1.1.2016","2.1.2016"}). В Excel 2007, формула матиме такий вигляд: =NETWORKDAYS(D53;E53;{"1.1.2016","1.2.2016"})

Щоб обчислити час, що минув, можна відняти одне значення часу від іншого. Спочатку введіть в одній клітинці час початку, а в іншій – час завершення. Час слід вводити повністю, включно з годинами, хвилинами та пробілом перед 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".

Діалогове вікно "Формат клітинок", пункт "Дата", тип "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

Інші функції дати й часу

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

Отримуйте нові функції раніше за інших
Приєднайтеся до оцінювачів Office

Ця інформація корисна?

Дякуємо за ваш відгук!

Дякуємо за відгук! Схоже, вам може стати в нагоді допомога одного з наших спеціалістів служби підтримки Office, з яким ми вас можемо з’єднати.

×