Підручник Excel: обчислення дати й часу (обчислення різниці, віку, додавання/віднімання)

У Excel часто використовується обчислення дати й часу, наприклад обчислення різниці між двома датами/часами, додавання або віднімання дати й часу, отримання віку на основі дати народження тощо. Тут, у цьому підручнику, наведено майже сценарії обчислення дати й часу та надано відповідні методи для вас.

Навігація в цьому посібнику

1. Обчисліть різницю між двома датами/часами 1.1 Обчисліть різницю в день/місяць/рік між двома датами 1.11 Обчислити різницю між двома датами в днях/місяцях/роках/тижнях 1.12 Обчислення місяців ігнорує роки та дні між двома датами 1.13 Обчислення днів ігнорує роки та місяці між двома датами 1.14 Обчисліть різницю між двома датами та поверніть роки, місяці та дні 1.15 Обчисліть різницю між датою та сьогоднішнім днем 1.16 Розрахуйте робочі дні з вихідними або без вихідних між двома датами 1.17 Розрахунок вихідних між двома датами 1.18 Розрахувати конкретний день тижня між двома датами 1.19 Обчисліть кількість днів, що залишилися в місяці/році

1.2 Обчисліть різницю між двома часами 1.21 Обчисліть різницю між двома часами 1.22 Обчисліть різницю між двома часами в годинах/хвилинах/секундах 1.23 Розраховуйте різницю в годинах лише між двома часами (не більше 24 годин) 1.24 Обчисліть різницю в хвилинах лише між двома часами (не більше 60 хвилин) 1.25 Обчислити різницю в секундах лише між двома часами (не більше 60 секунд) 1.26 Обчислити різницю між двома часами та повернути години, хвилини, секунди 1.27 Обчисліть різницю між двома датами 1.28 Обчислити різницю в часі в мілісекундах 1.29 Розрахунок робочого часу між двома датами, за винятком вихідних

1.3 Обчисліть різницю між двома датою та часом Kutools for Excel 1.31 Обчисліть різницю між двома датами за допомогою Data & Time Helper 1.32 Обчислення різниці у вихідні/робочі дні/конкретні дні тижня між двома датами за допомогою Formula Helper

1.4 Об’єднайте два стовпці, якщо вони пусті 1.41 Використання функції IF 1.42 Використання VBA

2. Додайте або відніміть дату та час 2.1 Додайте або відніміть дні/місяці/роки/тижні/робочі дні до дати 2.11 Додавання або віднімання днів до дати 2.12 Додавання або віднімання місяців до дати 2.13 Додавання або віднімання років до дати 2.14 Додавання або віднімання тижнів до дати 2.15 Додайте або відніміть робочі дні, включаючи або виключаючи свята 2.16 Додавання або віднімання конкретного року, місяця, днів до дати

2.2 Додайте або відніміть години/хвилини/секунди до часу 2.21 Додавання або віднімання годин/хвилин/секунд до дати й часу 2.22 Сума разів за 24 години 2.23 Додайте робочі години до дати, за винятком вихідних і святкових днів

2.3 Додайте або відніміть дату/час Kutools for Excel

2.4 Розширення 2.41 Позначте або виділіть, якщо термін дії минув 2.42 Повертає кінець поточного місяця/перший день наступного місяця

3. Розрахувати вік 3.1 Розрахувати вік на основі дати 3.11 Обчисліть вік на основі вказаної дати народження 3.12 Обчисліть вік у форматі років, місяців і днів відповідно до дати народження 3.13 Обчисліть вік за датою народження до 1/1/1900

3.2 Обчисліть вік за народженням за допомогою Kutools for Excel

3.3 Обчислити вік або отримати дату народження на основі серії номерів 3.31 Отримати день народження з ідентифікаційного номера 3.32 Розрахувати вік за ідентифікаційним номером

 

У цьому підручнику я створив кілька прикладів, щоб пояснити методи, ви можете змінити посилання на свої потреби, коли використовуєте наведений нижче код VBA або формули

---

1. Обчисліть різницю між двома датами/часами

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

1.11 Обчислити різницю між двома датами в днях/місяцях/роках/тижнях

Функцію Excel DATEDIF можна використовувати для швидкого обчислення різниці між двома датами в днях, місяцях, роках і тижнях.

Натисніть, щоб дізнатися більше про ДАТИФІКОВАНО функція

Різниця днів між двома датами

Щоб отримати різницю в днях між двома датами в клітинках A2 і B2, скористайтеся такою формулою

