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