- Уточнимо відміни: рік / років, місяці / місяців, дня / днів
- Про обчислення страхового стажу
- Зворотній завдання
Визначимо, скільки років, місяців і днів пройшло з певної дати, а також вирішимо зворотну задачу.
Для обчислень тривалості інтервалів дат найзручніше використовувати недокументовані функцію РАЗНДАТ (). Цій функції немає в довідці EXCEL2007 і в Майстрі функцій (SHIFT + F3), але вона працює ( з деякими огріхами ).
Якщо в осередку В2 міститься сьогоднішня дата, а в А2-дата початку відліку (наприклад, день народження), то формула:
= РАЗНДАТ (A2; B2; "y") & "м" &
РАЗНДАТ (A2; B2; "ym") & "міс." &
РАЗНДАТ (A2; B2; "md") & "дн."
розрахує скільки років, місяців, днів пройшло з конкретної дати і поверне результат у вигляді 37 м 5 міс. 1 дн.
Як показано в статті про функції РАЗНДАТ (), краще уникати застосування цієї функції з аргументом "md", тому що вона може видати неправильний результат. Тому частина формули РАЗНДАТ (A2; B2; "md") краще замінити її еквівалентом:
= ЕСЛИ (ДЕНЬ (A2)> ДЕНЬ (B2); ДЕНЬ (КОНМЕСЯЦА (ДАТАМЕС (B2; -1); 0)) - ДЕНЬ (A2) + ДЕНЬ (B2); ДЕНЬ (B2) -день (A2))
Підсумкова формула приведена в файлі прикладу:
= РАЗНДАТ (A2; B2; "y") & "м" &
РАЗНДАТ (A2; B2; "ym") & "міс." &
ЯКЩО (ДЕНЬ (A2)> ДЕНЬ (B2); ДЕНЬ (КОНМЕСЯЦА (ДАТАМЕС (B2; -1); 0)) - ДЕНЬ (A2) + ДЕНЬ (B2); ДЕНЬ (B2) -день (A2)) & " дн. "
Уточнимо відміни: рік / років, місяці / місяців, дня / днів
У разі, якщо виходить ціле кількість років, то формула поверне результат у вигляді 37 м 0 міс. 0 дн. , Що не дуже красиво.
Скориставшись ідеями, підказаними нашим користувачем МСН, також провідмінювати місяців / місяця і днів / дня
= ЕСЛИ (РАЗНДАТ (A2; B2; "y"); РАЗНДАТ (A2; B2; "y") & "" & ТЕКСТ (ОСТАТ (МАКС (ОСТАТ (РАЗНДАТ (A2; B2; "y") - 11; 100) ; 9); 10); "[<1] \ го \ д; [<4] \ го \ так; років") & "";) &
ЯКЩО (РАЗНДАТ (A2; B2; "ym"); РАЗНДАТ (A2; B2; "ym") & "мі" & ТЕКСТ (ОСТАТ (РАЗНДАТ (A2; B2; "ym") - 1; 11); "[< 1] ц; [<4] ца; ців ") &" ";) &
ЯКЩО (РАЗНДАТ (A2; B2; "md"); РАЗНДАТ (A2; B2; "md") & "д" & ТЕКСТ (ОСТАТ (МАКС (ОСТАТ (РАЗНДАТ (A2; B2; "md") - 11; 100) ; 9); 10); "[<1] ень; [<4] ня; ній");)
Остаточно результат буде виглядати так: 37 років 1 місяць 4 дні.
Про обчислення страхового стажу
Формули цієї статті НЕ дозволяють правильно обчислити страхової (трудової) стаж з точністю до днів. Пояснимо сказане на прикладі (також див. Статтю Функція РАЗНДАТ () - Обчислення різниці двох дат в днях, місяцях, роках в MS EXCEL )
Знайдемо різницю дат 30.01.15. і 16.03.2015. Функція РАЗНДАТ () з параметрами md і ym підрахує, що різниця складає 1 місяць і 14 днів.
Алгоритм розрахунку цієї функції наступний: спочатку функція послідовно додає місяці до дати початку, до тих пір поки вийшла дата менше кінцевої. Тобто до 30.01.2015 функція додасть 1 місяць і отримає 30.02.2015, але такої дати не існує, в лютому 2015 р. 28 днів! Нічого страшного, функція запам'ятовує, що потрібно відняти 2 дня, щоб отримати правильну дату! Потім йде підрахунок днів, що залишилися в березні - їх 16. Потім, функція до 16 дням додає -2, і отримуємо 14 днів. Природно, такий розрахунок не вірний.
Виходить, що функцію РАЗНДАТ () можна використовувати тільки для розрахунку повних років і місяців , Але без днів. Розглянуті вище формули будуть в основному повертати правильний результат (іноді, звичайно, буде виникати похибка в 1-2 дня в меншу сторону), але часто зручність застосування цієї функції переважує вимоги до точності. Такий підхід безумовно не годиться для розрахунку страхового стажу для визначення розміру допомоги по тимчасовій непрацездатності (про це див. Статтю Розрахунок страхового (трудового) стажу в MS EXCEL ).
Зворотній завдання
Тепер вирішимо зворотну задачу: задавши кількість кількість років, місяців і днів, розрахуємо кінцеву дату.
Кінцеву дату будемо розраховувати двома способами: через функцію ДАТА () і ДАТАМЕС ()
= ДАТА (РІК (A8) + B8; МІСЯЦЬ (A8) + C8; ДЕНЬ (A8) + D8)
= ДАТАМЕС (ДАТА (РІК (A8) + B8; МІСЯЦЬ (A8); ДЕНЬ (A8)); C8) + D8
Обчисливши кінцеву дату, потім обчислимо за допомогою РАЗНДАТ () різницю між ними (повинна збігтися із заданою). І нарешті, з кінцевої дати отримаємо початкову, віднявши задану кількість років, місяців і днів з використанням формул:
= ДАТА (РІК (E8) -B8; МІСЯЦЬ (E8) -C8; ДЕНЬ (E8) -D8)
= ДАТАМЕС (ДАТА (РІК (E16) -B8; МІСЯЦЬ (E16); ДЕНЬ (E16)); - C8) -D8
Якщо для початкової дати 15.01.2015 (рядок 8 у файлі прикладу, лист Зворотній завдання) все обчислення зрозумілі і однакові (додали 1 рік, 2 місяці і 5 днів), отримали 20.03.2015, різниця склала теже 1 рік, 2 місяці і 5 днів, розрахункова початкова дата збіглася із заданою початковою датою, то для 29.01.2015 все не так гладко (див. рядок 9). Для наочності додамо 1 місяць.
Як ми вже знаємо з статті Функція ДАТАМЕС () в MS EXCEL функції ДАТА () і ДАТАМЕС () не еквівалентні при додаванні місяців:
При додаванні місяців між ДАТАМЕС () і ДАТА () існує різниця. Додамо до 29.01.2015 один місяць:
- = ДАТАМЕС ( "29.01.2015"; 1) поверне 28.02.2015, тому що 30 лютого не існує, то функція поверне останній день місяця, тобто 28.02.2014 ( "зайві" 2 дня будуть відкинуті);
- = ДАТА (РІК ( "29.01.2015"); МІСЯЦЬ ( "29.01.2015") + 1; ДЕНЬ ( "29.01.2015")) поверне 02.03.2015: "зайві" 2 дні (29 і 30 лютого) будуть додані до дати.
Розрахунок за допомогою ДАТАМЕС () здається логічніше, хоча функція РАЗНДАТ () з цим не згодна: різницю, яка відповідає заданій вона повертає тільки для варіанту з функцією ДАТА ().
Розрахункова початкова дата в обох випадках не збігається із заданою початковою датою: отримаємо 01.02.2015 і 28.01.2015 замість 29.01.2015.
Як бути? Ймовірно, рішення буде залежати від конкретного завдання, що стоїть перед користувачем. Необхідно пам'ятати, що потрібно використовувати ДАТАМЕС () при додаванні (відніманні) місяців, а РАЗНДАТ () - це недокументированная і, відповідно, не гарантує правильність розрахунків функція. Про цю функцію написана стаття Функція РАЗНДАТ () - Обчислення різниці двох дат в днях, місяцях, роках в MS EXCEL , В якій дається формула, що враховує вищевказану ситуацію (див. Розділ "Ще раз про кривизну РАЗНДАТ ()").