=DATEDIF(A2,B2,"d")

прес вводити ключ, щоб отримати результат.

Різниця в місяцях між двома датами

Щоб отримати різницю в місяцях між двома датами в клітинках A5 і B5, скористайтеся такою формулою

=DATEDIF(A5,B5,"m")

прес вводити ключ, щоб отримати результат.

Роки різниці між двома датами

Щоб отримати різницю в роках між двома датами в клітинках A8 і B8, скористайтеся такою формулою

=DATEDIF(A8,B8,"y")

прес вводити ключ, щоб отримати результат.

Різниця в тижнях між двома датами

Щоб отримати різницю в тижнях між двома датами в клітинках A11 і B11, скористайтеся такою формулою

=DATEDIF(A11,B11,"d")/7

прес вводити ключ, щоб отримати результат.

Примітка:

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

2) Якщо ви використовуєте наведену вище формулу для отримання різниці тижнів, вона може повернутися до десяткового числа. Якщо ви хочете отримати ціле число тижня, ви можете додати функцію ROUNDDOWN перед цим, як показано нижче, щоб отримати цілу різницю тижнів:

=ROUNDDOWN(DATEDIF(A11,B11,"d")/7,0)

1.12 Обчислення місяців ігнорує роки та дні між двома датами

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

=DATEDIF(A2,B2,"ym")

прес вводити ключ, щоб отримати результат.

A2 — дата початку, а B2 — дата завершення.

1.13 Обчислення днів ігнорує роки та місяці між двома датами

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

=DATEDIF(A5,B5,"md")

прес вводити ключ, щоб отримати результат.

A5 — дата початку, а B5 — дата завершення.

1.14 Обчисліть різницю між двома датами та поверніть роки, місяці та дні

Якщо ви хочете отримати різницю між двома датами та повернути xx років, xx місяців і xx днів, як показано на знімку екрана нижче, тут також надається формула.

=DATEDIF(A8, B8, "y") &" years, "&DATEDIF(A8, B8, "ym") &" months, " &DATEDIF(A8, B8, "md") &" days"

прес вводити ключ, щоб отримати результат.

A8 — дата початку, а B8 — дата завершення.

1.15 Обчисліть різницю між датою та сьогоднішнім днем

Щоб автоматично обчислити різницю між датою та сьогоднішнім днем, просто змініть end_date у наведених вище формулах на TODAY(). Ось для прикладу обчислення різниці в днях між минулою датою та сьогоднішньою датою.

=DATEDIF(A11,TODAY(),"d")

прес вводити ключ, щоб отримати результат.

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

=DATEDIF(TODAY(),A14,"d")

Зауважте, що початкова_дата має бути меншою за кінцеву дату у функції DATEDIF, інакше вона поверне значення #NUM! значення помилки.

1.16 Розрахуйте робочі дні з вихідними або без вихідних між двома датами

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

У цій частині ви будете використовувати функцію NETWORKDAYS.INTL:

Натисніть NETWORKDAYS.INTL знати його аргументи та використання.

Рахувати робочі дні зі святами

Щоб підрахувати робочі дні зі святами між двома датами в клітинках A2 і B2, скористайтеся такою формулою:

=NETWORKDAYS.INTL(A2,B2)

прес вводити ключ, щоб отримати результат.

Рахувати робочі дні без свят

Щоб підрахувати робочі дні зі святами між двома датами в клітинках A2 і B2, за винятком свят у діапазоні D5:D9, скористайтеся такою формулою:

=NETWORKDAYS.INTL(A5;B5,1;5;D9:DXNUMX)

прес вводити ключ, щоб отримати результат.

Примітка:

У наведених вище формулах вони приймають суботу та неділю як вихідні. Якщо у вас різні вихідні дні, змініть аргумент [вихідні], як вам потрібно.

1.17 Розрахунок вихідних між двома датами

Якщо ви хочете порахувати кількість вихідних між двома датами, функції SUMPRODUCT або SUM можуть зробити вам послугу.

Щоб підрахувати вихідні (субота та неділя) між двома датами в клітинках A12 і B12:

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A12&":"&B12)),2)>5))

Or

=SUM(INT((WEEKDAY(A12-{1,7})+B12-A12)/7))

прес вводити ключ, щоб отримати результат.

1.18 Розрахувати конкретний день тижня між двома датами

Щоб підрахувати кількість певного дня тижня, наприклад понеділка, між двома датами, вам може допомогти комбінація функцій INT і WEEKDAY.

