5.6. Функции дат и времени

Обзор функций дат и времени. Эти функции являются эффективным средством управления данными, содержащими значения дат и времени. С их помощью можно вычислять количество рабочих дней между заданными датами; вставлять в документ текущую дату и время; определять дату, отстоящую от текущей на заданное число рабочих дней, и т.д.

Дата сохраняется как целая часть числа (количество дней, начиная с 1-го января 1900 г.), а время сохраняется как дробная часть числа (доля от 24-часовых суток). Например, дата 1 января 2000 года будет храниться как число 36526 (именно столько дней прошло с 1-го января 1900 г.). А 6 часов 31-го января 1900 года сохраняется как 31.25 (здесь 0.25 – результат деления 6/24). Такое представление дат и времени позволяет производить любые вычисления с датами и временем (их можно складывать, вычитать и использовать в других вычислениях).

Функции дат и времени можно условно разделить на три группы: функции преобразования форматов; функции анализа дат и времени; функции для работы с интервалами дат.

Функции преобразования форматов. В эту категорию включены функции, пре­образующие текстовый формат дат и времени в числовой, а также формирующие даты и время из отдельно заданных аргументов.

=ДАТАЗНАЧ(дата^_как^_текст) – преобразует дату из текстового формата в числовой. Например, функция =ДАТАЗНАЧ("5.06.2003") возвращает 37777 (столько дней прошло с 1.01.1900 до 5.06.2003 года).

=ВРЕМЗНАЧ(время^_как^_текст) – преобразует время из текстового формата в числовой. Например, функция =ВРЕМЗНАЧ("12:30") возвращает 0.520833 (12 часов – это 12/24 = 0.5, а 30 минут – это 30/60/24 = 0.020833; в сумме 0.520833).

=ДАТА(год; месяц; день) – возвращает дату, соответствующую заданным аргументам. Если ячейка имеет формат Числовой, дата будет представлена числом, отсчитанным от 1 ян­варя 1900 года. А если ячейка имеет формат Общий, результат форматируется как дата (например, функция =ДАТА(2003; 5; 9) в этом случае возвращает 5.09.03).

=ВРЕМЯ(часы; минуты; секунды) – возвращает время, соответствующее заданным аргументам. Например, функция =ВРЕМЯ(6; 30; 05) возвращает 6:30:05.

Функции анализа дат и времени. Эти функции служат для извлечения отдельных составляющих дат и времени (например, года, месяца, количества минут и т.д.). Их час­то используют совместно с функциями СУММ и ЕСЛИ для условного суммирования данных. Например, если в ячейках D3:D15 находятся даты, а в ячейках E3:E15 – соответствующие платежи, то с помощью формулы массива {=СУММ(ЕСЛИ(МЕСЯЦ(D3:D15)=5;E3:E15;0))} можно просуммировать только майские платежи. Напомним, что ввод формул массива нужно заканчивать нажатием комбинации клавиш Shift+Ctrl+Enter.

Кроме функций извлечения составляющих дат и времени к категории функций анализа ус­ловно можно отнести функции СЕГОДНЯ и ТДАТА, возвращающие текущую дату и время. Рассмотрим основные функции анализа:

=ГОД(дата^_в^_числовом^_формате) – возвращает год, соответствующий заданному ар­гументу. Аргумент этой функции и всех других функций, где используется дата_в_число­вом_формате, можно задавать любым из трех способов:

 в виде ссылки на ячейку с датой;

 в виде числа, определяющего отстояние даты от 1.01.1900 года.

 в виде текстовой строки, где дата заключена в кавычки (например, "5.10.2003").

=МЕСЯЦ(дата^_в^_числовом^_формате) – возвращает номер месяца, соответствующего заданному ар­гументу. Например, если в ячейке D2 содержится дата 5.10.2003, то функция =МЕСЯЦ(D2) возвращает число 10.

=ДЕНЬ(дата^_в^_числовом^_формате) – возвращает день месяца, соответствующий задан­ному ар­гументу. Для той же ячейки D2 функция =ДЕНЬ(D2) возвращает число 5.

=ДЕНЬНЕД(дата^_в^_числовом^_формате; тип) – возвращает день недели, соответствующий задан­ным ар­гументам. Первый аргумент в данном случае не имеет никаких особенностей, а в качестве аргумента тип можно задавать одно из трех значений:

 1 (или аргумент опущен) – дни нумеруются от 1 (воскресенье) до 7 (суббота);

 2 – дни нумеруются от 1 (понедельник) до 7 (воскресенье);

 3 – дни нумеруются от 0 (понедельник) до 6 (воскресенье).

