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