Время в Excel

Как представляется дата в 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 недостаточно форматов, вы можете найти категорию «Все форматы». Вариантов тоже много.

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

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

Суммирование времени

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

1. Выделите соответствующие ячейки. Если это диапазон, удерживая левую кнопку мыши, нарисуйте вокруг него круг. Если мы имеем дело с отдельными ячейками, разбросанными по листу, то мы делаем их выделение, кстати, зажав клавишу Ctrl на клавиатуре.
2. Щелкаем правой кнопкой мыши, вызывая контекстное меню. Переходим к пункту «Формат ячейки…». Вместо этого вы также можете нажать Ctrl + 1 после выбора на клавиатуре.

Откроется окно форматирования. Перейдите во вкладку «Номер», если она была открыта в другой вкладке. В блоке параметров «Числовые форматы» переводим переключатель в положение «Время». В правой части окна в блоке «Тип» выберите тип отображения, с которым мы будем работать. После завершения настройки нажмите кнопку «ОК» в нижней части окна.

Способ 1: показание часов через промежуток времени

Сначала давайте посмотрим, как рассчитать, сколько часов будут отображаться через определенный период времени, выраженный в часах, минутах и ​​секундах. В нашем конкретном примере нам нужно узнать, сколько часы покажут между 1 часом 45 минутами и 51 секундой, если время сейчас 13:26:06.

В отформатированной области листа в разных ячейках с помощью клавиатуры введите данные «13:26:06» и «1:45:51».

В третьей ячейке, где также установлен формат времени, введите знак «=». Затем нажмите на ячейку с временем «13:26:06», нажмите на знак «+» на клавиатуре и нажмите на ячейку со значением «1:45:51».

Для просмотра результата расчета на экране нажмите кнопку «Ввод».

Внимание! Используя этот метод, вы можете узнать, сколько часы покажут через определенный промежуток времени, только в течение одного дня. Чтобы иметь возможность «перепрыгнуть» дневной лимит и знать, сколько времени часы будут показывать одновременно, обязательно выберите тип формата со звездочкой при форматировании ячеек, как на изображении ниже.

Способ 2: использование функции

Альтернативой предыдущему методу является использование функции СУММ.

После ввода первичных данных (считывания текущего времени и временного диапазона) выделите отдельную ячейку. Нажмите кнопку «Вставить функцию».

Мастер откроется. Ищем функцию «СУММ» в списке элементов. Выберите его и нажмите кнопку «ОК».

Откроется окно аргументов функции. Установите курсор в поле «Число 1» и щелкните ячейку, содержащую текущее время. Затем мы помещаем курсор в поле «Число2» и щелкаем по ячейке, указывающей время для добавления. Заполнив оба поля, нажмите кнопку «ОК».

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

Способ 3: суммарное сложение времени

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

Но сначала нам придется отформатировать ячейки иначе, а не так, как описано в предыдущих вариантах. Выделите область и вызовите окно форматирования. На вкладке «Число» переместите переключатель «Форматы чисел» в положение «Дополнительно». В правой части окна находим и устанавливаем значение «h: mm: ss». Чтобы сохранить изменение, нажмите кнопку «ОК».

Затем вам нужно выбрать диапазон, заполненный значением времени, и пустую ячейку после него. На вкладке «Главная» щелкните значок «Сумма», расположенный на ленте панели инструментов «Редактировать». Или вы можете набрать Alt + = на клавиатуре+=».

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

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

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

Как вы можете видеть в следующих разделах, Excel упрощает добавление и вычитание времени.

Сложение значений времени

Допустим, вы хотите знать, сколько часов и минут потребуется на выполнение двух задач. Предполагается, что первое занятие займет 6 часов 45 минут, а второе — 9 часов 30 минут.

Вот способ персонализировать на листе.

Введите 6:45 в ячейку B2 и введите 9:30 в ячейку B3.

В ячейке B4 введите = B2 + B3 и нажмите Enter.

Результат: 16 — 16 часов 15 минут — выполнить два задания.

Совет: вы также можете складывать время, используя функцию автосуммирования чисел. Выберите ячейку B4, затем на вкладке «Главная» выберите «Автосумма». Формула будет выглядеть так: = СУММ (B2: B3). Нажмите Enter, чтобы получить тот же результат, 16 часов 15 минут.

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

Чтобы добавить более 24 часов, выполните следующие действия.

Введите 12:45 в ячейку B2 и 15:30 в ячейку B3.

В ячейке B4 введите формулу = B2 + B3 и нажмите Enter.

Результат 4:15, чего нельзя было ожидать. Это связано с тем, что время для задачи 2 имеет 24-часовой формат. 15:30 равно 3:30.

Чтобы просмотреть значение времени больше 24 часов, выберите ячейку B4.

На вкладке Главная в группе Ячейки щелкните Форматировать, а затем щелкните Формат ячеек.

В диалоговом окне «Формат ячеек» в списке «Категория» выберите (все форматы).

В поле типа вверху списка форматов введите h: mm; @ и нажмите ОК.

Нарисуйте заметку с двоеточием после h и точкой с запятой после мм.

Результат — 28 часов 15 минут. Формат сохраняется в списке Тип для использования в будущем.

Вычитание значений времени

Вот еще один пример. Допустим, вы и ваши друзья знаете время начала и окончания «волонтерского» проекта и хотели бы знать, сколько времени было потрачено на совместное использование.

Следуйте инструкциям ниже, чтобы узнать прошедшее время (разницу между двумя временами).

В ячейке B2 введите время начала и укажите «a» для AM или «p» для PM. Затем нажмите Enter.

В ячейке C2 введите время окончания, например «a» или «p», затем нажмите Enter.

Введите разное время начала и окончания для своих друзей Сергея и Николая.

В ячейке D2, чтобы вычесть время окончания из времени начала, введите формулу = C2-B2, затем нажмите Enter.

В диалоговом окне «Формат ячеек» в списке «Категория» выберите (все форматы).

В списке Тип выберите h: мм (часы и минуты) и нажмите OK.

Теперь вы видите, что Григорий проработал 3 часа 45 минут.

Чтобы получить результаты для Сергея и Николая, скопируйте формулу, выделив ячейку D2 и перетащив ее в ячейку D4.

Параметры форматирования в ячейке D2 копируются вместе с формулой.

Чтобы вычесть время, превышающее 24 часа, сделайте следующее.

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

Следуйте инструкциям ниже.

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

В диалоговом окне «Формат ячеек» в списке «Категория» выберите (все форматы).

В поле Тип вверху списка форматов введите дм.гггг ч: мм.

Обратите внимание на пустое пространство в конце гггг и в конце мм.

В будущем новый формат будет доступен в списке Тип.

В ячейке B1 введите дату начала, включая день, месяц, год.

Выполните те же действия для даты окончания в ячейке B2.

Введите формулу в ячейку B3: = (B2-B1) * 24.

Результат 31,5 часа.

Примечание. В ExcelOnline можно складывать и вычитать более 24 часов, но нельзя применять настраиваемые числовые форматы.

Сложение значений времени

Допустим, вы хотите знать, сколько часов и минут потребуется на выполнение двух задач. Предполагается, что первое занятие займет 6 часов 45 минут, а второе — 9 часов 30 минут.

Введите 6:45 в ячейку B2 и 9:30 в ячейку B3.

В ячейке B4 введите формулу = B2 + B3 и нажмите Enter.

Оба упражнения занимают 16 часов 15 минут.

Совет: вы также можете добавить время с помощью автосуммы. Щелкните ячейку B4. Затем щелкните Главная> Автосумма. Формула будет выглядеть так: = СУММ (B2: B3). Нажмите Enter, чтобы получить результат: 16 часов 15 минут.

Пример1 — используем ссылки

Допустим, столбец A содержит часы, столбец B — минуты.

Формула = ВРЕМЯ (A2; B2;) в ячейке C2 вернет время в числовом формате (0,88 для 21:00). Чтобы отобразить значение в стандартном формате времени чч: мм, откройте диалоговое окно Формат ячеек, нажав CTRL + 1, выберите формат числа времени, затем выберите соответствующий формат в поле Тип.

Пример2 — прибавляем минуты

Добавьте 75 минут к 13:23 (значение находится в ячейке A1). Для этого в ячейке B1 запишите формулу = A1 + TIME (; 75;)

Формула вернет значение 14:38

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

При работе с датой и временем пользователь может использовать более 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, которая использовала обе эти функции, имеет формат даты и времени.

Метод 1: использование формулы

Этот метод самый простой и предполагает использование формулы умножения. Чтобы преобразовать часы в минуты, необходимо сначала умножить указанное время на 60 (количество минут в часе), а затем на 24 (количество часов в сутках). Другими словами, нам нужно умножить время на 1440. Давайте попробуем практический пример.

1. Мы находимся в ячейке, где ожидаем отобразить результат в виде количества минут. Ставя знак равенства, пишем формулу умножения. Координаты ячейки с исходным значением (в нашем случае — C4) можно указать вручную, либо просто щелкнув по ней левой кнопкой мыши. Затем нажмите клавишу Enter.
   
2. Следовательно, мы не получим в точности то, что ожидали, а именно значение «0:00”.
   
3. Это связано с тем, что при отображении результата программа ориентируется на форматы ячеек, входящих в формулу. Таковые в нашем случае результирующей ячейке присваивается формат «Время». Вы можете изменить его на «Общие» как на вкладке «Главная» (панель инструментов «Число»), как обсуждалось выше, так и в окне формата ячейки, к которому можно получить доступ через контекстное меню ячейки, вызываемое щелчком правой кнопкой мыши по Это.
   
   Оказавшись в окне форматирования в списке слева, выберите строку «Общие» и нажмите кнопку ОК.
   
4. В результате мы получаем общее количество минут в любой момент времени.
   
5. Если вам нужно преобразовать часы в минуты для всего столбца, совсем не обязательно делать это отдельно для каждой ячейки, потому что процесс можно автоматизировать. Для этого наведите указатель мыши на ячейку с формулой, как только появится черный знак плюса (маркер заливки), удерживая левую кнопку мыши, перетащите его в последнюю ячейку, для которой необходимо произвести соответствующие вычисления.
   
6. Все готово, благодаря этому простому действию мы смогли быстро преобразовать часы в минуты для всех значений столбцов.
   

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

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

ДАТАМЕС()

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

КОНМЕСЯЦА()

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

РАБДЕНЬ()

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

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

Пример3 — вычисляем количество секунд

Пусть ячейка содержит значение времени 0:20:35, т.е. 20 минут и 35 секунд. Посчитаем, сколько секунд содержится в этом значении.

Используя формулу = ВРЕМЯ (0; 20; 35), введите значение времени, указанное в ячейке A1. Это значение времени соответствует числу 0,014294.

Напомним, что 1 секунда в числовом формате равна 1/24/60/60.

Чтобы рассчитать количество секунд, разделите значение в ячейке A1 на 1/24/60/60. Для этого напишите формулу = A1 / (1/24/60/60) .

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