Комірки A15 і B15 – це дві дати, між якими потрібно відраховувати понеділок, скористайтеся такою формулою:

=INT((WEEKDAY(A15- 2)-A15 +B15)/7)

прес вводити ключ, щоб отримати результат.

Змініть номер дня тижня у функції WEEKDAY, щоб рахувати інший день тижня:

1 – неділя, 2 – понеділок, 3 – вівторок, 4 – середа, 5 – четвер, 6 – п’ятниця, 7 – субота)

1.19 Обчисліть кількість днів, що залишилися в місяці/році

Іноді вам може знадобитися дізнатися кількість днів, що залишилися в місяці або році на основі наданої дати, як показано на знімку екрана нижче:

Отримати решту днів у поточному місяці

Натисніть ЕМОНТ знати аргумент і вживання.

Щоб отримати решту днів поточного місяця в клітинці A2, скористайтеся такою формулою:

=EOMONTH(A2,0)-A2

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

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

Отримайте решту днів поточного року

Щоб отримати решту днів поточного року в клітинці A2, скористайтеся такою формулою:

=DATE(YEAR(A2),12,31)-A2

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

---

1.21 Обчисліть різницю між двома часами

Щоб отримати різницю між двома часами, ось дві прості формули, які можуть вам допомогти.

Припустимо, що клітинки A2 і B2 містять окремо start_time і end_time, використовуючи такі формули:

=B2-A2

=TEXT(B2-A2,"hh:mm:ss")

прес вводити ключ, щоб отримати результат.

Примітка:

• Якщо ви використовуєте end_time-start_time, ви можете відформатувати результат у інший формат часу, як вам потрібно, у діалоговому вікні «Формат клітинок».
• Якщо ви використовуєте TEXT(end_time-first_time,"time_format"), введіть формат часу, у якому ви хочете отримати результат у формулі, наприклад TEXT(end_time-first_time,"h") повертає 16.
• Якщо end_time менший за start_time, обидві формули повертають значення помилок. Щоб вирішити цю проблему, ви можете додати ABS перед цими формулами, наприклад ABS(B2-A2), ABS(TEXT(B2-A2,"hh:mm:ss")), а потім відформатувати результат як час.

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

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

Отримайте різницю в годинах між двома часами

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

=INT((B5-A5)*24)

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

Якщо ви хочете отримати різницю в десяткових годинах, використовуйте (end_time-start_time)*24.

Отримайте різницю в хвилинах між двома часами

Щоб отримати різницю в хвилинах між двома часами в A8 і B8, скористайтеся такою формулою:

=INT((B8-A8)*1440)

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

Якщо ви хочете отримати різницю в десяткових хвилинах, використовуйте (end_time-start_time)*1440.

Отримайте різницю в секундах між двома часами

Щоб отримати різницю в секундах між двома часами в A5 і B5, скористайтеся такою формулою:

=(B11-A11)*86400)

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

1.23 Розраховуйте різницю в годинах лише між двома часами (не більше 24 годин)

Якщо різниця між двома часами не перевищує 24 годин, функція HOUR може швидко отримати різницю в годинах між цими двома часами.

Натисніть ЧАС щоб дізнатися більше про цю функцію.

Щоб отримати різницю в годинах між часом у клітинках A14 і B14, скористайтеся цією функцією HOUR:

=HOUR(B14-A14)

прес вводити ключ, щоб отримати результат.

Початковий_час має бути меншим за кінцевий, інакше формула повертає #NUM! значення помилки.

1.24 Обчисліть різницю в хвилинах лише між двома часами (не більше 60 хвилин)

Функція MINUTE може швидко отримати лише різницю в хвилинах між цими двома часами та ігнорувати години та секунди.

Натисніть ПУТІВКИ щоб дізнатися більше про цю функцію.

Щоб отримати лише різницю в хвилинах між часами в клітинках A17 і B17, скористайтеся цією функцією MINUTE:

=MINUTE(B17-A17)

прес вводити ключ, щоб отримати результат.

Початковий_час має бути меншим за кінцевий, інакше формула повертає #NUM! значення помилки.

1.25 Обчислити різницю в секундах лише між двома часами (не більше 60 секунд)

Функція SECOND може швидко отримати єдину різницю в секундах між цими двома часами та ігнорувати години та хвилини.

Натисніть ДРУГИЙ щоб дізнатися більше про цю функцію.

Щоб отримати лише різницю в секундах між часами в клітинках A20 і B20, скористайтеся цією функцією SECOND:

=SECOND(B20-A20)

