Трюк №38. Трюки с возможностями даты и времени в Excel

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

По умолчанию в Excel используется система дат 1900. Это означает, что числовое значение, лежащее в основе даты 1 января 1900 года, равно 1, у 2 января 1900 года — 2 и так далее. В Excel эти значения называются последовательными значениями, позволяющими использовать даты в вычислениях. Формат времени очень похож, но Excel обрабатывает время как десятичные доли, где 1 — это 24:00, или 00:00. Числовое значение 18:00 (в русской версии) равно 0,75, так как это три четверти от 24 часов.

Чтобы узнать числовое значение даты или времени, отформатируйте ячейку, содержащую значение, форматом Общий (General). Например, у даты и времени (в английской версии) 3/July/2002 3:00:00 РМ числовое значение 37440.625, где число после десятичной точки представляет время, а 37440 — последовательное значение для 3 июля 2002 года.

Сложение за пределами 24 часов

При помощи функции СУММ (SUM) или просто знака плюс время можно складывать. Таким образом, =SUM(A1:A5) даст нам общее количество часов в диапазоне А1 :А5, если эти ячейки содержат допустимые значения времени. Однако если Excel не дано никаких специальных указаний, он не складывает время за пределами 24 часов. Это происходит потому, что, когда значение времени превышает 24 часа (настоящее значение равно 1), оно переходит в новый день и отсчет начинается заново. Чтобы заставить Excel не переходить в новый день после каждых 24 часов, можно использовать формат ячеек 37:30:55 или пользовательский формат [ч]:MM:cc([h]:mm:ss).

Схожий формат можно применять для получения общего количества минут или секунд, Чтобы узнать полное количество минут, когда время равно 24:00, отформатируйте ячейку как [м] ([m]), и вы получите 1440. Чтобы получить общее количество секунд, используйте пользовательский формат [с] ([s]) и вы получите 86400.

Вычисление времени и даты

Если вы хотите использовать фактические значения времени в других вычислениях, помните о следующих «магических» числах: 60 — 60 минут или 60 секунд; 3600 — 60 секунд * 60 минут; 24 — 24 часа; 1440 — 60 минут * 24 часа; 86400 — 24 часа * 60 минут * 60 секунд.

Вооружившись этими магическими числами и предыдущей информацией, намного проще манипулировать временем и датами. Взглянем на следующие примеры и посмотрим, что они обозначают (предполагаем, что время записано в ячейке А1). Если у вас есть число 5.50 и вы хотите получить 5:30 или 5:30 a.m., используйте следующую формулу: =А1/24. Необходимо указать подходящий формат!

Если время должно выглядеть как 17:30 или 5:30 p.m., используйте следующую формулу: =(А1/24)+0.5.

Чтобы получить противоположное значение, то есть десятичное время из настоящего времени, воспользуйтесь формулой =А1*24.

Если в ячейке содержится настоящая дата и настоящее время (например, 22/Jan/03 15:36), а вы хотите получить только дату, используйте следующую формулу: =INT(A1), в русской версии Excel =ЦЕЛОЕ(А1).

Чтобы получить только время: =A1-INT(A1), в русской версии Excel =А1-ЦЕЛОЕ(А1) или =MOO(A1;1), в русской версии Excel =OCTAT(A1;1). И вновь необходим подходящий формат.

Чтобы найти разность между двумя датами, воспользуйтесь формулой =DATEDIF(A1;A2;"d"), где А1 — это более ранняя дата. Получим количество дней между двумя датами. В качестве результата можно также указать «m» или «у», то есть месяцы или годы. (В действительности функция DATEDIF в Excel 97 не документирована и является функцией Lotus 123.)

Если более ранние дата или время неизвестны, помогут функции МИН (MIN) и МАКС (МАХ). Например, чтобы наверняка получить правильный результат, можно воспользоваться такой функцией: =DATEDIF(MIN(Al;A2); MAX(Al,A2),"d"), в русской версии Excel: =DATEDIF(MИН(Al;A2);MAKC(A1;A2);"d").

При работе со временем может также понадобиться учитывать начальное и конечное время. Например, начальное время — это 8:50 p.m. в ячейке А1, а конечное время — 9:50 a.m. в ячейке А2. Если вы вычтете начальное время из конечного (=А2-А1), получите в ответе ######, так как Excel по умолчанию не работает с отрицательными значениями времени. Подробнее о том, как работать с отрицательными значениями времени, — в разделе «Трюк № 74. Отображение отрицательных значений времени».

Иначе это ограничение можно обойти двумя способами, гарантировав положительный результат: =MAX(A1;A2)-MIN(A1;A2), в русской версии Excel =МАКС(А1;А2)-МИН(А1;А2) или =A1-A2+IF(A1>A2,1), в русской версии Excel =А1-А2+ЕСЛИ(А1>А2;1).

Можно также приказать Excel прибавить любое количество дней, месяцев или лет к любой дате: =DATE(YEAR(A1)+value1;MONTH(Al)+value2;DAY(Al)+value3), в русской версии Excel =ДАТА(ГОД(А1)+value1;,МЕСЯЦ(А1)+value2;ДЕНЬ(А1)+value3).

Чтобы добавить один месяц к дате в ячейке А1, воспользуйтесь формулой =DATE(YEAR(A1);MONTH(A1)+1;DAY(AD), в русской версии Excel =ДАТА(ГОД(А1);МЕСЯЦ(А1)+1;ДЕНЬ(А1)).

В Excel реализовано и несколько дополнительных функций, являющихся частью надстройки Analysis ToolPak. Выберите команду Файл → Надстройки (File → Add-Ins) и установите флажок Пакет анализа (Analysis ToolPak) и, если появится сообщение с вопросом, нужно ли установить эту надстройку, ответьте согласием. Станут доступны дополнительные функции, такие, как ДАТАМЕС (EDATE), КОНЕЦМЕСЯЦА (EMONTH), ЧИСТРАБДНИ (NETWORKDAYS) и WEEKNUM. Все эти функции можно найти в категории Дата и время (Date & Time) диалогового окна мастера функций. Их легко применять, сложнее узнать, что эти функции существуют, и привлечь их к делу.

Настоящие даты и время

Иногда в таблицах с импортированными данными (или данными, введенными неправильно) даты и время отображаются как текст, а не как настоящие числа. Это можно легко распознать, немного расширив столбцы, выделив столбец, выбрав команду Главная → Ячейки → Выравнивание (Home → Cells → Alignment) и для параметра По горизонтали (Horizontal) выбрав значение По значению (General) (это формат ячеек по умолчанию). Щелкните кнопку ОК и внимательно просмотрите даты и время. Если какие-либо значения не выровнены по правому краю, Excel не считает их датами.

Чтобы исправить эту ошибку, сначала скопируйте любую пустую ячейку, затем выделите столбец и отформатируйте его, выбрав любой формат даты или времени. Не снимая выделение столбца, выберите команду Главная → Специальная вставка → Значение → OK (Home → Paste Special → Value → Add).Tenepb Excel будет преобразовывать все текстовые даты и время в настоящие даты и время. Возможно, вам придется еще раз изменить форматирование. Еще один простой способ — ссылаться на ячейки так: =А1+0 или =А1*1.

Ошибка даты?

Excel ошибочно предполагает, что 1900 год был високосным годом (Добавим, он был последним годом XIX века, а не первым XX). Это означает, что внутренняя система дат Excel считает, что существовал день 29 февраля 1900 года, тогда как его не было! Самое невероятное — Microsoft сделала это намеренно, по крайней мере, они так утверждают.