Excel содержит массу функций, позволяющих работать с датами и временем в формулах. В этом разделе будет подробно продемонстрировано использование таких функций.
В таблице систематизированы связанные со временем функции Excel. Все приведенные здесь функции можно найти в раскрывающемся списке Формулы→Библиотека функций→Дата и время.
Отображение текущего времени
Приведенная ниже формула отображает текущее время в числовом формате или как порядковое число.
=ТДАТА()-СЕГОДНЯ()
Чтобы распознать результат как время, необходимо отформатировать ячейку соответствующим образом с помощью команды Главная→Число→Числовой формат. Например, можно отобразить на экране время до и после полудня, используя следующий числовой формат.
чч:мм AM/РМ
Время можно объединить с текстовой строкой. Следующая формула позволяет отобразить текст Текущее время 6:28 РМ:
=“Текущее время ”&ТЕКСТ(ТДАТА();“ч:мм АМ/РМ”)
Примечание
Имейте в виду, что эти формулы обновляются при пересчете рабочего листа.
Для того чтобы ввести в заданную ячейку штамп времени (значение, которое не будет изменяться при пересчете рабочей книги), нажмите комбинацию клавиш <Ctrl+Shift+: (двоеточие)>.
Отображение произвольного времени
Ранее речь уже шла о том, как вводить временные данные. Достаточно ввести в ячейку определенное время, используя для этого всего один знак : (двоеточие). Но существует и другой способ. Чтобы отобразить время, воспользуйтесь функцией ВРЕМЯ. Например, формула, приведенная ниже, возвращает значение времени, которому соответствуют часы, содержащиеся в ячейке А1, минуты, введенные в ячейку В1, и секунды из ячейки С1.
=ВРЕМЯ(А1;В1;С1)
Подобно функции ДАТА, функция ВРЕМЯ воспринимает недопустимые аргументы, которые она приводит в соответствие автоматически. Например, введем, в следующую формулу заведомо неправильный аргумент 80. В этом случае формула возвратит значение 10:20:15 AM. “Неправильные” 80 минут будут просто добавлены к заданному часу, а оставшаяся их часть – отображена как 20 минут.
=ВРЕМЯ(9;80;15)
Предупреждение
Если ввести в качестве первого аргумента функции время значение, превышающее 24 часа, то результат будет не совсем ожидаемый. Следуя логике, можно предположить, что такая формула должна определить числовое значение даты и времени, равное 1,041667 (т.е. один день и один час), но в действительности она возвращает 0,041667.
=ВРЕМЯ(25;0;0)
Данная формула возвращает то же значение, что и предыдущая:
=ВРЕМЯ(1;0;0)
Функция ДАТА может быть использована вместе с функцией ВРЕМЯ в одной ячейке. Например, следующая формула вычисляет числовое значение 39420,7708333333, которое соответствует времени 18:30 4 декабря 2010 года.
=ДАТА(2010;12;4)+ВРЕМЯ(18;30;0)
Предупреждение
При вводе описанной выше формулы Excel автоматически форматирует ячейку для отображения только даты. Чтобы увидеть время, нужно вручную установить соответствующий числовой формат.
Для ввода в ячейку текущей даты и времени, которые не будут изменяться при пересчете листа, нажмите <Ctrl+Shift+; (точка с запятой)>, пробел, <Ctrl+Shift+: (двоеточие)>, а затем <Enter>.
Функция ВРЕМЗНАЧ преобразует текстовую строку, подобную временному значению, в числовое представление заданного времени. Приведенная ниже формула возвращает числовое значение 0,2395833333 для времени 5:45:
=ВРЕМЗНАЧ(“5:45”)
Чтобы результат этой формулы отображался как время, необходимо применить к ячейке формат времени. Имейте в виду, что функция ВРЕМЗНАЧ принимает только форматы времени, поддерживаемые в приложении Excel. Например, следующая формула вернет значение ошибки, поскольку Excel не воспринимает запись “a.m.” как параметр времени:
=ВРЕМЗНАЧ(“5:45 a.m.”)
Суммирование значений времени, превышающих 24 часа
Многих удивляет, почему при суммировании нескольких значений времени, когда результат превосходит 24 часа, Excel отображает его некорректно. На рисунке показан пример, в котором введено несколько значений времени – количество отработанных часов и минут за каждый день недели. Формула, вычисляющая суммарное значение времени, содержится в ячейке В9.
=СУММ(В2:В8)
Весьма очевидно, что формула возвращает неправильное значение (18 часов 30 минут). Ведь простой расчет показывает, что общее количество часов должно быть равным 42 часам 30 минутам. Проблема состоит в том, что на самом деле формула возвращает числовое значение даты и времени 1,770833, но формат ячейки не позволяет отображать часть значения, относящегося к дате.
Для того чтобы полученный результат отображался как время, превышающее 24 часа, необходимо изменить формат результирующей ячейки, а часть времени, представляющую собой часы, заключить в квадратные скобки. Применение следующего формата к ячейке В9 позволит правильно отобразить сумму значений времени:
[ч]:мм
Рассмотрим еще один пример расчета времени. На следующем рабочем листе отслеживаются нормативные и сверхурочные часы, отработанные в течение недели.
Дата начала работы введена в ячейку В4. Столбец В содержит формулы, которые вычисляют дни недели, соответствующие каждой дате. Таким образом определяются рабочие дни недели. Отработанное время вводится в диапазон ячеек С7:F13. Столбец G содержит формулы, которые вычисляют общее количество часов, отработанное за каждый день. Например, формула, содержащаяся в ячейке G7, выглядит следующим образом:
=ЕСЛИ(D7
Следующая формула, введенная в ячейку С16, подводит итог по столбцу G и вычисляет общее количество часов, отработанных в течение недели:
=СУММ(G7:G13)
В этом примере принята за основу 40-часовая рабочая неделя. Поэтому все часы, отработанные сверх 40 часов, рассматриваются как сверхурочные. Ячейка, отображающая сверхурочное время, тоже показана на рисунке выше. Если стандартная рабочая неделя в расчетах содержит больше или меньше 40 часов, эта формула может быть изменена в соответствии с конкретными условиями.
Следующая ячейка Е18 содержит формулу, которая вычисляет нормативные часы. Формула возвращает меньшее из двух значений, введенных в качестве аргументов: общее установленное стандартами количество часов или сверхурочные часы.
=МИН(Е17;Сверхурочные)
И наконец, последняя формула, содержащаяся в ячейке Е19, вычитает нормативные часы из общего количества отработанных часов и возвращает количество сверхурочных часов.
=С16-С17
Все значения в диапазоне ячеек С16:С18 отображают время, превышающее 24 часа, поэтому применяется пользовательский числовой формат.
[ч]:мм
Вычисление разницы между двумя временными значениями
Поскольку Excel позволяет преобразовать значение времени в числовой формат, несложно вычесть более раннее время из более позднего и определить таким образом разницу во времени. Например, если ячейка А2 содержит значение времени 5:30:00, а ячейка В2 – 14:00:00, то следующая формула вернет значение времени 08:30:00, т.е. разница во времени составит 8 часов 30 минут.
=В2-А2
Если разница во времени отрицательная, что является недопустимым результатом, Excel отобразит несколько знаков “решетки” (#######), поскольку при отсутствии даты точка отсчета времени – это 0. Отрицательное время, соответственно, означает отрицательное числовое значение, что само по себе уже недопустимо.
В том случае, если направление вычитания времени не столь важно, можно использовать функцию ABS, которая возвращает абсолютное значение.
=ABS(В2-А2)
Проблема “отрицательного времени” часто возникает при вычислении разницы между более ранним и более поздним временем. Вернемся к задаче вычисления количества отработанных часов. Чтобы определить его, достаточно просто вычесть начальное время работы из конечного времени. Это не представляет никакой проблемы, если два указанных значения приходятся на один и тот же день. Но как быть, если рабочее время начинается незадолго до конца текущих суток, а заканчивается на следующие сутки? Например, если служащий начинает работать в 22:00, а заканчивает в 6:00, то в этом случае результатом вычитания будет отрицательное время. Таким образом, определить отработанные часы для смены, начинающейся в одних сутках, а заканчивающейся в других, становится проблемой.
Какой может быть выход из такой ситуации? Использование функции ABS и вычисление абсолютного значения в этом случае не подходит, поскольку формула возвращает неправильный результат – 16 часов. Тем не менее приведенная ниже формула может сработать:
=ЕСЛИ(В2<А2;В2+1;В2)-А2
Существует и более простая формула для решения задачи:
=ОСТАТ(В2-А2;1)
Как уже отмечалось, работа с отрицательным временем возможна, если рабочая книга использует систему дат 1904. Чтобы перейти к этой системе дат, откройте диалоговое окно параметров Excel, перейдите во вкладку Дополнительно и в разделе При пересчете этой книги установите флажок Использовать систему дат 1904. Будьте бдительны! При изменении системы исчисления все даты, имеющиеся в рабочей книге, будут увеличены на четыре года.
Преобразование “военного времени”
“Военное время”, как правило, представляет собой четырехразрядное значение в пределах от 0000 до 2359. Например, 1:00 AM отображается как 0100 часов, а 3:30 РМ как 1530 часов. Формула, приведенная ниже, преобразует это значение (ячейка А1) в общепринятый формат времени.
=ВРЕМЗНАЧ(ЛЕВСИМВ(А1;2)&“:”&ПРАВСИМВ(А1;2))
Если ячейка А1 содержит больше или меньше четырех чисел, то результат, который возвратит данная формула, будет неверным. Следующая формула корректирует эту ошибку и возвращает допустимое значение времени для любого времени в “военном формате” от 0 до 2359:
=ВРЕМЗНАЧ(ЛЕВСИМВ(ТЕКСТ(А1;“0000”);2)&“:”&ПРАВСИМВ(А1;2))
В более простой формуле задействована функция ТЕКСТ, которая форматирует строку как текст, а затем использует функцию ВРЕМЗНАЧ, чтобы отобразить результат в формате времени.
=ВРЕМЗНАЧ(ТЕКСТ(А1;“00\:00”))
Преобразование часов, минут и секунд в десятичном исчислении в значение времени
Иногда часы, минуты или секунды времени отображаются в виде значений в десятичном исчислении. Для того чтобы преобразовать их в стандартный формат времени, просто разделите значение на 24. Предположим, что ячейка А1 содержит значение часа 9,25. Следующая формула возвращает 09:15:00 (9 часов 15 минут):
=А1/24
Подобная формула может использоваться и для преобразования минут в десятичном исчислении. Но в знаменателе должно стоять число 1440 – количество минут в сутках. Например, если ячейка А1 содержит значение 500 (минут), то следующая формула возвращает 08:20:00 (8 часов 20 минут):
=А1/1440
При преобразовании секунд в стандартный формат времени значение делится на 86 400 (число секунд в сутках). Например, если ячейка А1 содержит число 65 000 (секунд), то формула, приведенная ниже, возвращает 18:03:20 (18 часов 3 минуты и 20 секунд):
=А1/86400
Добавление часов, минут или секунд к заданному времени
Функция ВРЕМЯ также используется для добавления часов, минут или секунд ко времени, содержащемуся в определенной ячейке. Например, если ячейка А1 содержит время, следующая формула позволяет добавляет к нему 2 часа и 30 минут и отображает полученный результат:
=А1+ВРЕМЯ(2;30;0)
Эта же функция используется для заполнения определенного диапазона ячеек возрастающими значениями времени. На рисунке показан рабочий лист, в котором диапазон ячеек А1:А14 содержит значения времени, каждое из которых последовательно увеличивается на 10 минут. Только в одну из этих ячеек (А1) значение времени вводится вручную. В остальные ячейки просто копируется формула, введенная в ячейку А2.
=А1+ВРЕМЯ(0;10;0)
Для заполнения значениями времени диапазона ячеек можно также использовать функцию автозаполнения. К примеру, чтобы создать последовательность значений времени с приращением в 10 минут, введите в ячейку А1 значение 8:00, а в ячейку А2 – 8:10. Выделите обе ячейки и перетащите правой кнопкой мыши маркер автозаполнения (в нижнем правом углу ячейки А2) вниз по столбцу.
Работа с часовыми поясами
Представьте, что вам необходимо использовать рабочий лист, содержащий дату и время по Гринвичу, которое иногда называют универсальным временем. Как перейти от этого времени ко времени текущего часового пояса? Для того чтобы дата и время соответствовали определенному часовому поясу, необходимо, в первую очередь, установить разницу между этими поясами. Например, разница между временем по Гринвичу (GMT) и среднеамериканским временем (CST) составляет -6 часов.
В данном случае функцию ВРЕМЯ с отрицательным аргументом использовать нельзя, поэтому задача требует несколько иного решения. Один час эквивалентен 1/24 суток. Таким образом, разницу во времени можно разделить на 24, а затем полученный результат добавить ко времени исходного часового пояса.
На рисунке приведен пример рабочего листа, в котором дата и время по Гринвичу приводятся в соответствие текущему часовому поясу. Ячейка В1 содержит разницу в часах между поясами – в данном случае -5 часов для Стандартного восточного времени США (EST). В ячейке В4 отображается приведенная ниже формула, которая скопирована вниз по столбцу.
=А4+($В$1/24)
Данная формула весьма эффективна, поскольку добавляет х часов к дате и времени, содержащимся в столбце А. Если ячейка В1 содержит отрицательное значение часа, это значение вычитается из даты и времени, отображаемых в столбце А. Заметьте, что в некоторых случаях это действие также изменяет и дату.
Округление значений времени
Иногда может потребоваться округлить время до определенного значения. Например, округлить записи в отчете об использовании времени до ближайшего значения 15 минут. В этом разделе мы обсудим несколько возможных решений задачи округления временных значений.
Следующая формула округляет время, содержащееся в ячейке А1, до ближайшей минуты:
=ОКРУГЛ(А1*1440;0)/1440
Эта формула работает следующим образом: заданное время умножается на 1440 – в результате вычисляется общее количество минут. После этого полученный результат обрабатывается с помощью функции ОКРУГЛ и делится на 1440. Например, если ячейка А1 содержит значение 11:52:34, формула возвращает 11:53:00.
Представленная ниже формула несколько напоминает предыдущую, за исключением того, что она округляет время в ячейке А1 до ближайшего часа.
=ОКРУГЛ(А1*24;0)/24
Если ячейка А1 содержит значение 5:21:31, данная формула возвращает 5:00:00.
Формула, приведенная ниже, округляет время, отображаемое в ячейке А1, до ближайших 15 минут.
=ОКРУГЛ(А1*24/0,25;0)*(0,25/24)
В этой формуле значение 0,25 означает четверть часа, т.е. 15 минут. Аналогично, чтобы округлить время до ближайших 30 минут, просто измените 0,25 на 0,5, как это сделано в следующей формуле:
=ОКРУГЛ(А1*24/0,5;0)*(0,5/24)
Работа со значениями, не имеющими отношения ко времени суток
Вы когда-нибудь сталкивались с задачей составления списка результатов гоночных соревнований или, например, подсчета времени ежедневной пробежки? Эти временные значения фактически никак не связаны с определенным временем дня. Они, скорее, характеризуются длительностью конкретного события в часах, минутах, секундах. Итак, предположим, что время, отведенное на тест, составляет 35 минут 45 секунд. Это значение может быть введено в ячейку следующим образом:
00:35:45
Excel воспринимает эту запись достаточно хорошо и интерпретирует ее как 12:35:45 AM. Остается только установить для этой ячейки подходящий формат времени. Имейте в виду, что при вводе времени, которое не содержит часа, для значения часа необходимо указать хотя бы один нуль. В противном случае, если данные часа будут опущены, Excel интерпретирует эту запись как 35 часов 45 минут.
На рисунке показан пример рабочего листа, в котором отслеживаются тренировки спортсмена. Столбец А содержит обыкновенные даты. Столбец В – расстояние в милях. Столбец С отображает время, которое потребовалось на преодоление каждой дистанции. Формулы, содержащиеся в столбце D, вычисляют скорость спортсмена в милях за час. Например, формула в ячейке D2 выглядит следующим образом:
=В2/(С2*24)
В столбце Е содержатся формулы, которые вычисляют скорость движения спортсмена в минутах за милю. Например, в ячейке Е2 расположена следующая формула:
=(С2*60*24)/В2
Ячейки в столбце F отображают формулы, которые вычисляют итоги использованного времени (данные столбца С). Во всех ячейках столбца G установлен приведенный ниже числовой формат, который позволяет отображать время, превышающее 24 часа.
[чч]:мм:сс