Операции со временем в Excel

Операции со временем в Excel
На чтение
29 мин.
Просмотров
14
Дата обновления
06.11.2024

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

К сожалению, многие новички не знают, как обрабатываются данные. Поэтому, прежде чем рассматривать этот класс операций, необходимо провести более подробную образовательную программу.

Как представляется дата в Excel

Обработка информации о дате производится исходя из количества дней, прошедших с 0 января 1900 года. Да, вы не ошиблись. Действительно, с нуля. Но это необходимо для того, чтобы была отправная точка, чтобы 1 января уже считалось номером 1 и так далее. Максимальное поддерживаемое значение даты — 2958465, то есть 31 декабря 9999 года.

Этот метод позволяет использовать даты для расчетов и формул. Затем Excel позволяет определить количество дней между датами. Схема проста: вторая вычитается из числа, затем полученное значение конвертируется в формат даты.

Для наглядности вот таблица, в которой указаны даты с соответствующими числовыми значениями.

Чтобы определить количество дней, прошедших с даты A до даты B, необходимо вычесть первое из последнего. В нашем случае это формула = B3-B2. После его ввода результат будет следующим.Операции со временем в Excel

важно отметить, что значение представлено в днях, потому что мы выбрали формат для ячейки, отличный от даты. Если бы мы изначально выбрали формат «Дата», результат был бы следующим.Операции со временем в Excel

на этот момент важно обращать внимание при расчетах.

Другими словами, чтобы отобразить правильный серийный номер, полностью соответствующий дате, вы должны использовать формат, отличный от даты. В свою очередь, чтобы преобразовать число в дату, нужно установить соответствующий формат. 

Как представляется время в Excel

Время в Excel немного отличается от даты. За основу взят день, а его дробные части — часы, минуты, секунды. То есть 24 часа — это 1, и любое меньшее значение считается его долей. Итак, 1 час — это 1/24 дня, 1 минута — 1/1140, а 1 секунда — 1/86400. Наименьшая единица времени, доступная в Excel, — 1 миллисекунда.

Подобно датам, этот режим представления позволяет выполнять вычисления с течением времени. Правда, здесь неудобно одно. После расчетов получаем часть дня, а не количество дней.

На скриншоте показаны значения в числовом и временном формате».Операции со временем в Excel

Метод исчисления времени аналогичен дате. Вам нужно вычесть предыдущий час из следующего часа. В нашем случае это формула = B3-B2.Операции со временем в Excel

Поскольку до этого ячейка B4 имела общий формат, после окончания ввода формулы она сразу меняется на «Сейчас». 

Excel при работе со временем выполняет обычные арифметические операции с числами, которые затем преобразуются в привычный формат времени. 

Формат дат и времени

Насколько нам известно, даты и время могут храниться в разных форматах. Поэтому вам нужно знать, как их правильно вставить, чтобы форматирование было правильным. 

Конечно, вы можете использовать порядковый номер дня или части дня при вводе даты и времени, но такой подход очень неудобен. Также вам придется постоянно применять к ячейке определенный формат, что только увеличивает дискомфорт.

Таким образом, Excel дает вам возможность указывать время и дату по-разному. Если вы примените его, программа немедленно преобразует информацию в соответствующее число и применит правильный формат к ячейке.

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

На самом деле способов просмотра гораздо больше. Но этого достаточно. Кроме того, конкретный способ записи даты может различаться в зависимости от страны или региона, а также от настроек операционной системы.

Произвольное форматирование

При работе с ячейками пользователь может решить, какой будет формат. Он может отображать только время, месяц, день и так далее. Вы также можете настроить порядок ввода даты и разделители.

Чтобы получить доступ к окну редактирования, вам нужно открыть вкладку «Число», где вы можете найти опцию окна «Формат ячеек». В открывшемся диалоговом окне будет категория «Дата», в которой вы можете выбрать правильный формат даты.Операции со временем в Excel

Если выбрать категорию «Время», то, соответственно, появится список с вариантами отображения времени.Операции со временем в Excel

Чтобы применить к ячейке определенный параметр форматирования, необходимо выбрать нужный формат и нажать «ОК». Далее результат будет применен. Если вам не хватает форматов, предлагаемых Excel, вы можете найти категорию Все форматы. Вариантов тоже много.Операции со временем в Excel

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

  1. Выберите ячейку для форматирования.Операции со временем в Excel
  2. Откройте диалоговое окно «Формат ячеек» и найдите вкладку «Число».
  3. Затем открывается категория «Все форматы», где мы находим поле ввода «ТИП». Там нужно указать код формата номера. После его ввода нажмите «ОК».Операции со временем в Excel
  4. После этих шагов в ячейке отобразится информация о дате и времени в настраиваемом формате.Операции со временем в Excel

Использование функций при работе с датами и временем

При работе с датой и временем пользователь может использовать более 20 различных функций. И хотя для некоторых эта сумма может быть чрезмерной, все они могут быть использованы для достижения определенных целей.

Для доступа ко всем возможным функциям необходимо перейти в категорию «Дата и время» группы «Библиотека функций». Мы рассмотрим лишь некоторые из основных функций, которые позволяют извлекать различные параметры из даты и времени.

ГОД()

Предоставляет возможность получить год, соответствующий определенной дате. Как вы уже знаете, это значение может находиться в диапазоне от 1900 до 9999.Операции со временем в Excel

В ячейке 1 отображается дата в формате ДДДД ДД.ММ.ГГГГ чч: мм: куб. Это формат, который мы создали ранее. Возьмем в качестве примера формулу, которая определяет, сколько лет прошло между двумя датами.Операции со временем в Excel

