Кількість днів між датами в EXCEL – Як порахувати з функцією РАЗНДАТ ()

Для обчислень тривалості часових інтервалів найзручніше використовувати недокументовані функцію РАЗНДАТ ( , англійський варіант DATEDIF ().

Файл прикладу

Функції РАЗНДАТ ( немає в довідці EXCEL2007 і в Майстрі функцій SHIFT ), але вона працює, хоча і не без огріх.

Синтаксис функції:

РАЗНДАТ (початкова_дата; кінцева_дата; спосіб_виміру)

Аргумент початкова_дата повинен бути раніше аргументу кінцева_дата .

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

 

значенняопис
“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. Те ж відноситься і до розрахунку повних місяців (див. Нижче).

Читайте також  ЯК ПЕРЕНЕСТИ ТАБЛИЦЮ З EXCEL В WORD БЕЗ ПОМИЛОК

Приклад 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 дня)!

Читайте також  Список Кращих програм для SSD-дисків на windows

Не раджу використовувати формулу з вищевказаним значенням аргументу. Формула може бути замінена альтернативною виразом: = ЕСЛИ (ДЕНЬ (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). В інших випадках формули еквівалентні. Яку формулу застосовувати? Це вирішувати користувачеві в залежності від умови задачі.

Степан Лютий

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

Вам також сподобається...

Залишити відповідь

Ваша e-mail адреса не оприлюднюватиметься. Обов’язкові поля позначені *