прес вводити ключ, щоб отримати результат.

Початковий_час має бути меншим за кінцевий, інакше формула повертає #NUM! значення помилки.

1.26 Обчислити різницю між двома часами та повернути години, хвилини, секунди

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

Натисніть TEXT усвідомити аргументи та використання цієї функції.

Щоб обчислити різницю між часом у клітинках A23 і B23, скористайтеся такою формулою:

=TEXT(B23-A23,"h"" hours ""m"" minutes ""s"" seconds""").

прес вводити ключ, щоб отримати результат.

Примітка:

Ця формула також обчислює лише різницю в годинах, що не перевищує 24 години, а end_time має бути більшим за start_time, інакше вона повертає #VALUE! значення помилки.

1.27 Обчисліть різницю між двома датами

Якщо у форматі мм/дд/рррр гг:хх:сс два рази, щоб обчислити різницю між ними, ви можете скористатися однією з наведених нижче формул.

Отримайте різницю в часі між двома датами та поверніть результат у форматі гг:хх:сс

Візьміть дві дати та час у клітинках A2 і B2 як приклад, скористайтеся такою формулою:

=B2-A2

прес вводити ключ, що повертає результат у форматі дати й часу, а потім відформатуйте цей результат як [год]: мм: сс у спеціальній категорії під Номер Вкладка Формат ячеек діалог
 

Отримайте різницю між двома датами та поверніть дні, години, хвилини, секунди

Візьміть дві дати та час у клітинках A5 і B5 як приклад, скористайтеся такою формулою:

=INT(B5-A5) & " Days, " & HOUR(B5-A5) & " Hours, " & MINUTE(B5-A5) & " Minutes, " & SECOND(B5-A5) & " Seconds "

прес вводити ключ, щоб отримати результат.

Примітка: в обох формулах end_datetime має бути більшим за start_datetime, інакше формули повертатимуть значення помилок.

1.28 Обчислити різницю в часі в мілісекундах

По-перше, вам потрібно знати, як відформатувати клітинку, щоб відображати мілісекунди:

Виділіть комірки, які потрібно відображати в мілісекундах, і виберіть правою кнопкою миші Формат ячеек щоб включити Формат ячеек діалог, виберіть виготовлений на замовлення в Категорія список на вкладці Число та введіть це год: мм: сс.000 у текстове поле.

Використовуйте формулу:

Тут, щоб обчислити різницю між двома часами в клітинках A8 і B8, скористайтеся такою формулою:

=ABS(B8-A8)

прес вводити ключ, щоб отримати результат.

1.29 Розрахунок робочого часу між двома датами, за винятком вихідних

Іноді вам може знадобитися підрахувати робочі години між двома датами, за винятком вихідних (субота та неділя).

Тут робочий час становить 8 годин щодня, і щоб обчислити робочий час між двома датами, указаними в клітинках A16 і B16, скористайтеся такою формулою:

=NETWORKDAYS(A16,B16) * 8

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

Додаткові приклади розрахунку робочого часу між двома датами див Отримайте робочі години між двома датами в Excel

---

Якщо у вас є Kutools for Excel встановлено в Excel, 90 відсотків обчислень різниці дати й часу можна швидко вирішити, не запам’ятовуючи жодних формул.

1.31 Обчисліть різницю між двома датами за допомогою Data & Time Helper

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

1. Виберіть комірку, у яку потрібно розмістити обчислений результат, і клацніть Kutools > Помічник формули > Помічник дати та часу.

2. У вискакуванні Помічник дати та часу діалогове вікно, дотримуйтесь наведених нижче налаштувань:

1. перевірити Різниця варіант;
2. Виберіть дату початку та час закінчення Введення аргументів ви також можете безпосередньо ввести дату та час вручну у поле введення або клацнути піктограму календаря, щоб вибрати дату;
3. Виберіть тип результату виведення зі списку, що розкривається;
4. Перегляньте результат у Результат .

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

Порада:

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

Виберіть комірку, куди потрібно розмістити результат, і клацніть Kutools > Помічник формули > Дата, час > Рахуйте дні, години та хвилини між двома датами.

Тоді в Помічник формул у діалоговому вікні вкажіть дату початку та дату завершення, а потім натисніть Ok.

І результат різниці буде показано як дні, години та хвилини.

Натисніть Помічник дати та часу щоб дізнатися більше про використання цієї функції.

Натисніть Kutools for Excel щоб знати всі функції цієї надбудови.

Натисніть Безкоштовне завантаження , Щоб отримати 30-денна безкоштовна пробна версія Kutools for Excel

