Как вычесть время в excel и посчитать разницу во времени
Мы проверили на предыдущем уроке, что дата в Excel – это число количества дней прошедших от 01.01.1900г. А сейчас убедимся что время в Excel – это дробная часть числа, которая представляет собой период суток.
Работать с временем приходиться не редко. Например когда нужно отделу кадров сформировать отчет по балансу рабочего времени сотрудников предприятия. Или производственному отделу нужно посчитать сколько потребуется работо-часов фрезерному станку на изготовления деталей мелкосерийного производства. Время нужно считать инженерам для анализа производительности оборудования при определенных количествах операций.
Как посчитать сумму времени в Excel?
Каждая дробная часть чисел в Excel может отображаться в часах и минутах (в зависимости от выбранного формата отображения).
Данный факт лучше усвоить на конкретном примере:
- Заполните диапазон ячеек A1:A3 числовыми значениями: 0,25 (четверть дня); 0,5(полдень); 0,75 (3/4 дня).
- Выделите A1:A3 и с помощью диалогового окна «Формат ячеек» (CTRL+1) задайте формат «Время», как показано на рисунке:
Чтобы сложить время в Excel по формуле или с помощью функции:=СУММ() нужно использовать специальный формат.
- В ячейку А4 введите:=СУММ(A1:A3). Как видно вместо 36 часов мы видим только 12 – это время следующего дня. А чтобы отобразить сумму часов меняем формат ячейки.
- Открываетм окно «Формат ячеек»-«Число»-«Числовые форматы»-«Все форматы». В поле «Тип:» вводим: [ч]:мм:сс. И нажимаем ОК.
Теперь отображается количество суммированных часов и минут.
Как посчитать разницу во времени в Excel?
Данный метод отображения времени весьма полезный. Допустим нам нужно узнать разницу между временем в минутах. В ячейку B2 введите формулу:=A2-A1. Установите теперь для ячейки «Все форматы» и на этот раз в поле «Тип:» вводим: [мм]. И нажимаем ОК.
Так нам удалось перевести время в минуты. Если мы в эту же ячейку введем число 1 (или время 24:00 что по сути одно и то же), то мы узнаем что в сутках 1440 минут. Так фактически можно в Excel посчитать любое время в минутах.
Примечание. С помощью формата ячеек мы настраиваем способ отображения их значений. Например, например можно добавить или убрать время из даты в Excel.
Учет времени на протяжении суток
На следующем примере посчитаем, какая часть суток прошла от полночи до 9:45.
- В ячейки A1:A3 введите 9:45.
- Перейтите на A1 и вызовите окно «Формат ячеек» (CTRL+1). На закладке «Число», из списка «Числовые форматы» выберите «Дробный».
- Перейдите на A2 и задайте таким же способом «Процентный» формат (или просто нажмите комбинацию горячих клавиш CTRL+SHIFT+5).
- Перейдите на A3 и удалите формат ячейки. А из предыдущих уроков вы знаете, что удалить формат это присвоить формат по умолчанию «Общий». Для этого можно нажать комбинацию CTRL+SHIFT+` или использовать выше описанный способ.
Как видите 9:45 – это 2/5 частей суток. Проще говоря, прошло пока 40,63% суток.
Как поставить текущее время в Excel?
Автоматический ввод текущего времени в Excel выполняется комбинацией горячих клавиш CTRL+SHIFT+;. Автоматическая вставка текущей даты и времени в Excel отличается использованием клавиши SHIFT.
Быстро находите разницу между двумя значениями времени, контролируя формат результата и расширив вычисления до списка времени. Например, можно быстро вычесть одно время из другого или вычислить длительность повестки собрания.
Используйте предоставленные образцы данных и процедуры, чтобы узнать, как вычислять время для повестки собрания.
Копирование примера данных
Чтобы лучше понять функцию Вычитания времени, скопируйте пример данных из примера ниже в ячейку A1 на пустом листе.
Откройте пустую книгу или лист.
Выберите пример данных, включая заголовки.
Повестка дня
Время начала
Время окончания
Длительность
проведения "мозговых штурмов";
Нажмите клавиши +C.
Выберем пустую ячейку A1 на +V.
Примечание: Не выбирайте заголовки строк или столбцов на пустом листе (1, 2, 3. A, B, C. ) при вклеии примера данных.
В этом примере данные в пустую таблицу
Расчет времени между двумя часами и минутами
Выберите ячейку D2 — первую пустую ячейку в столбце Длительность, если скопировали заголовки столбцов на лист вместе с образцом данных.
На вкладке Формулы нажмите кнопку Текст >ТЕКСТ.
В ячейке появится =ТЕКСТ().
В области формул поместите курсор внутри фигуры (),щелкните значение ивведите C2-B2.
Внутри знака ()щелкните format_text ,а затем введите "ч:мм"(включая кавычка), чтобы отформатеть результат как часы и минуты.
Нажмите клавишу RETURN.
Результат — 1:50 (1 час 50 минут).
Выберите ячейку D2.
Чтобы скопировать формулу в оставшиеся строки, перетащите маркер заполнения .
Excel автоматически изменяет ссылки на ячейки, чтобы включить правильные значения для каждой строки.
Дополнительные информацию об этой формуле см. в функции ТЕКСТ.
Если вы используете как формат, примененный к функции ТЕКСТ, так и числовом формате, функция ТЕКСТ имеет приоритет.
Дополнительные примеры формул
Чтобы попробовать примеры, скопируйте таблицу ниже в любую пустую часть листа, содержаную образец данных, а затем щелкните ячейки формулы, чтобы увидеть, как построена формула в построителье формул.
Описание (результат)
=ТЕКСТ (C3-B3,"h")
Часы между двумя ячейками с форматом "а" (4)
=ТЕКСТ (C3-B3,"ч:мм")
Часы и минуты между двумя ячейками с форматом "ч:мм" (4:55)
=ТЕКСТ (C3-B3;"ч:мм:сс")
Часы, минуты и секунды между двумя ячейками с форматом "ч:мм:сс" (4:55:00)
=INT ((C3-B3)*24)
Общее количество часов между двумя временами (4)
= (C3-B3)*1440
Общее количество минут между двумя разами (295)
= (C3-B3)*86400
Общее количество секунд между двумя числами времени (17700)
=ЧАС (C3-B3)
Количество часов между двумя значениями времени, если разница не превышает 24 (4).
=МИНУТЫ (C3-B3)
Количество минут между двумя значениями времени, если разница не превышает 60 (55).
=СЕКУНДЫ (C3-B3)
Количество секунд между двумя значениями времени, если разница не превышает 60 (0).
Копирование примера данных
Чтобы лучше понять описываемые действия, скопируйте пример данных в ячейку A1 пустого листа.
Создайте пустую книгу или лист.
Выделите приведенный ниже образец данных.
Примечание: Не выбирайте заголовки строк или столбцов на пустом листе (1, 2, 3. A, B, C. ) при вклеии образца данных на пустой лист.
Функция РАЗНДАТ позволяет выяснить, сколько полных дней, месяцев или лет прошло между двумя указанными датами. Таким образом можно легко посчитать в Excel: возраст от даты рождения, трудовой стаж, сроки беременности и др. Примеры расчетов точных сроков, возрастов и продолжительности до окончания периодов времени с помощью функции РАЗНДАТ. Как правильно считать разницу между двумя датами при 365 и 366 днях в году?
Примеры использования функции РАЗНДАТ
Пример 1. Требуется вычислить, зная дату рождения работника, сколько полных лет, месяцев и дней ему исполнилось на момент сегодняшней даты.
Используя в качестве аргументов саму дату рождения и функцию СЕГОДНЯ(), а также значения "Y", "M" и "D" для единицы, получим следующий набор формул:
Таким образом мы получили точный возраст в годах, месяцах и днях от даты рождения.
Как точно посчитать сколько прошло времени: лет, месяцев и дней?
Пример 2. В документе записаны даты рождения сотрудников. Требуется выяснить, сколько лет, месяцев и дней исполнилось каждому из них на момент сегодняшнего дня.
Используя уже знакомую формулу =РАЗНДАТ(B2;СЕГОДНЯ();"Y") для ячейки С2 получим количество полных лет возраста каждого из работников. Протянем эту формулу на весь столбец ниже.
Модифицировав формулу, использованную выше, заменяем значение единицы с "Y" на "YM". Итоговый вид формулы: =РАЗНДАТ(B2;СЕГОДНЯ();"YM"). Таким образом, мы получим на выходе количество полных месяцев возраста, не учитывая прошедшие полные года. Протянем формулу на весь столбец ниже.
В конце нам понадобятся и дни, не вошедшие в месяц. Снова заменяем значение единицы с "YM" на "YD" и получим формулу следующего вида, в которой на выходе будет число дней: =РАЗНДАТ(B2;СЕГОДНЯ();"YD").
Такое решение можно элегантно улучшить, если использовать функцию СПЕЦПИТЬ() и вызвать окошко аргументов, приведя функцию в следующий вид: =СЦЕПИТЬ(C2;" лет ";D2;" месяцев ";E2;" дня(-ей)")
Как посчитать сроки окончания действия договора в Excel
Пример 3. В документе записаны даты заключения договоров сотрудников компании. По умолчанию договор заключается на 1 год. Требуется выяснить, сколько дней осталось до момента окончания трудового договора у каждого из сотрудников, чтобы подготовить новые экземпляры для продления договоров.
С помощью функции РАЗНДАТ определим, сколько дней прошло с момента заключения трудового договора. В новом столбце для ячейки С2 используем формулу: =РАЗНДАТ(B2;СЕГОДНЯ();"d") и протянем ее на весь столбец ниже.
Подсчитаем количество оставшихся дней до окончания трудового договора каждого из сотрудников. Для этого в новом столбце вычитаем от количества дней в году значения из столбца С, используя формулу:
Сначала мы определяем количество дней во году по дате (365 или 366) по формуле ЕСЛИ(ОСТАТ(ГОД(B2);4);365;366), а после вычитаем, чтобы узнать сколько осталось -C2.
Для удобства можно добавить еще одну колонку, в которой с помощью условий указать замечание для продления договора, если осталось меньше 10 дней до его окончания. Для ячейки E2 используем формулу:
10;"Действует";ЕСЛИ(D2
В данной формуле используется функция ЕСЛИ с несколькими условиями. Если до конца срока больше 10-ти суток тогда формула возвращает значение «Действует», а если меньше 10-то тогда одно из условий:
- Если меньше 10-ти суток, но больше чем 0 – значение «Заканчивается».
- Если меньше чем 0, тогда «Просрочен».
Особенности использования функции РАЗНДАТ
- — нач_дата : дата, от которой будет вестись расчет;
- — кон_дата : дата, до которой будет вестись расчет;
- — единица : аргумент, указывающий на величину, в которой будет производиться расчет.
Примечание: аргументы нач_дата и кон_дата могут принимать как числовое значение, так и быть результатом какого-либо вычисления, производимого вне ячеек (например, функции ДАТА(), функции СЕГОДНЯ()).
Как преобразовать разницу во времени между двумя значениями времени в число (часы / минуты / секунды) в Excel?
Обычно при вычислении разницы во времени с вычитанием в Excel вы получаете результат в формате чч: мм или чч: мм: сс. Для прямого преобразования разницы во времени в числа, такие как часы / минуты / секунды, только после расчета, методы, описанные в этой статье, окажут вам услугу.
Easily convert time to number (hours/minutes/seconds) in selected range in Excel:
After calculating the time difference which format as hh:mm:ss, the Convert Time utility of Kutools for Excel can help you to quickly convert time to hours, minutes or seconds with only clicks. See screenshot:
Kutools for Excel: with more than 200 handy Excel add-ins, free to try with no limitation in 60 days. Download and free trial Now!
- Reuse Anything: Add the most used or complex formulas, charts and anything else to your favorites, and quickly reuse them in the future.
- More than 20 text features: Extract Number from Text String; Extract or Remove Part of Texts; Convert Numbers and Currencies to English Words.
- Merge Tools : Multiple Workbooks and Sheets into One; Merge Multiple Cells/Rows/Columns Without Losing Data; Merge Duplicate Rows and Sum.
- Split Tools : Split Data into Multiple Sheets Based on Value; One Workbook to Multiple Excel, PDF or CSV Files; One Column to Multiple Columns.
- Paste Skipping Hidden/Filtered Rows; Count And Sum by Background Color ; Send Personalized Emails to Multiple Recipients in Bulk.
- Super Filter: Create advanced filter schemes and apply to any sheets; Sort by week, day, frequency and more; Filter by bold, formulas, comment.
- More than 300 powerful features; Works with Office 2007-2019 and 365; Supports all languages; Easy deploying in your enterprise or organization.
Преобразуйте разницу во времени в число (часы / минуты / секунды) с помощью формул
Чтобы преобразовать разницу во времени в число, сделайте следующее.
Перевести разницу во времени в часы
Выберите пустую ячейку (в данном случае я выбираю C2), введите в нее приведенную ниже формулу и нажмите Enter ключ. Затем перетащите маркер автозаполнения вниз, чтобы отобразить все разницы во времени в формате часов. Смотрите скриншот:
= (B2-A2) * 24
Преобразование разницы во времени в минуты
Для перевода разницы во времени в минуты используйте формулу ниже.
= (B2-A2) * 1440
Преобразовать разницу во времени в секунды
Приведенная ниже формула может помочь вам напрямую преобразовать разницу во времени в формат секунд, как показано ниже.
= (B2-A2) * 86400
Внимание: В приведенных выше формулах. A2 содержит время начала, а B2 - время окончания. Вы можете менять их по своему усмотрению.
Легко конвертируйте разницу во времени в часы / минуты / секунды с помощью замечательного инструмента
Здесь очень рекомендую Помощник по дате и времени of Kutools for Excel для быстрого расчета разницы во времени и отображения в часах, минутах или секундах всего несколькими щелчками мыши.
Перед применением Kutools for Excel, Пожалуйста, сначала скачайте и установите.
1. Выберите пустую ячейку для вывода разницы во времени, щелкните Кутулс > Формула Помощник >Помощник по дате и времени.
2. в Помощник по дате и времени диалоговое окно, настройте следующим образом.
Тогда вы увидите разницу во времени в часах, минутах или секундах между указанными временами.
Советы: Формула будет автоматически вставлена в выбранную ячейку, вы можете перетащить маркер автозаполнения вниз, чтобы получить другую разницу во времени.
Если вы хотите получить бесплатную (30-дневную) пробную версию этой утилиты, пожалуйста, нажмите, чтобы загрузить это, а затем перейдите к применению операции в соответствии с указанными выше шагами.
Excel
Microsoft Excel можно использовать для расчетов бюджета, налоговых расчетов и многих других целей. Вы можете использовать его в качестве другой формы калькулятора благодаря функциям, которые он нам предоставляет. Вы даже можете использовать Excel для расчета разницы между двумя значениями. Это помогает рассчитать количество часов, которые сотрудник проработал между заданным временем начала и окончания. С помощью Excel можно вести полный учет рабочего времени сотрудника, что помогает нам рассчитывать заработную плату, которая будет выплачиваться за сотрудника.
Рассчитать разницу во времени в Excel
Трудно посчитать разницу между двумя значениями в Excel. Но с небольшим количеством хитрости мы можем достичь желаемого результата. Просто следуйте этой статье до конца, и там вы сможете отлично рассчитать разницу во времени в Excel. Вот так!
Просто рассмотрите пример данных, как показано ниже,
Итак, как уже говорилось, если мы просто попытаемся вычесть «Время окончания» из «Время начала», вы столкнетесь с двумя проблемами и посмотрим, что это.
Вы можете видеть, что к этому результату добавлено «AM» или «PM», чего мы не ожидаем. Вторая проблема – это показывает ошибки хеша. Поскольку мы выполнили прямое вычитание, в некоторых случаях результат может быть отрицательным, и поскольку время не должно быть отрицательным, Excel отображает его как ошибки хеширования.
Мы можем решить первую проблему, изменив формат чисел, выполнив следующие простые шаги
- Выберите ячейки, которые имеют результат (общее количество часов в нашем случае) и нажмите ‘CTRL + 1’, чтобы открыть диалоговое окно форматирования ячеек.
- Нажмите на «Custom» и в поле «type» введите «h: mm» и нажмите «Ok».
Таким образом, вы можете избавиться от первой проблемы. Это несколько удовлетворительно, но даже если это не полное решение, которое мы ищем. Нам по-прежнему необходимо устранить ошибки хэширования в рассчитанных полных часах. Чтобы решить вторую проблему, вам нужно изменить формулу, которую вы использовали на первом шаге (Время окончания – Время начала, т.е. «E14-E13»). Выберите строку «Всего часов» и нажмите «F2», чтобы изменить формулу. Сделайте формулу следующим образом
E14-E13 + (E13> E14)
Теперь вместо нажатия клавиши Enter просто нажмите «CTRL + Enter», чтобы результат был применен ко всем выделенным ячейкам за один снимок. Таким образом, мы можем избежать изменения формулы для каждой ячейки.
Мы все знаем, что 24-часовая система используется Excel. Основная сложность, используемая здесь для решения хеш-ошибок, – это добавление логической операции. Тем самым мы преодолели отрицательный результат, поскольку «ИСТИНА» обрабатывается как «1», а «ЛОЖЬ» – как «0». Наконец, разница во времени не показывает отрицательные значения, иначе больше нет ошибок хеширования.
Это простой, удобный и совершенный способ подсчета разницы во времени в Microsoft Excel.
Читайте также: