Кількість днів між датами в EXCEL – Як порахувати з функцією РАЗНДАТ ()
Для обчислень тривалості часових інтервалів найзручніше використовувати недокументовані функцію РАЗНДАТ ( ) , англійський варіант DATEDIF ().
Функції РАЗНДАТ ( ) немає в довідці EXCEL2007 і в Майстрі функцій ( SHIFT + F 3 ), але вона працює, хоча і не без огріх.
Синтаксис функції:
РАЗНДАТ (початкова_дата; кінцева_дата; спосіб_виміру)
Аргумент початкова_дата повинен бути раніше аргументу кінцева_дата .
Аргумент спосіб_виміру визначає, як і в яких одиницях буде вимірюватися інтервал між датами початку та закінчення. Цей аргумент може приймати наступні значення:
| значення | опис |
| “D” | різниця в днях |
| “M” | різниця в повних місяцях |
| “Y” | різниця в повних роках |
| “Ym” | різниця в повних місяцях без урахування років |
| “Md” | різниця в днях без урахування місяців і років УВАГА! Функція для деяких версій EXCEL повертає помилкове значення, якщо день початкової дати більше дня кінцевої дати (наприклад, в EXCEL 2007 при порівнянні дат 28.02.2009 і 01.03.2009 результат буде 4 дні, а не 1 день). Уникайте використання функції з цим аргументом. Альтернативна формула приведена нижче. |
| “Yd” | різниця в днях без урахування років УВАГА! Функція для деяких версій EXCEL повертає помилкове значення. Уникайте використання функції з цим аргументом. |
Нижче наведено докладний опис всіх 6 значень аргументу спосіб_виміру, а також альтернативних формул (функцію РАЗНДАТ () можна замінити іншими формулами (правда досить громіздкими). Це зроблено в ФАЙЛІ ПРИКЛАДУ ).
У файлі прикладу значення аргументу початкова_дата поміщена в осередку А2 , а значення аргументу кінцева_дата– в осередку В2 .
1. Різниця в днях ( “d”)
Формула = РАЗНДАТ (A2; B2; “d”) поверне просту різницю в днях між двома датами.
Приклад 1: початкова_дата 25.02.2007, кінцева_дата 26.02.2007 Результат: 1 (день).
Цей приклад показиват, що при підрахунку стажу необхідно використовувати функцію РАЗНДАТ () з обережністю. Очевидно, що якщо співробітник працював 25 і 26 лютого, то відпрацював він 2 дні, а не 1. Те ж відноситься і до розрахунку повних місяців (див. Нижче).
Приклад 2: початкова_дата 01.02.2007, кінцева_дата01.03.2007 Результат: 28 (днів)
Приклад3: початкова_дата 28.02.2008, кінцева_дата01.03.2008 Результат: 2 (дня), тому що 2008 рік – високосний
Ця формула може бути замінена простим виразом = ЦІЛЕ (B2) -метою (A2) . Функція ЦІЛЕ () округлює значення до меншого цілого і використана для того випадку, коли початкові дати введені разом з часом доби ( РАЗНДАТ () ігнорує час, тобто дробову частину числа).
2. Різниця в повні місяці ( “m”)
Формула = РАЗНДАТ (A2; B2; “m”) поверне кількість повних місяців між двома датами.
Приклад 1: початкова_дата 01.02.2007, кінцева_дата01.03.2007 Результат: 1 (місяць)
Приклад 2: початкова_дата 01.03.2007, кінцева_дата31.03.2007 Результат: 0
При розрахунку стажу, вважається, що співробітник відпрацював усі дні місяця – відпрацював 1 повний місяць. Функція РАЗНДАТ () так не вважає!
Приклад3: початкова_дата 01.02.2007, кінцева_дата01.03.2009 Результат: 25 місяців
Формула може бути замінена альтернативною виразом: = 12 * (ГОД (B2) – ГОД (A2)) – (МЕСЯЦ (A2) – Месяц (B2)) – (ДЕНЬ (B2) <ДЕНЬ (A2))
Увага : У довідці MS EXCEL (див. Розділ Обчислення віку) є крива формула для обчислення кількості місяці між 2-ма датами:
= (ГОД (ТДАТУ ()) – ГОД (A3)) * 12 + МЕСЯЦ (ТДАТУ ()) – МЕСЯЦ (A3)
Якщо замість функції ТДАТУ () – поточна дата використовувати дату 31.10.1961, а в А3 ввести 01.11.1962, то формула поверне 13, хоча фактично минуло 12 місяців і 1 день (листопад і грудень в 1961р. + 10 місяців в 1962р.) .
3. Різниця в повних роках ( “y”)
Формула = РАЗНДАТ (A2; B2; “y”) поверне кількість повних років між двома датами.
Приклад 1: початкова_дата 01.02.2007, кінцева_дата01.03.2009 Результат: 2 (року)
Приклад 2: початкова_дата 01.04.2007, кінцева_дата01.03.2009 Результат: 1 (рік)
Детальніше читайте в статті Повний вік або стаж .
Формула може бути замінена альтернативною виразом: = ЕСЛИ (ДАТА (РІК (B2); МІСЯЦЬ (A2); ДЕНЬ (A2)) <= B2; РІК (B2) -рік (A2); РІК (B2) -рік (A2) -1)
4. Різниця в повних місяцях без урахування років ( “ym”)
Формула = РАЗНДАТ (A2; B2; “ym”) поверне кількість повних місяців між двома датами без урахування років (див. Приклади нижче).
Приклад 1: початкова_дата 01.02.2007, кінцева_дата01.03.2009 Результат: 1 (місяць), тому що порівнюються кінцева дата 01.03.2009 і модифікована початкова дата 01.02. 2009 (рік початкової дати замінюється роком кінцевої дати, тому що 01.02 менше ніж 01.03)
Приклад 2: початкова_дата 01.04.2007, кінцева_дата01.03.2009 Результат: 11 (місяців), тому що порівнюються кінцева дата 01.03.2009 і модифікована початкова дата 01.04. 2008 (рік початкової дати замінюється роком кінцевої дати за вирахуванням 1 року , тому що 01.04 більше ніж 01.03)
Вся ця вакханалія потрібна, наприклад, для підрахунку скільки повних днів, місяців і років пройшло з певної дати до сьогоднішнього дня.
Формула може бути замінена альтернативною виразом: = ОСТАТ (C7; 12) В осередку С7 повинна міститися різниця в повні місяці (див. П.2).
5. Різниця в днях без урахування місяців і років ( “md”)
Формула = РАЗНДАТ (A2; B2; “md”) поверне кількість днів між двома датами без урахування місяців і років. Використовувати функцію РАЗНДАТ () з цим аргументом не рекомендується (див. Приклади нижче).
Приклад 1: початкова_дата 01.02.2007, кінцева_дата06.03.2009 результат1: 5 (днів), тому що порівнюються кінцева дата 06.03.2009 і модифікована початкова дата 01. 03 . 2009 (рік і місяць початкової дати замінюється роком і місяцем кінцевої дати, тому що 01 менше ніж 06)
Приклад 2: початкова_дата 28.02.2007, кінцева_дата28.03.2009 Результат2: 0, тому що порівнюються кінцева дата 28.03.2009 і модифікована початкова дата 28. 03 . 2009 (рік і місяць початкової дати замінюється роком і місяцем кінцевої дати)
Приклад3: початкова_дата 28.02.2009, кінцева_дата01.03.2009 Результат3: 4 (дня) – абсолютно незрозумілий і НЕПРАВИЛЬНИЙ результат. Відповідь має бути = 1. Більш того, результат обчислення залежить від версії EXCEL.
Версія EXCEL 2007 за SP3.
Результат – 143 дні! Більше ніж днів у місяці!
Версія EXCEL 2007: Додати
Різниця між 28.02.2009 та 01.03.2009 – 4 дні!
Причому в EXCEL 2003 зі SP3 формула повертає вірний результат 1 день. Для значень 31.12.2009 і 01.02.2010 результат взагалі негативний (-2 дня)!
Не раджу використовувати формулу з вищевказаним значенням аргументу. Формула може бути замінена альтернативною виразом: = ЕСЛИ (ДЕНЬ (A2)> ДЕНЬ (B2); ДЕНЬ (КОНМЕСЯЦА (ДАТАМЕС (B2; -1); 0)) – ДЕНЬ (A2) + ДЕНЬ (B2); ДЕНЬ (B2) -день (A2))
Дана формула лише еквівалетное (в більшості випадків) вираз для РАЗНДАТ () з параметром md. Про коректності цієї формули читайте в розділі “Ще раз про кривизну РАЗНДАТ ()” нижче.
6. Різниця в днях без урахування років ( “yd”)
Формула = РАЗНДАТ (A2; B2; “yd”) поверне кількість днів між двома датами без урахування років. Використовувати її не рекомендується з причин, викладених у попередньому пункті.
Результат, що повертається формулою = РАЗНДАТ (A2; B2; “yd”) залежить від версії EXCEL.
Формула може бути замінена альтернативною виразом: = ЕСЛИ (ДАТА (РІК (B2); МІСЯЦЬ (A2); ДЕНЬ (A2))> B2; B2-ДАТА (РІК (B2) -1; МІСЯЦЬ (A2); ДЕНЬ (A2) ); B2-ДАТА (РІК (B2); МІСЯЦЬ (A2); ДЕНЬ (A2)))
Ще раз про кривизну РАЗНДАТ ()
Знайдемо різницю дат 16.03.2015 і 30.01.15. Функція РАЗНДАТ () з параметрами md і ym підрахує, що різниця складає 1 місяць і 14 днів. Чи так це насправді?
Маючи формулу, еквівалентну РАЗНДАТ () , можна зрозуміти хід обчислення. Очевидно, що в нашому випадку кількість повних місяців між датами = 1, тобто весь лютий. Для обчислення днів, функція знаходить кількість днів в попередньому місяці щодо кінцевої дати, тобто 28 (кінцева дата належить березня, попередній місяць – лютий, а в 2015р. В лютому було 28 днів). Після цього забирає день початку і додає день кінцевої дати = ДЕНЬ (КОНМЕСЯЦА (ДАТАМЕС (B6; -1); 0)) – ДЕНЬ (A6) + ДЕНЬ (B6) , тобто 28-30 + 16 = 14. На наш погляд, між датами все ж 1 повний місяць і всі дні березня, тобто 16 днів, а не 14! Ця помилка проявляється, коли в попередньому місяці щодо кінцевої дати, днів менше, ніж за п’ять днів початкової дати. Як вийти з цієї ситуації?
Модифікуємо формулу для розрахунку днів різниці без урахування місяців і років:
= ЕСЛИ (ДЕНЬ (A18)> ДЕНЬ (B18); ЕСЛИ ((ДЕНЬ (КОНМЕСЯЦА (ДАТАМЕС (B18; -1); 0)) – ДЕНЬ (A18)) <0; ДЕНЬ (B18); ДЕНЬ (КОНМЕСЯЦА (ДАТАМЕС (B18; -1); 0)) – ДЕНЬ (A18) + ДЕНЬ (B18)); ДЕНЬ (B18) -день (A18))
При застосуванні нової функції необхідно враховувати, що різниця в днях буде однаковою для декількох початкових дат (див. Малюнок вище, дати 28-31.01.2015). В інших випадках формули еквівалентні. Яку формулу застосовувати? Це вирішувати користувачеві в залежності від умови задачі.