1.32 Обчислення різниці у вихідні/робочі дні/конкретні дні тижня між двома датами за допомогою Formula Helper

Якщо ви хочете швидко порахувати вихідні, робочі дні або певний день тижня між двома датами, Kutools for ExcelАвтора Помічник формули група може вам допомогти.

1. Виділіть комірку, у яку буде розміщено обчислений результат, клацніть Kutools > Статистичний > Кількість неробочих днів між двома датами/Кількість робочих днів між двома датами/Підрахуйте номер конкретного дня тижня.

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

Щоб підрахувати певний день тижня, ви можете звернутися до примітки, щоб використовувати 1-7 для позначення неділі-суботи.

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

Натисніть Kutools for Excel щоб знати всі функції цієї надбудови.

Натисніть Безкоштовне завантаження , Щоб отримати 30-денна безкоштовна пробна версія Kutools for Excel

---

2. Додайте або відніміть дату та час

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

2.11 Додавання або віднімання днів до дати

Щоб додати або відняти певну кількість днів до дати, ось два різні методи.

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

Спосіб 1 дата+дні

Виберіть клітинку та введіть формулу:

=A+21

прес вводити ключ, щоб отримати результат.

Якщо ви хочете відняти 21 день, виступи змінюють знак плюс (+) на знак мінус (-).

Спосіб 2 Спеціальна вклейка

1. Введіть кількість днів, які потрібно додати, у клітинку, припустимо, у клітинку C2, а потім натисніть Ctrl + C скопіювати його.

2. Потім виберіть дати, до яких потрібно додати 21 день, клацніть правою кнопкою миші, щоб відкрити контекстне меню, і виберіть Спеціальна вставка....

3 В Спеціальна вставка діалогове вікно, перевірте додавати (Якщо ви хочете відняти дні, поставте прапорець Віднімати варіант). Натисніть OK.

4. Тепер вихідні дати змінюються на 5-значні числа, відформатуйте їх як дати.

2.12 Додавання або віднімання місяців до дати

Щоб додати або відняти місяці до дати, можна використовувати функцію EDATE.

Натисніть ЕДАТ вивчити його аргументи та використання.

Припустимо, що до дати в клітинці A6 додано 2 місяців, скористайтеся такою формулою:

=EDATE(A2,6)

прес вводити ключ, щоб отримати результат.

Якщо ви хочете відняти 6 місяців від дати, змініть 6 на -6.

2.13 Додавання або віднімання років до дати

Щоб додати або відняти n років до дати, можна використати формулу, яка поєднує функції ДАТА, РІК, МІСЯЦЬ і ДЕНЬ.

Припустимо, що до дати в клітинці A3 додано 2 роки, скористайтеся такою формулою:

=DATE(YEAR(A2) + 3, MONTH(A2),DAY(A2))

прес вводити ключ, щоб отримати результат.

Якщо ви хочете відняти 3 роки від дати, змініть 3 на -3.

2.14 Додавання або віднімання тижнів до дати

Для додавання або віднімання тижнів до дати використовується загальна формула

Припустимо, що до дати в клітинці A4 додано 2 тижні, скористайтеся такою формулою:

=A2+4*7

прес вводити ключ, щоб отримати результат.

Якщо ви хочете відняти 4 тижні від дати, змініть знак плюс (+) на знак мінус (-).

2.15 Додайте або відніміть робочі дні, включаючи або виключаючи свята

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

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

Додайте робочі дні, включаючи свята

У комірці A2 указана дата, яку ви використовуєте, у комірці B2 міститься кількість днів, які ви хочете додати, скористайтеся такою формулою:

=WORKDAY(A2,B2)

прес вводити ключ, щоб отримати результат.

Додайте робочі дні, за винятком свят

У комірці A5 указана дата, яку ви використовуєте, у комірці B5 міститься кількість днів, які потрібно додати, у діапазоні D5:D8 перераховані свята, скористайтеся такою формулою:

=WORKDAY(A5,B5,D5:D8)

прес вводити ключ, щоб отримати результат.

Примітка:

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

візит WORKDAY.INTL для більш докладної інформації.

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

2.16 Додавання або віднімання конкретного року, місяця, днів до дати

Якщо ви хочете додати певний рік, місяць і дні до дати, формула, яка поєднує функції ДАТА, РІК, МІСЯЦЬ і ДНІ, може зробити вам послугу.

Щоб додати 1 рік, 2 місяці та 30 днів до дати в A11, скористайтеся такою формулою:

