Рассмотрены различные варианты определения количества полных лет, месяцев и дней между датами с помощью формул Excel, макрокоманд Excelи недокументированных возможностей Excel. Приведен метод определения високосного года. Описаны параметры функций ДОЛЯГОДА() и РАЗНДАТ()
Во многих задачах, таких как «Определить дату очередной прививки ребенку» или «Расчет отработанного стажа» необходимо определить, сколько полных лет, месяцев и дней исполнилось человеку на указанную дату. Автоматизировать данный процесс можно, используя таблицы ExcelДля определения количества лет можно использовать функцию ДОЛЯГОДА (Начальная дата; Конечная дата; Базис), которая возвращает долю года, между двумя указанными датами, где базис — используемый способ вычисления дня. Значения базисов приведены в таблице 1
Таблица 1
Значения базисов в функции «ДОЛЯГОДА»
|
Базис |
Способ вычисления дня |
|
0 |
Американский (NASD) 30/360 |
|
1 |
Фактический / фактический |
|
2 |
Фактический / 360 |
|
3 |
Фактический / 365 |
|
4 |
Европейский 30/360 |
Например, если начальная дата (дата на которую необходимо произвести расчет) составляет 01.07.2011, а конечная дата (дата рождения) составляет 08.03.2010, то результатом функции будет 1,31506849315068. Целая часть данного числа и даст количество полных лет. Количество месяцев, можно получить, как остаток данного числа, умноженное на 12. В результате получим 0,31506849315068*12=3,78082191780822 или 3 полных месяца. Приблизительное значение дней получаем как остаток от месяцев, умноженный на 30. т. е. 0,78082191780822 *30=23,4246575342465.
В результате получаем таблицу, приведенную на рисунке 1
Рис.1. Вычисление полных лет и месяцев с приближенным вычислением дней
В ячейку С5 поместим формулу
=ОТБР(ДОЛЯГОДА(B5;B1;1))
В ячейку D5 поместим формулу
=ОТБР(ОСТАТ(ДОЛЯГОДА(B5;B1;1);1)*12)
В ячейку E4 поместим формулу
=ОТБР(ОСТАТ(ОСТАТ(ДОЛЯГОДА(B5;B1;1);1)*12;1)*30)
Если необходимо определить точное количество дней, то в последней формуле необходимо производить умножение на количество дней конкретного месяца, присутствующего в дате, введенной в ячейку B1
Определить количество дней в месяце конкретной даты с помощью формул Excel сложнее. Готовой такой формулы нет. Тем более, если месяц окажется февралем, то все будет зависеть еще и от года (високосный или нет).
Рис.2. Вычисление полных лет и месяцев с точным вычислением дней
Попробуем последовательно решить эту задачу.
Создадим таблицу, приведенную на рисунке 2.
В ячейку B2 поместим формулу =ГОД(B1)
В ячейку B3 поместим формулу =МЕСЯЦ(B1)
Чтобы определить, является ли год високосным, необходимо выполнить следующий алгоритм:
1. Если год делится на 4, перейдите к шагу 2. В противном случае перейдите к шагу 5.
2. Если год делится на 100, перейдите к шагу 3. В противном случае перейдите к шагу 4.
3. Если год делится на 400, перейдите к шагу 4. В противном случае перейдите к шагу 5.
4. Год является високосным (366 дней).
5. Год не является високосным (365 дней).
В связи с этим в ячейку B4 поместим формулу:
=ЕСЛИ(ИЛИ(ОСТАТ(B2;400)=0;И(ОСТАТ(B2;4)=0;ОСТАТ(B2;100)<>0));1;0)
Формула возвращает единицу, если год високосный и ноль если нет.
В ячейку B5 поместим формулу:
=ЕСЛИ(ИЛИ(B3=1;B3=3;B3=5;B3=7;B3=8;B3=10;B3=12);31;ЕСЛИ(B3=2;28;30))
Формула возвращает одно из значений 31, 30 или 28 в зависимости от месяца в дате. Остается правильно учесть дни в феврале в зависимости от года. Для этого в ячейку B6 поместим формулу: =ЕСЛИ(B5=28;B5+B4;B5) Формула будет возвращать количество дней в месяце даты с учетом года.
И последнее, что можно сделать, это объединить все формулы, по вычислению дней в месяце даты в одну формулу.
Создадим таблицу, приведенную на рисунке 3.
Рис.3. Оптимизированная таблица
Поместим в ячейку B2 формулу:
В ячейке F5 сошлемся на нее, как
=ОТБР(ОСТАТ(ОСТАТ(ДОЛЯГОДА(B5;A2;1);1)*12;1)*B2)
Заметим, что при точном подсчете, дней оказалось не 23, как показано в таблице на рисунке 1, а 24 дня.
Еще один вариант определения количества дней в дате текущего месяца, это использование функций КОНМЕСЯЦА и ДЕНЬ. Необходимо сказать, что функция КОНМЕСЯЦА доступна только после подключения надстройки «Пакет анализа». Функция КОНМЕСЯЦА возвращает дату в числовом формате для последнего дня месяца, отстоящего вперед или назад на заданное число месяцев. Если заданное число месяцев принять равным нулю, то функция возвратит дату последнего дня текущего месяца. Получить числовое значение количества дней можно с помощью функции ДЕНЬ.
Создадим таблицу, приведенную на рисунке 4.
В ячейку B2 поместим формулу =ДЕНЬ(КОНМЕСЯЦА($B$1;0))
В ячейку C5 поместим формулу =ОТБР(ДОЛЯГОДА(B5;$B$1;1))
В ячейку D5 поместим формулу
=ОТБР(ОСТАТ(ДОЛЯГОДА(B5;$B$1;1);1)*12)
В ячейку E5 поместим формулу
=ОСТАТ(ОСТАТ(ДОЛЯГОДА(B5;$B$1;1);1)*12;1)*$B$2
Рис. 4. Использование функции КОНМЕСЯЦА
Задачу можно решить, зная макрокоманды Excel. Для вычисления количества дней в месяце указанной даты можно написать функцию:
Function DnMes(D)
'Дней в месяце от даты
M = Month(D): G = Year(D): V = 0
If (G Mod 400 = 0) Or ((G Mod 4 = 0) And (G Mod 100 <> 0)) Then V = 1
Select Case M
Case 1, 3, 5, 7, 8, 10, 12
DnMes = 31
Case 2
DnMes = 28 + V
Case 4, 6, 9, 11
DnMes = 30
EndSelect
EndFunction
Тогда в ячейку B2 таблицы, приведенной на рисунке 3 достаточно поместить формулу: =DnMes(A2)
И еще есть такая вещь, как недокументированные возможности. Оказывается Excel поддерживает функцию РАЗНДАТ(). Правда этой функции нет среди тех, которые доступны в диалоговом окне «Мастер функций» и набирать ее придется вручную. Функция известна еще с Lotus1–2-3, и очевидно с целью совместимости Excel поддерживает эту функцию. Интересно, что с версии Excel 2000 данная функция даже не упоминалась. А из интерактивной справки Excel2003 были удалены все ссылки на эту функцию, хотя сама функция доступна, как и в Excel2007.
Функция позволяет вычислить количество дней, месяцев и лет, между двумя указанными датами (как раз, то, что нам нужно). Она использует три аргумента: «начальная дата», конечная дата» и «код», с помощью которого задается единица времени. Значения кодов приведены в таблице 2.
Таблица 2
Коды функции РАЗНДАТ()
|
Код |
Возвращаемое значение |
|
«y» |
Количество полных лет |
|
«m» |
Количество полных месяцев |
|
«d» |
Количество дней |
|
«md» |
Количество дней (месяцы и годы игнорируются) |
|
«ym» |
Количество месяцев (годы и дни игнорируются) |
|
«yd» |
Количество дней (годы игнорируются) |
Имея в распоряжении такую функцию, наша задача решается тривиально просто.
Создадим таблицу, приведенную на рисунке 5.
Рис. 5. Использование функции РАЗНДАТ()
В ячейку С5 поместим формулу =РАЗНДАТ(B5;$A$2;«y«)
В ячейку D5 поместим формулу =РАЗНДАТ(B5;$A$2;«ym»)
В ячейку E5 поместим формулу =РАЗНДАТ(B5;$A$2;«md»)
Опять же, есть один нюанс. Дело в том, что функция РАЗНДАТ() по умолчанию использует американский базис при вычислении количества дней (смотри таблицу 1). Поэтому все зависит от конкретной задачи. Если необходимо фактическое количество дней, то лучше пользоваться таблицей, приведенной на рисунке 3.
Литература
1. Веденеева Е. Функции и формулы Excel 2007. Библиотека пользователя. СПб.: Питер, 2008. 384 с.
2. Мединов О. Office 2007. Мультимедийный курс. СПб.: Питер, 2009. 176 с.
3. Поддержка Excel/Справка и инструменты по Excel 2007 [Электронный ресурс]. — http://office.microsoft.com/ru-ru/excel-help/HP010062302.aspx
4. Форум по Excel [Электронный ресурс]. — http://sizop.my1.ru/forum/10–236–1
5. Справочник по функциям Excel [Электронный ресурс]. — http://www.excel-vba.ru/tag/funkcii-excel/