Кроме того, если приглядеться, выясняется, что функция не вычислила полностью правильный результат. Причина в том, что в расчетах используются только даты.

МЕСЯЦ()

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

ДЕНЬ()

Подобно предыдущим функциям, это обеспечивает номер дня в определенную дату. Результат расчета может быть от 1 до 31.Операции со временем в Excel

ЧАС()

Как следует из названия, эта функция возвращает номер часа в диапазоне от 0 до 23.Операции со временем в Excel

МИНУТЫ()

Функция, возвращающая количество минут в определенной ячейке. Возможные возвращаемые значения от 0 до 59.Операции со временем в Excel

СЕКУНДЫ()

Эта функция возвращает те же значения, что и предыдущая, с единственным исключением, что она возвращает секунды.Операции со временем в Excel

ДЕНЬНЕД()

С помощью этой функции можно узнать номер дня недели, который используется для этой даты. Возможные значения от 1 до 7, но следует учитывать, что отсчет времени начинается с воскресенья, а не с понедельника, как это принято в нашей стране.Операции со временем в Excel

Однако, используя второй аргумент, эта функция позволяет настроить формат. Например, если вы передадите значение 2 в качестве второго параметра, вы можете настроить формат, в котором число 1 означает понедельник, а не воскресенье. Это намного удобнее для домашнего пользователя.Операции со временем в Excel

Если вы напишете 2 во втором аргументе, в нашем случае функция вернет значение 6, что соответствует субботе.Операции со временем в Excel

СЕГОДНЯ()

Эта функция очень проста: вам не нужно вводить никаких аргументов, чтобы она работала. Возвращает серийный номер даты, установленной на компьютере. Если он применяется к ячейке, для которой установлен общий формат, он будет автоматически преобразован в формат «Дата».Операции со временем в Excel

ТДАТА()

Эта функция также не принимает аргументов. Он действует так же, как и предыдущий, только с датой и временем. Используется, если вам нужно ввести в ячейку текущие дату и время, установленные на вашем компьютере. И, как и в предыдущей функции, при применении этой функции ячейка автоматически преобразуется в формат даты и времени, если сначала вы установили «Общий» формат».Операции со временем в Excel

И предыдущая, и эта функция автоматически изменяются при каждом пересчете листа, что позволяет вам просматривать самое последнее время и дату. 

Например, по такой формуле можно определить текущее время.

= TDATA () — СЕГОДНЯ() 

В этом случае формула определит долю дня в десятичном формате. Правда, вам придется применить формат времени к ячейке, где записана формула, если вам нужно отображать время, а не число.Операции со временем в Excel

ДАТА()

Эта функция имеет три аргумента, каждый из которых необходимо ввести. После вычислений эта функция возвращает порядковый номер даты. Ячейка автоматически преобразуется в формат «Дата», если раньше она имела формат «Общий.Операции со временем в Excel

Аргумент «День» или «Месяц» может быть положительным или отрицательным. В первом случае дата увеличивается, а во втором — уменьшается.Операции со временем в Excel

Операции со временем в Excel

вы также можете использовать математические операции в аргументах функции ДАННЫЕ. Например, эта формула добавляет 1 год, 5 месяцев и 17 дней к дате, указанной в ячейке A1.Операции со временем в Excel

И такая формула позволяет превратить текстовую строку в полноценную рабочую дату, которую также можно использовать в других функциях.Операции со временем в Excel

ВРЕМЯ()

Как и функция DATE (), у этой функции есть три обязательных параметра: часы, минуты и секунды. После его использования в результирующей ячейке появится десятичное число, но сама ячейка будет отформатирована в формате «Время», если раньше она имела формат «Общий».Операции со временем в Excel

По принципу действия функции TIME () и DATE () имеют много общего. Поэтому нет смысла заострять на этом внимание. 

важно отметить, что эта функция не может возвращать время больше 23:59:59. Если результат больше, функция автоматически сбрасывается.Операции со временем в Excel

Функции ДАТА () и ВРЕМЯ () можно использовать вместе.Операции со временем в Excel

На этом снимке экрана ячейка D1, которая использовала обе эти функции, имеет формат даты и времени. 

Функции вычисления даты и времени

Всего есть 4 функции, которые позволяют выполнять математические операции с датой и временем.

ДАТАМЕС()

Используя эту функцию, вы можете узнать порядковый номер даты, которая опаздывает на известное количество месяцев (или раньше, чем определенное). Эта функция принимает два аргумента: дату начала и количество месяцев. Второй аргумент может быть положительным или отрицательным. Первый вариант необходимо указать, если вы хотите рассчитать будущую дату, а второй — если предыдущий.Операции со временем в Excel

КОНМЕСЯЦА()

Эта функция позволяет вам определять прогрессивное число последнего дня месяца, в конце или перед определенной датой. Он имеет те же аргументы, что и предыдущий.Операции со временем в Excel

 

РАБДЕНЬ()

Как и функция DATE (), только задержка или опережение происходит в течение определенного количества рабочих дней. Синтаксис такой же.Операции со временем в Excel

Все три функции возвращают число. Чтобы увидеть дату, вам необходимо преобразовать ячейку в соответствующий формат. 

ЧИСТРАБДНИ()

Эта простая функция определяет количество рабочих дней между датой 1 и датой 2.Операции со временем в Excel

0 Комментариев
Комментариев на модерации: 0
Оставьте комментарий