=DATE(YEAR(A11)+1,MONTH(A11)+2,DAY(A11)+30)

прес вводити ключ, щоб отримати результат.

Якщо ви хочете відняти, змініть усі знаки плюс (+) на знаки мінус (-).

---

2.21 Додавання або віднімання годин/хвилин/секунд до дати й часу

Тут наведено деякі формули для додавання або віднімання годин, хвилин або секунд до дати та часу.

Додайте або відніміть години до дати й часу

Припустимо додати 3 години до дати й часу (також може бути часом) у клітинці A2, скористайтеся такою формулою:

=A2+3/24

прес вводити ключ, щоб отримати результат.

Додайте або відніміть години до дати й часу

Припустимо додати 15 хвилин до дати та часу (також може бути часом) у клітинці A5, скористайтеся такою формулою:

=A2+15/1440

прес вводити ключ, щоб отримати результат.

Додайте або відніміть години до дати й часу

Припустимо додати 20 секунд до дати й часу (також може бути часом) у клітинці A8, скористайтеся такою формулою:

=A2+20/86400

прес вводити ключ, щоб отримати результат.

2.22 Сума разів за 24 години

Припустімо, що існує таблиця Excel, яка фіксує робочий час усіх співробітників за тиждень, щоб підсумувати загальний робочий час для розрахунку виплат, ви можете використовувати SUM(діапазон) щоб отримати результат. Але загалом підсумковий результат буде показано як час, що не перевищує 24 години, як показано на знімку екрана нижче. Як отримати правильний результат?

Насправді вам просто потрібно відформатувати результат як [гг]:хх:сс.

Клацніть правою кнопкою миші на комірці результату, виберіть Формат ячеек у контекстному меню та в спливаючому Формат ячеек діалогове вікно, виберіть виготовлений на замовлення із списку та введіть [гг]:хх:сс у текстове поле в правій частині клацніть OK.
  

Сумований результат буде відображено правильно.

2.23 Додайте робочі години до дати, за винятком вихідних і святкових днів

Тут наведено довгу формулу для отримання кінцевої дати на основі додавання певної кількості робочих годин до початкової дати без урахування вихідних (субота та неділя) і свят.

У таблиці Excel A11 містить дату початку, а B11 ​​містить робочі години, у клітинках E11 і E13 указано час початку та закінчення роботи, а клітинка E15 містить свято, яке буде виключено.

Будь ласка, використовуйте формулу так:

=WORKDAY(A11,INT(B11/8)+IF(TIME(HOUR(A11),MINUTE(A11),SECOND(A11))+TIME(MOD(B11,8),MOD(MOD(B11,8),1)*60,0)> $E$13,1,0),$E$15)+IF(TIME(HOUR(A11),MINUTE(A11),SECOND(A11))+TIME(MOD(B11,8),MOD(MOD(B11,8),1)*60,0)>$E$13,$E$11 +TIME(HOUR(A11),MINUTE(A11),SECOND(A11))+TIME(MOD(B11,8),MOD(MOD(B11,8),1)*60,0)-$E$13,TIME(HOUR(A11),MINUTE(A11),SECOND(A11)) +TIME(MOD(B11,8),MOD(MOD(B11,8),1)*60,0))

прес вводити ключ, щоб отримати результат.

---

Якщо у вас є Kutools for Excel встановлено, лише один інструмент – Дата й час Довідкаr може вирішити більшість обчислень щодо додавання та віднімання дати та часу.

1. Клацніть клітинку, для якої потрібно вивести результат, і застосуйте цей інструмент, клацнувши Kutools > Помічник формули > Помічник дати й часу.

2 В Помічник дати та часу діалогове вікно, перевірте додавати варіант або Віднімати за потреби, а потім виберіть клітинку або безпосередньо введіть дату й час, у якій потрібно використовувати Введення аргументів потім вкажіть роки, місяці, тижні, дні, години, хвилини та секунди, які потрібно додати або відняти, а потім натисніть Ok. Дивіться знімок екрана:

Ви можете попередньо переглянути обчислений результат у Результат .

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

Натисніть Помічник дати та часу щоб дізнатися більше про використання цієї функції.

Натисніть Kutools for Excel щоб знати всі функції цієї надбудови.

Натисніть Плата завантаження , Щоб отримати 30-денна безкоштовна пробна версія Kutools for Excel

---

2.41 Позначте або виділіть, якщо термін дії минув

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

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

