Работа с величинами типа дат и времени

Дата хранится Excel в виде некоторого целого числа. За начало принята дата 1 января 1900 года. Таким образом, числу 1 соответствует указанная дата. Каждый день – это одна единица.

Время в интерпретации Excel – это дробная часть числа. Тогда время 00 часов соответствует числу 0,00; 3 часа ночи соответствует числу 0,125; двенадцать часов дня – число 0,5 и т.д. Таким образом, дата 1 июня 2007 года и время – 15 часов имеет представление в виде числа 39 234,625.

Excel при вводе в ячейку даты и времени в любом из форматов (кроме текстового), указанных в окне Формат ячеек, преобразует их в некоторое число. С этим числом, как правило, и работают функции времени и даты.

Некоторые функции дат и времени

ГОД – извлекает из даты в формате числа номер года.

ДАТА – преобразует отдельно заданные год, месяц и день в число.

ДЕНЬ – возвращает день месяца из даты, заданной в виде числа.

ДЕНЬНЕД – возвращает день недели из даты, заданной в виде числа.

МЕСЯЦ – возвращает номер месяца из даты, заданной в виде числа.

СЕГОДНЯ - возвращает текущую дату в числовом формате.

ТДАТА - возвращает текущую дату и время в числовом формате.

ВРЕМЗНАЧ – возвращает время в числовом формате, заданное в виде текстовой строки.

ВРЕМЯ – возвращает время в числовом формате, заданного отдельно в виде часа, минуты, секунды.

СЕКУНДЫ - возвращает количество секунд из времени, заданного в числовом формате.

МИНУТЫ - возвращает количество минут из времени, заданного в числовом формате. ЧАС - возвращает количество часов из времени, заданного в числовом формате.

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

Приведём пример вычисления количества дней между двумя датами:

=А1-В1

В этой формуле предполагается, что в ячейке А1 находится более поздняя дата, а в ячейке В1 – более ранняя дата. Ячейка, содержащая эту формулу, должна быть отформатирована числовым форматом, а не форматом для дат.

Разность между значениями времени вычислить достаточно просто. Например, если в ячейке А2 хранится время 5:30:00, а в ячейке В2 – 14:00:00, то следующая формула вернёт значение 8:30:00:

=В2-А2

Это правильный результат, но если разность отрицательна - результат будет ошибочный. Правда, эту формулу для этого случая можно подправить:

=ABS(А2-В2)

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

=ЕСЛИ (В2 < A2; B2+1; B2) – A2

Заметим, что единица в этой формуле соответствует суткам.

Суммирование значений времени, если сумма превосходит 24 часа, задача не простая. Чтобы был правильный результат необходимо отформатировать ячейку, содержащую формулу суммы, пользовательским форматом [ч]:мм:сс. Квадратные скобки здесь указывают на то, что значение часов может превышать 24.

Преобразование числа в значение времени

Преобразование числа в значение времени покажем на следующих примерах:

• Ячейка А1 содержит количество часов, равное 9,25 в любом из числовых форматов. Формула преобразования имеет вид

=А1/24

Ячейка, содержащая эту формулу, должна быть отформатирована как время и будет отображать значение 9:15:00.

• Ячейка А1 содержит количество минут равное 500 в любом из числовых форматов. Формула преобразования имеет вид

=А1/1440

Ячейка, содержащая эту формулу, должна быть отформатирована как время и будет отображать значение 8:20:00.

• Ячейка А1 содержит количество секунд, равное 65000 в любом из числовых форматов. Формула преобразования имеет вид

=А1/86400

Ячейка, содержащая эту формулу, должна быть отформатирована как время и будет отображать значение 18:03:20.

Преобразование числа секунд t в число часов T реализуется простой формулой: T=t/3600. Чтобы вычислить полное число часов необходимо от величины T взять целую часть. Для этого можно использовать функцию ЦЕЛОЕ(Х), где Х – число или ссылка на число.

Предположим, что в ячейке А1 находится число секунд, равное 47560. Тогда следующая формула

=ЦЕЛОЕ(А1/3600)

вернёт 13 – полное число часов с начала суток. Другая формула

=ЦЕЛОЕ(ОСТАТ(А1;3600)/60)

вернёт 12 - полное число минут, прошедшее с начала последнего часа. Аналогично можно преобразовать минуты в часы.