Рассчитать сложение и вычитание времени (разница во времени, рабочее время) в Excel

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

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

В этом уроке я покажу вам, как Выполнение вычислений с использованием времени в Excelнесколько методов (таких как расчет разницы во времени, сложение и вычитание времени, отображение времени в разных форматах и ​​расчет суммы значений времени).

Как Excel обрабатывает дату и время?

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

Например, значение 1 представляет 1900 января 1 года в Excel, что является отправной точкой для Excel, чтобы начать учитывать даты.

Таким образом, 2 означает 1990 января 1 года, 2 — 3 января 1900 года и так далее, 1 — 3 января 44197 года.

Примечание: Excel для Windows и Excel для Mac имеют разные даты начала.1 в Excel для Windows означает 1900 января 1 г., 1 в Excel для Mac означает 1 января 1904 г.

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

Например, 44197.5 означает 2021 января 1 года, 1:12:00.

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

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

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

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

Во многих случаях все, что вам нужно сделать, это найти общее время, прошедшее между двумя значениями времени (например, в случае расписания с In-time и Out-time).

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

Давайте рассмотрим несколько примеров

Простое вычитание для расчета разницы во времени в Excel

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

время окончания - время начала

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

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

Возможно, ваши результаты будут отображаться в формате времени (а не в десятичном формате или в часах/минутах).В приведенном выше примере результат в ячейке C2 показывает 09:30 вместо 9.5.

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

Чтобы преобразовать его в десятичный, измените формат ячейки на «Общий» (эта опция находится на вкладке «Главная» группы «Числа»)

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

Вот различные форматы, которые вы можете использовать:

формат	что он может сделать
hh	Показывать только количество часов, прошедших между двумя датами
mm	Отображение часов в виде двух цифр (например, 04 или 12)
чч: мм	Отображает часы и минуты, прошедшие между двумя датами, например, 10:20.
чч: мм: сс	Отображает часы, минуты и секунды, прошедшие между двумя датами, например, 10:20:36.

Если вам интересно, где и как применить эти настраиваемые форматы даты, выполните следующие действия:

1. Выберите ячейки, чтобы применить формат даты к
2. Удерживайте Control и нажмите 1 (Command + 1 при использовании Mac)
3. В открывшемся диалоговом окне «Формат ячеек» щелкните вкладку «Числа» (если она еще не выбрана).
4. На левой панели нажмите «Настроить».
5. Введите любой желаемый код формата в поле «Тип» (в этом примере я использую чч: мм: сс)
6. Нажмите ОК

Вышеуказанные шаги изменят формат и отобразят значение в соответствии с форматом.

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

СОВЕТ ПРОФЕССИОНАЛА: если общее количество часов превышает 24 часа, используйте вместо этого следующий пользовательский числовой формат: [чч]:мм:сс

Рассчитать разницу во времени в часах, минутах или секундах

Когда вы вычитаете значения времени, Excel возвращает десятичное число, представляющее результирующую разницу во времени.

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

Вычислить разницу во времени в часах

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

Приведенная ниже формула даст вам разницу во времени в часах:

= (B2-A2) * 24

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

Иногда Excel пытается помочь, а также выдает результаты в формате времени (показано ниже).

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

Если вы просто хотите вернуть общее количество часов, прошедших между двумя моментами (без дробной части), используйте эту формулу:

=ЦЕЛОЕ((B2-A2)*24)

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

Вычислить разницу во времени в минутах

Чтобы рассчитать разницу во времени в минутах, нужно полученное значение умножить на общее количество минут в сутках (т.е. 1440 или 24*60).

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

Вот формула, которая может это сделать:

=(В2-А2)*24*60

Вычислить разницу во времени в секундах

Чтобы вычислить разницу во времени в секундах, нужно полученное значение умножить на общее количество секунд в сутках (т.е. 24*60*60 или 86400).

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

Вот формула, которая может это сделать:

=(B2-A2)*24*60*60

Рассчитать разницу во времени с помощью функции ТЕКСТ

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

Функция ТЕКСТ позволяет задавать форматирование в формулах.

=ТЕКСТ(дата_конца-дата_начала, формат)

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

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

Вот несколько формул, которые дадут вам результаты в разных форматах.

Показать только часы:

=ТЕКСТ(B2-A2;"чч")

Приведенная выше формула даст вам только результат количества часов, прошедших между двумя значениями времени.Если ваш результат равен 9 часам 30 минутам, он все равно будет показывать только 9.

Показать общее количество минут

=ТЕКСТ(B2-A2;"[мм]")

Показать общее количество секунд

=ТЕКСТ(B2-A2;"[сс]")

Отображение часов и минут

=ТЕКСТ(B2-A2;"[чч]:мм")

Отображение часов, минут и секунд

=ТЕКСТ(B2-A2;"чч:мм:сс")

Если вам интересно, в чем разница между чч и [чч] в формате (или мм и [мм]), когда вы используете квадратные скобки, это дает вам общее количество часов между двумя датами, даже если значения часа больше 24.Поэтому, если вы вычитаете два значения даты, которые отличаются более чем на 24 часа, использование [чч] даст вам общее количество часов, а чч даст вам только количество часов, прошедших на дату окончания.

Получить разницу во времени в одной единице (часы/минуты), игнорируя другие

Если вы просто хотите рассчитать разницу во времени между двумя значениями времени в часах, минутах или секундах, вы можете использовать специальные функции HOUR, MINUTE или SECOND.

Каждая из этих функций принимает один параметр, значение времени, и возвращает указанную единицу времени.

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

Вот формула для этого:

Подсчитать количество часов, прошедших между двумя повторениями

=ЧАС(B2-A2)

Рассчитать минуты на основе результата значения времени (исключая полные часы)

=МИНУТА(B2-A2)

Вычислить секунды из результата значения времени (исключая завершенные часы и минуты)

= ВТОРОЙ (B2-A2)

Несколько вещей, которые нужно знать при использовании этих формул ЧАСОВ, МИНУТ и СЕКУНД:

• Разница между временем окончания и временем начала не может быть отрицательной (обычно это происходит при изменении даты).В этом случае эти формулы вернут #ЧИСЛО!ошибка
• В этих формулах используется только часть времени (и игнорируется часть дней) результирующего значения времени.Таким образом, если разница между временем окончания и временем начала составляет 2 дня, 10 часов, 32 минуты и 44 секунды, формула ЧАСов даст 10, формула МИНУТ даст 32, а формула СЕКУНДА даст 44.

Рассчитать время, прошедшее сейчас (от времени начала)

Если вы хотите рассчитать общее время, прошедшее между временем начала и текущим временем, вы можете использовать формулу СЕЙЧАС вместо времени окончания.

Функция СЕЙЧАС возвращает текущую дату и время в ячейке, в которой она используется.Это одна из функций, которая не принимает никаких входных аргументов.

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

=СЕЙЧАС() - время начала

Ниже приведен пример, где у меня есть время начала в столбце A и время до сих пор в столбце B.

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

Это можно сделать с помощью следующей формулы ТЕКСТ:

=ТЕКСТ(СЕЙЧАС()-A2,"дд чч:сс:мм")

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

Если время начала содержит только временную часть, Excel считает его 1990 января 1 года.

В этом случае, если вы используете функцию СЕЙЧАС для вычисления времени, прошедшего до настоящего момента, это даст вам неверный результат (поскольку результирующее значение также будет содержать общее количество дней, прошедших с 1990 января 1 года).

В этом случае можно использовать следующую формулу:

=СЕЙЧАС()-INT(СЕЙЧАС())-A2

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

Обратите внимание, что NOW — это энергозависимая функция, которая обновляется при изменении листа, но не в режиме реального времени.

Рассчитать время при изменении даты (рассчитать и отобразить отрицательное время в Excel)

Описанные до сих пор методы хорошо работают, если ваше время окончания позже времени начала.

Однако проблема возникает, когда время окончания меньше времени начала.Обычно это происходит, когда вы заполняете табель учета рабочего времени и вводите только время, а не всю дату и время.

В этом случае, если вы работаете в ночную смену и дата меняется, время окончания может быть раньше времени начала.

Например, если вы начинаете работу в 6:00, а заканчиваете работу и делаете перерыв в 9:00.

Если бы вы использовали только значения времени, то вычитание времени начала из времени окончания дало бы отрицательное значение 9 часов (9–18).

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

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

К счастью, есть очень простой обходной путь.

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

Как вы заметите, иногда время начала — вечер, а время окончания — утро (это указывает на то, что это была ночная смена, а день изменился).

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

= B2-A2

Это формула ЕСЛИ, если значение разницы во времени отрицательное, если оно отрицательное, возвращается правильный результат.

=IF((B2-A2)<0,1-(A2-B2),(B2-A2))

Хотя в большинстве случаев это работает хорошо, этого все же недостаточно, если время начала и окончания отличается более чем на 24 часа.Например, кто-то входит в систему в 1:9 в первый день и выходит из системы в 00:2 во второй день.

Поскольку это более 24 часов, невозможно узнать, бросил ли человек курить через 2 часа или через 26 часов.

Хотя лучший способ исправить это — убедиться, что запись включает дату и время, если это просто время, с которым вы работаете, то приведенная выше формула должна решить большую часть проблемы (учитывая, что маловероятно, что кто-то будет работать больше, чем 24 часа)

Сложение и вычитание времени в Excel

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

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

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

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

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

Например, если вы добавите 1 к дате в Excel, вы получите следующую дату.Это связано с тем, что 1 представляет полный день (эквивалентно 24 часам) в Excel.

Поэтому, если вы хотите добавить 1 час к существующему значению времени, вы не можете просто добавить к нему 1.Вы должны обязательно преобразовать значение часа в дробную часть, представляющую час.То же самое касается добавления минут и секунд.

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

Функции времени в Excel принимают значения часов, минут и секунд и преобразуют их в десятичное число, представляющее время.

Например, если бы я хотел добавить 4 часа к существующему времени, я мог бы использовать следующую формулу:

= время начала + ВРЕМЯ (4,0,0)

Это полезно, если вы знаете часы, минуты и секунды, которые нужно добавить к существующему времени, и можете просто использовать функцию ВРЕМЯ, не беспокоясь о правильном преобразовании времени в десятичное значение.

Также обратите внимание, что функция ВРЕМЯ учитывает только целую часть введенных вами значений часов, минут и секунд.Например, если я использую 5.5 часов в функции ВРЕМЯ, она просто добавляет 5 часов и игнорирует дробную часть.

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

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

Точно так же, как я использовал функцию ВРЕМЯ, чтобы добавить время, вы также можете вычесть время.Просто измените знак + в приведенной выше формуле на знак минус.

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

Хотя функция времени проста в использовании, у нее есть некоторые ограничения (как упоминалось выше).

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

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

Например, если вы хотите добавить 24 часа к существующему значению времени, вы можете использовать следующую формулу:

= время начала + 24/24

Это просто означает, что я добавляю день к существующему значению времени.

Теперь, используя ту же концепцию, допустим, вы хотите добавить 30 часов к значению времени, вы можете использовать следующую формулу:

= время начала + 30/24

Приведенная выше формула делает то же самое, где целая часть (30/24) представляет собой общее количество дней времени, которое вы хотите добавить, а дробная часть представляет собой часы/минуты/секунды.

Аналогичным образом, если вы хотите добавить к значению времени определенное количество минут, вы можете использовать следующую формулу:

=Начальное время +(Добавить минуты)/24*60

Если у вас есть секунды, чтобы добавить, вы можете использовать эту формулу:

= время начала + (дополнительные минуты)/24*60*60

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

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

Как суммировать время в Excel

Иногда может потребоваться быстро добавить все значения времени в Excel.Добавлять несколько значений времени в Excel легко (всего лишь простая формула СУММ)

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

Давайте посмотрим на пример.

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

В ячейке B9 я использовал простую формулу СУММ для расчета общего времени выполнения всех этих задач, и она дает мне значение 18:30 (это означает, что для выполнения всех этих задач потребуется 18 часов и 20 минут).

Все идет нормально!

Как суммировать более 24 часов в Excel

Теперь посмотрите, что произойдет, если я изменю время выполнения задачи 2 с 1 часа на 10 часов.

Результат теперь показывает 03:20, что означает, что для выполнения всех этих задач потребуется 3 часа 20 минут.

Это неправильно (очевидно)

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

И поскольку результирующее значение здесь превышает 24 часа, Excel решает преобразовать 24-часовую часть в день, удалить ее из значения, отображаемого пользователю, и показать только оставшиеся часы, минуты и секунды.

К счастью, это легко исправить.

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

Вот несколько форматов, которые вы можете использовать:

формат	Ожидаемые результаты
[Н]: мм	28:30
[м]: сс	1710:00
д «Д» чч:мм	День 04 30:XNUMX
d «D» hh «Min» ss «Sec»	1 день 04 минуты 00 секунд
d «День» hh «Минута» ss «Секунда»	1 день 04 минуты 00 секунд

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

Вы можете использовать следующую формулу ТЕКСТ для отображения времени, даже если оно превышает 24 часа:

=ТЕКСТ(СУММ(B2:B7);"[ч]:мм:сс")

Или, если вы хотите преобразовать часы более 24 часов в дни, используйте следующую формулу:

=ТЕКСТ(СУММ(B2:B7);"дд чч:мм:сс")

Результаты показывают решетку (###) вместо даты/времени (причина + исправление)

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

Вот некоторые возможные причины и способы их устранения:

Колонки недостаточно широкие

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

Это легко исправить - изменить ширину столбца и сделать его шире.

отрицательное значение даты

Значения даты или времени в Excel не могут быть отрицательными.Если вы вычисляете разницу во времени и результат отрицательный, Excel отобразит знак решетки.

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

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

В этом уроке я рассмотрел темы расчета времени в Excel (где вы можете вычислять разницу во времени, добавлять и вычитать время, отображать время в разных форматах и ​​суммировать значения времени).

Я надеюсь, что вы нашли этот урок полезным.

Часто задаваемые вопросы о расчете сложения и вычитания времени в Excel