1. Виберіть дати, які потрібно перевірити, а потім натисніть Головна > Умовне форматування > Нове правило.

2 В Нове правило форматування діалог, виберіть За допомогою формули визначте, які клітинки потрібно форматувати в Виберіть тип правила розділ і тип =B2 у поле введення (B2 — це перша дата, яку потрібно перевірити), і натисніть сформований спливати Формат ячеек діалоговому вікні, а потім виберіть інше форматування, щоб виділити дати, що закінчилися, як вам потрібно. Натисніть OK > OK.
  

2.42 Повертає кінець поточного місяця/перший день наступного місяця/a>

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

Отримати кінець поточного місяця

Ось дата створення в комірці B13, скористайтеся такою формулою:

=EOMONTH(B13,0)

прес вводити ключ, щоб отримати результат.

Отримайте 1 число наступного місяця

Ось дата створення в комірці B18, скористайтеся такою формулою:

=EOMONTH(B18,0)+1

прес вводити ключ, щоб отримати результат.

---

3. Розрахувати вік

У цьому розділі наведено методи визначення віку на основі заданої дати або номеру серії.

---

3.11 Обчисліть вік на основі вказаної дати народження

Отримайте вік у десятковому числі на основі дати народження

Натисніть РІК для детальної інформації про його аргументи та використання.

Наприклад, щоб отримати вік на основі списку дат народження в стовпці B2:B9, скористайтеся такою формулою:

=YEARFRAC(B2,TODAY())

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

Порада:

1) Ви можете вказати десятковий знак, як вам потрібно, у Формат ячеек діалог

2) Якщо ви хочете обчислити вік на певну дату на основі заданої дати народження, змініть TODAY() на конкретну дату, укладену в подвійні лапки, наприклад =YEARFRAC(B2,"1/1/2021")

3) Якщо ви хочете отримати вік наступного року на основі дати народження, просто додайте 1 у формулу, наприклад =YEARFRAC(B2,TODAY())+1.

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

Натисніть ДАТИФІКОВАНО для детальної інформації про його аргументи та використання.

Використовуючи наведений вище приклад, щоб отримати вік на основі дат народження у списку в B2:B9, скористайтеся такою формулою:

=DATEDIF(B2,TODAY(),"y")

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

Порада:

1) Якщо ви хочете обчислити вік на певну дату на основі заданої дати народження, змініть СЬОГОДНІ() на конкретну дату, укладену в подвійні лапки, як-от =DATEDIF(B2,"1/1/2021","y") .

2) Якщо ви хочете отримати вік наступного року на основі дати народження, просто додайте 1 у формулу, як-от =DATEDIF(B2,TODAY(),"y")+1.

3.12 Обчисліть вік у форматі років, місяців і днів відповідно до дати народження

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

Щоб отримати вік у роках, місяцях і днях на основі дати народження в клітинці B12, скористайтеся такою формулою:

=DATEDIF(B12,TODAY(),"Y")&" Years, "&DATEDIF(B12,TODAY(),"YM")&" Months, "&DATEDIF(B12,TODAY(),"MD")&" Days"

прес вводити щоб отримати вік, а потім перетягніть маркер автозаповнення вниз до інших клітинок.

Порада:

Якщо ви хочете обчислити вік у певну дату на основі заданої дати народження, змініть TODAY() на конкретну дату, укладену в подвійні лапки, наприклад = =DATEDIF(B12,"1/1/2021","Y")& " Роки, "&DATEDIF(B12,"1/1/2021","YM")&" Місяці, "&DATEDIF(B12,"1/1/2021","MD")&" Дні".

3.13 Обчисліть вік за датою народження до 1/1/1900

У програмі Excel дату до 1/1/1900 не можна ввести як дату й час або правильно обчислити. Але якщо ви хочете обчислити вік відомої людини на основі заданої дати народження (до 1/11900) і дати смерті, вам може допомогти лише код VBA.

1. прес інший + F11 клавіші для ввімкнення Microsoft Visual Basic для додатків вікно та натисніть Insert та виберіть Модулі щоб створити новий модуль.

2. Потім скопіюйте та вставте наведений нижче код у новий модуль.

VBA: Обчисліть вік до 1/1/1900

