В профессиональной работе с электронными таблицами часто необходимо взаимодействовать с датой и временем. Вы не можете обойтись без него. Поэтому научиться работать с таким типом данных просто необходимо. Это позволит вам сэкономить огромное количество времени и избежать многих ошибок при работе с электронными таблицами.
К сожалению, многие новички не знают, как происходит работа с данными. Поэтому перед рассмотрением этого класса операций необходимо более подробное введение.
Как представляется дата в Excel
Информация о дате обрабатывается как количество дней с 0 января 1900 года. Да, вы не ошибаетесь. Действительно, с нуля. Но нужно, чтобы была точка отсчета, чтобы уже 1 января считалось номером 1 и так далее. Максимальное поддерживаемое значение даты - 2958465, или 31 декабря 9999 года.
Этот метод позволяет использовать даты для вычислений и формул. Таким образом, Excel позволяет указать количество дней между датами. Схема проста: одно число вычитается из другого, а затем полученное значение преобразуется в формат даты.
Для наглядности приводим таблицу, в которой указаны даты с соответствующими числовыми значениями.
Чтобы определить количество дней между датой A и датой B, вычтите первую дату из последней. В данном случае формула =B3-B2. После ввода результат будет следующим.
Обратите внимание, что значение отображается в днях, потому что мы выбрали другой формат ячеек, чем для даты. Если бы мы изначально выбрали формат 'Date', результат был бы следующим.
Важно отметить этот момент в ваших расчетах.
То есть, чтобы отобразить действительный порядковый номер, который полностью соответствует дате, необходимо использовать любой формат, кроме даты. В свою очередь, чтобы преобразовать число в дату, необходимо установить соответствующий формат.
Как представляется время в Excel
Способ представления времени в Excel несколько отличается от способа представления дат. В основе суток лежит день, а часы, минуты и секунды - это его дробные части. Другими словами, 24 часа - это 1, а любое меньшее значение рассматривается как дробь. Так, 1 час равен 1/24 суток, 1 минута - 1/1140, а 1 секунда - 1/86400. Наименьшая единица времени, доступная в Excel, - 1 миллисекунда.
Как и в случае с датами, этот способ представления позволяет производить расчеты со временем. Однако здесь есть одно неудобство. После вычисления мы получаем долю дня, а не количество дней.
На скриншоте показаны значения в числовом формате и в формате "Время".
Метод вычисления времени такой же, как и для даты. Вы должны вычесть более раннее время из более позднего. В нашем случае это формула =B3-B2.
Поскольку ячейка B4 сначала имела формат General, после завершения ввода формулы он сразу же изменится на 'Time'.
При работе со временем Excel выполняет обычные арифметические операции над числами, которые затем преобразуются в знакомый нам формат времени.
Формат дат и времени
Насколько нам известно, даты и время могут храниться в различных форматах. Поэтому необходимо знать, как правильно их вводить, чтобы форматирование было корректным.
Конечно, можно использовать порядковый номер дня или части дня при вводе даты и времени, но такой подход очень неудобен. Более того, вам придется постоянно применять к ячейке определенный формат, что только добавляет неудобств.
Поэтому Excel позволяет вводить время и дату разными способами. Если вы применяете один из них, программа немедленно преобразует информацию в правильное число и применяет правильный формат к ячейке.
В таблице ниже приведен список методов ввода даты и времени, поддерживаемых Excel. В левом столбце перечислены возможные форматы, а в правом столбце показано, как они будут отображаться в Excel после преобразования. Обратите внимание, что если вы не укажете год, автоматически назначается текущий год, установленный в операционной системе.
На самом деле, существует гораздо больше способов их отображения. Но даже этого достаточно. Кроме того, конкретный ввод даты зависит от страны или региона и настроек операционной системы.
Произвольное форматирование
При работе с ячейками пользователь может решить, каким будет форматирование. Они могут выбрать отображение только времени, месяца, дня и так далее. Также можно настроить порядок дат и разделителей.
Чтобы получить доступ к окну редактирования, откройте вкладку "Числа", где находится опция окна "Формат ячеек". В открывшемся диалоговом окне будет категория "Дата", в которой можно выбрать подходящий формат даты.
Если выбрать категорию "Время", появится список вариантов отображения времени.
Чтобы применить к ячейке определенный вариант форматирования, выберите нужный формат и нажмите "OK". Результат будет применен. Если форматов, которые предлагает Excel, вам недостаточно, вы можете найти категорию "Все форматы". Здесь также существует множество вариантов.
Если ни один из вариантов вам не подходит, вы всегда можете создать свой собственный. Это очень легко сделать. Просто выберите предустановленные форматы в качестве образцов и выполните следующие действия:
- Выберите ячейку, формат которой вы хотите изменить.
- Откройте диалоговое окно "Форматирование ячеек" и найдите вкладку "Число".
- Затем откройте категорию "Все форматы", где вы найдете поле ввода "ТИП". Там необходимо ввести цифровой код формата. После ввода нажмите "OK".
- Как только это будет сделано, ячейка отобразит информацию о дате и времени в пользовательском формате.
Использование функций при работе с датами и временем
Имеется более 20 функций даты и времени, которые могут быть использованы пользователями. Хотя для некоторых это число может оказаться слишком большим, все они полезны для определенных целей.
Чтобы получить доступ ко всем доступным функциям, перейдите в категорию "Дата и время" в группе "Библиотека функций". Но мы рассмотрим лишь некоторые из основных функций, позволяющих извлекать различные параметры из дат и времени.
ГОД()
Позволяет получить год, соответствующий определенной дате. Как вы уже знаете, это значение может быть от 1900 до 9999.
В ячейке 1 вы можете увидеть дату, представленную в формате ДДД.ММ.ГГГГ чч:мм:сс. Это формат, который мы создали ранее. Приведем пример формулы, которая определяет, сколько лет прошло между двумя датами.
Если вы присмотритесь, то заметите, что функция вычисляет не совсем правильный результат. Причина в том, что в своих расчетах он использует только даты.
МЕСЯЦ()
Эта функция может быть использована для извлечения номера месяца, соответствующего определенной дате. Он возвращает результат в диапазоне от 1 до 12. Это число, в свою очередь, соответствует номеру месяца.
ДЕНЬ()
Как и предыдущие функции, эта также выдает число дня, на определенную дату. Результат находится в диапазоне от 1 до 31.
ЧАС()
Как можно догадаться из названия, эта функция возвращает номер часа, который находится в диапазоне от 0 до 23.
МИНУТЫ()
Функция, возвращающая количество минут в указанной ячейке. Возможные значения возврата - от 0 до 59.
СЕКУНДЫ()
Эта функция возвращает те же значения, что и предыдущая функция, за исключением того, что она возвращает секунды.
ДЕНЬНЕД()
Эта функция отображает номер дня недели, используемого для указанной даты. Возможные значения - от 1 до 7, но обратите внимание, что отсчет начинается с воскресенья, а не с понедельника, как мы обычно делаем.
Со вторым аргументом эта функция позволяет настроить формат. Например, если вы передадите значение 2 в качестве второго параметра, вы можете настроить формат так, чтобы число 1 обозначало понедельник, а не воскресенье. Это гораздо удобнее для домашнего пользователя.
Если во втором аргументе написать 2, то в нашем случае функция вернет 6, что соответствует субботе.
СЕГОДНЯ()
Эта функция очень проста: для ее работы не нужно вводить никаких аргументов. Он возвращает серийный номер даты, установленной на компьютере. Если применить его к ячейке, имеющей формат Общий, он будет автоматически преобразован в формат Дата.
ТДАТА()
Эта функция также не требует аргументов. Он работает точно так же, как и предыдущий, но с датой и временем. Он используется для вставки в ячейку текущей даты и времени, которые установлены в компьютере. Как и предыдущая функция, эта автоматически преобразует ячейку в формат даты и времени при условии, что формат ранее был установлен на "Общий".
Как предыдущая, так и эта функция автоматически изменяются каждый раз при преобразовании рабочего листа, чтобы отображались самые актуальные время и дата.
Например, эта формула может определить текущее время.
=TDATE()-DAYS()
В этом случае в формуле будет указана доля дня в десятичном формате. Однако вам нужно будет применить формат времени к ячейке, в которую записана формула, если вы хотите, чтобы формат времени был представлен в виде времени, а не числа.
ДАТА()
Эта функция имеет три аргумента, каждый из которых необходимо ввести. После расчета эта функция возвращает порядковый номер даты. Ячейка автоматически преобразуется в формат "Дата", если ранее она была в формате "Общий".
Аргумент 'День' или 'Месяц' может быть положительным или отрицательным. В первом случае дата увеличивается, а во втором - уменьшается.
В аргументах функции DATE также могут использоваться математические операции. Например, эта формула позволяет добавить 1 год 5 месяцев и 17 дней к дате в ячейке A1.
А эта формула позволяет преобразовать текстовую строку в полную рабочую дату, которую можно использовать в других функциях.
ВРЕМЯ()
Как и DATE(), эта функция имеет три обязательных параметра - часы, минуты и секунды. При его использовании результирующая ячейка будет отображаться как десятичное число, но сама ячейка будет отформатирована как "Время", если ранее она была в формате "Общий".
Функции TIME() и DATE() имеют много общего в принципе работы. Поэтому нет необходимости уделять ему особое внимание.
Обратите внимание, что эта функция не может вернуть время больше 23:59:59. Если возвращается результат больше этого значения, функция автоматически сбрасывается.
Функции DATE() и TIME() можно использовать вместе.
На этом снимке ячейка D1, где используются обе функции, имеет формат даты и времени.
Функции вычисления даты и времени
Всего имеется 4 функции, позволяющие выполнять математические операции с датой и временем.
ДАТАМЕС()
С помощью этой функции можно узнать порядковый номер даты, которая находится на известное количество месяцев позже (или раньше) заданной даты. Эта функция принимает два аргумента: начальную дату и количество месяцев. Второй аргумент может быть положительным или отрицательным. Первый должен быть указан, если вычисляется будущая дата, а второй - если вычисляется предыдущая дата.
КОНМЕСЯЦА()
Эта функция позволяет указать порядковый номер последнего дня месяца после или до заданной даты. Он имеет те же аргументы, что и предыдущий.
РАБДЕНЬ()
То же, что и DATAMES(), но задерживается или продвигается на указанное количество рабочих дней. Синтаксис аналогичен.
Все три функции возвращают число. Чтобы увидеть дату, необходимо преобразовать ячейку в соответствующий формат.
ЧИСТРАБДНИ()
Эта простая функция находит количество рабочих дней между датой 1 и датой 2.