Например, если использовать данные той же ячейки D2, то функция =ДЕНЬНЕД(D2; 2) возвращает число 7. Поскольку задан тип 2, то это – воскресенье.

=ЧАС(время^_в^_числовом^_формате) – возвращает час (от 0 до 23), соответствующий задан­ному ар­гументу. Аргумент этой функции и других функций, где используется время_в_число­вом_формате, можно задавать любым из трех способов:

 в виде ссылки на ячейку со значением времени;

 в виде числа, определяющего время (например, 12/24 = 0.5 – это 12 часов).

 в виде текстовой строки, где время заключено в кавычки (например, "7:30:15").

=МИНУТЫ(время^_в^_числовом^_формате) – возвращает минуты, соответствующие заданному ар­гументу. Например, если в ячейке F5 содержится значение времени 11:30:20, то функция =МИНУТЫ(F5) возвращает число 30.

=СЕКУНДЫ(время^_в^_числовом^_формате) – возвращает секунды, соответствующие заданному ар­гументу. Для той же ячейки F5 функция =СЕКУНДЫ(F5) возвращает 20.

=СЕГОДНЯ() – возвращает текущую дату. Эта функция используется без аргументов, но скобки обязательны. Другая ее особенность заключается в том, что это динамическая функция. Если открывать документ Excel с функцией СЕГОДНЯ в разные дни, то эта функция будет возвращать разные даты.

Кроме отображения текущей даты, функция СЕГОДНЯ может использоваться в вы­числениях. Например, если в ячейке D5 содержится функция СЕГОДНЯ, в ячейке D6 – число 10, а в ячейке D7 – формула =D5+D6, то результатом вычисления по этой формуле 15-го октября 2003 года будет 25.10.03.

=ТДАТА() – возвращает текущую дату и текущее время. Эта функция используется без аргументов, но скобки обязательны. Как и функция СЕГОДНЯ, ТДАТА – динамическая функция. Возвращаемое ею время обновляется при каждом вводе новых данных. Кро­ме того, для приведения в соответствие времени, возвращаемого этой функцией, можно нажать клавишу F9.

Как и функция СЕГОДНЯ, ТДАТА может использоваться в вычислениях. Например, если в ячейке D5 содержится функция ТДАТА, в ячейке D6 – число 10.25⁷, а в ячейке D7 – формула =D5+D6, то результатом вычисления по этой формуле 15-го октября 2003 года в 12 часов 15 минут будет 25.10.03 18:15.

Функции для работы с интервалами дат. Эти функции используются для выполнения операций с интервалами дат без учета суббот, воскресений и праздничных дней. Наиболее распространенными функциями этого типа являются:

=ЧИСТРАБДНИ(нач^_дата; кон^_дата; праздники) – возвращает количество рабочих дней между двумя заданными датами: нач_дата и кон_дата. Эти аргументы можно задавать лю­бым из трех способов:

 в виде ссылок на ячейки с датами;

 в виде чисел, определяющих отстояние дат от 1.01.1900 года.

 в виде текстовых строк, где дата заключена в кавычки (например, "5.10.2003").

Необязательный аргумент праздники задается в виде ссылки на массив ячеек с празд­ничными датами. Например, если в ячейках F1:F3 содержатся даты: 14.06.03 (суббота), 18.06.03 (среда) и 16.06.03 (праздник), то функция =ЧИСТРАБДНИ(F1; F2; F3) вернет значение 2. То есть между 14-м и 18-м июня два рабочих дня (17.06 и 18.06).

=РАБДЕНЬ(нач^_дата; количество^_дней; праздники) – возвращает дату, отстоящую на заданное количество рабочих дней вперед или назад от нач_даты. Дата возвращается в виде числа, определяющего ее отстояние от 1.01.1900 года. Для получения обычной даты ну­жно отформатировать ячейку с результатом вычислений (см. Главу 3, п. 3.2).

Аргумент нач_дата задается в виде ссылки на ячейку или в виде числа, определяющего отстояние даты от 1.01.1900 года. Аргумент праздники ничем не отличается от аналогичного аргумента функции ЧИСТРАБДНИ. А аргумент количество_дней – это поло­жительное или отрицатель­ное число, определяющее отстояние искомой даты от заданной нач_даты. Положительное число задает отстояние даты вперед, а отрицательное – назад.