Public Function AgeFunc(SDate As Variant, EDate As Variant) As Long
'UpdatebyExtendOffice
    Dim xSMonth As Integer
    Dim xSDay As Integer
    Dim xSYear As Integer
    Dim xEMonth As Integer
    Dim xEDay As Integer
    Dim xEYear As Integer
    Dim xAge As Integer
    If Not GetDate(SDate, xSYear, xSMonth, xSDay) Then
        AgeFunc = "Invalid Date"
        Exit Function
    End If
    If Not GetDate(EDate, xEYear, xEMonth, xEDay) Then
        AgeFunc = "Invalid Date"
        Exit Function
    End If
    xAge = xEYear - xSYear
    If xSMonth > xEMonth Then
        xAge = xAge - 1
    ElseIf xSMonth = xEMonth Then
        If xSDay > xEDay Then xAge = xAge - 1
    End If
    If xAge < 0 Then
        AgeFunc = "Invalid Date"
    Else
        AgeFunc = xAge
    End If
End Function
Private Function GetDate(ByVal DateStr As String, Y As Integer, M As Integer, D As Integer) As Boolean
    Dim I As Long
    Dim K As Long
    Y = 0
    M = 0
    D = 0
    GetDate = True
    On Error Resume Next
    I = InStr(1, DateStr, "/")
    M = CLng(Left(DateStr, I - 1))
    D = CLng(Mid(DateStr, I + 1, InStr(I + 1, DateStr, "/") - I - 1))
    Y = CLng(Right(DateStr, Len(DateStr) - InStrRev(DateStr, "/")))
    If M < 1 Or M > 12 Or D < 1 Or D > 31 Or Y < 1 Then
        GetDate = False
    End If
End Function

3. Збережіть код, поверніться на аркуш і виберіть клітинку, щоб розмістити обчислений вік, введіть =AgeFunc(дата народження,дата смерті), у цьому випадку, =AgeFunc(B22,C22), натисніть клавішу Enter, щоб отримати вік. Використовуйте ручку автоматичного заповнення, щоб застосувати цю формулу до інших комірок, якщо потрібно.

Якщо у вас є Kutools for Excel встановлений в Excel, ви можете застосувати Помічник дати та часу інструмент для розрахунку віку.

1. Виберіть клітинку, у яку потрібно розмістити обчислений вік, і клацніть Kutools > Помічник формули > Помічник дати та часу.

2 В Помічник дати та часу діалогове вікно,

• 1) Перевірка вік варіант;
• 2) Виберіть клітинку з датою народження або безпосередньо введіть дату народження або клацніть піктограму календаря, щоб вибрати дату народження;
• 3) Виберіть сьогодні якщо ви хочете обчислити поточний вік, виберіть Вказана дата параметр і введіть дату, якщо ви хочете обчислити вік у минулому чи майбутньому;
• 4) Вказати тип виводу зі списку, що випадає;
• 5) Перегляньте вихідний результат. Натисніть Ok.

Натисніть Помічник дати та часу щоб дізнатися більше про використання цієї функції.

Натисніть Kutools for Excel щоб знати всі функції цієї надбудови.

Натисніть Безкоштовне завантаження , Щоб отримати 30-денна безкоштовна пробна версія Kutools for Excel

---

3.31 Отримати день народження з ідентифікаційного номера

Якщо існує список ідентифікаційних номерів, які використовують перші 6 цифр для запису дати народження, наприклад 920315330 означає, що дата народження – 03, як можна швидко перенести дату народження в інший стовпець?

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

=MID(C2,5,2)&"/"&MID(C2,3,2)&"/"&MID(C2,1,2)

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

Примітка:

У формулі ви можете змінити посилання відповідно до ваших потреб. Наприклад, якщо ідентифікаційний номер відображається як 13219920420392, день народження – 04, ви можете змінити формулу на =MID(C20)&"/"&MID(C1992)&"/ "&MID(C2,8,2), щоб отримати правильний результат.

3.32 Розрахувати вік за ідентифікаційним номером

Якщо існує список ідентифікаційних номерів, які використовують перші 6 цифр для запису дати народження, наприклад 920315330 означає, що дата народження – 03, як можна швидко обчислити вік на основі кожного ідентифікаційного номера в Excel?

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

=DATEDIF(DATE(IF(LEFT(C2,2)>TEXT(TODAY(),"YY"),"19"&LEFT(C2,2),"20"&LEFT(C2,2)),MID(C2,3,2),MID(C2,5,2)),TODAY(),"y")

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

Примітка:

У цій формулі, якщо рік менший за поточний рік, рік вважатиметься таким, що починається з 20, наприклад 200203943 вважатиметься роком 2020; якщо рік більший за поточний, рік вважатиметься таким, що починається з 19, наприклад 920420392 вважатиметься роком 1992.

---

Інші посібники з Excel: