Excel сумма времени в сводной таблице

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

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

Как посчитать разницу во времени в Excel (прошедшее время)

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

Вычесть одно время из другого

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

Самая простая и очевидная формула Excel чтобы посчитать время от одного момента до другого:

= Время окончания — Время начала

В зависимости от ваших данных, формула разницы во времени может принимать различные формы, например:

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

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

Чтобы применить пользовательский формат времени, используйте комбинацию клавиш Ctrl + 1, чтобы открыть диалоговое окно «Формат ячеек», выберите «Пользовательский»  и введите шаблон формата в поле «Тип». Подробные инструкции вы можете найти в статье Создание пользовательского формата времени в Excel .

А теперь давайте разберем это на простых примерах. Если время начала находится в столбце B, а время окончания — в столбце A, вы можете использовать эту простую формулу:

=$A2-$B2

Прошедшее время отображается по-разному в зависимости от использованного формата времени, как видно на этом скриншоте:

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

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

Еще один простой метод расчета продолжительности между двумя временами в Excel — применение функции ТЕКСТ:

• Рассчитать часы между двумя временами:   =ТЕКСТ(A2-B2; «ч»)
• Рассчитать часы и минуты:                             =ТЕКСТ(A2-B2;»ч:мм»)
• Посчитать часы, минуты и секунды:              =ТЕКСТ(A2-B2;»ч:мм:сс»)

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

Примечание:

• Значение, возвращаемое функцией ТЕКСТ, всегда является текстом. Обратите внимание на выравнивание по левому краю содержимого столбцов C:E на скриншоте выше. В некоторых случаях это может быть существенным ограничением, поскольку вы не сможете использовать полученное «текстовое время» в других вычислениях.
• Если результатом является отрицательное число, ТЕКСТ возвращает ошибку #ЗНАЧ!.

Как сосчитать часы, минуты или секунды

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

Как рассчитать количество часов.

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

=( Время окончания — Время начала ) * 24

Если начальное время записано в A2, а время окончания – в B2, вы можете использовать простое выражение B2-A2, чтобы вычислить разницу между ними, а затем умножить результат на 24. Это даст количество часов:

=(B2-A2)* 24

Чтобы получить количество полных часов, используйте функцию ЦЕЛОЕ, чтобы округлить результат до ближайшего целого числа:

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

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

Считаем сколько минут в интервале времени.

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

=( Время окончания — Время начала ) * 1440

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

=(B2-A2)*1440

Как сосчитать количество секунд.

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

=( Время окончания — Время начала ) * 86400

В нашем примере расчет выглядит следующим образом:

=(B2-A2)* 86400

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

Как посчитать разницу в каждой единице времени.

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

• Разница в часах без учета минут и секунд:

=ЧАС(B2-A2)

• Разница в минутах без учета часов и секунд:

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

• Разница в секундах без учета часов и минут:

=СЕКУНДЫ(B2-A2)

При использовании функций Excel ЧАС, МИНУТЫ и СЕКУНДЫ помните, что результат не может превышать 24 для часов, и 60 – для минут и секунд.

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

Примечание. Однако,если в ячейку вы записали не просто время, а дату и время, и при этом дата окончания окажется меньше даты начала (т. е. результатом будет отрицательное число), то вы увидите ошибку #ЧИСЛО!.

Как перевести секунды в часы, минуты и секунды

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

Давайте рассмотрим небольшой пример.

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

Вот как это будет:

Дни:

=ЦЕЛОЕ(A2/(60*60*24))

Часы:

=ЦЕЛОЕ(A2/(60*60)) — ЦЕЛОЕ(A2/(60*60*24))*24

Минуты:

=ЦЕЛОЕ(A2/60) — ЦЕЛОЕ(A2/(60*60))*60

Секунды – это просто две последние цифры:

=—ПРАВСИМВ(A2;2)

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

=ЦЕЛОЕ(A2/(60*60))

Конечно, здесь результат может быть больше 24.

Минуты и секунды подсчитываем, как и прежде.

Еще один вариант перевода секунд в дни, часы, минуты и секунды вы можете посмотреть на скриншоте ниже.

Расчет времени с момента начала до настоящего момента

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

Предположим, что начальная дата и время находятся в A2, выражение =ТДАТА()-A2 возвращает следующие результаты, при условии, что вы применили соответствующий формат времени к столбцу B (в этом примере «ч:мм») :

Если прошло больше чем 24 часа, используйте один из этих форматов времени , например Д «дн.» ч:мм:сс, как показано на скриншоте ниже:

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

=ВРЕМЯ(ЧАС(ТДАТА()); МИНУТЫ(ТДАТА()); СЕКУНДЫ(ТДАТА())) — A2

Примечание. Прошедшее время не обновляется в режиме реального времени, оно заново рассчитывается только при повторном открытии или пересчете рабочей книги. Чтобы принудительно обновить результат, нажмите либо Shift + F9, чтобы пересчитать активную таблицу, или F9 для пересчета всех открытых книг. Еще один способ быстрого пересчета – внесите изменения в любую ячейку вашего рабочего листа.

Отображение разницы во времени как «XX дней, XX часов, XX минут и XX секунд».

Это, вероятно, самая удобная формула для расчета разницы во времени в Excel. Вы используете функции ЧАС, МИНУТЫ и СЕКУНДЫ для возврата соответствующих единиц времени, и функцию ЦЕЛОЕ для вычисления разницы в днях. Затем вы объединяете все эти функции в одно выражение вместе с текстовыми пояснениями:

=ЦЕЛОЕ(B2-A2)&» дн., «&ЧАС(B2-A2)&» час., «&МИНУТЫ(B2-A2)&» мин. и «&СЕКУНДЫ(B2-A2)&» сек.»

Вот как это может выглядеть:

Чтобы скрыть нулевые результаты в формуле разницы во времени в Excel, встройте в нее четыре функции ЕСЛИ:

=ЕСЛИ(ЦЕЛОЕ(B2-A2)>0; ЦЕЛОЕ(B2-A2) & » дн.,  «;»») &

ЕСЛИ(ЧАС(B2-A2)>0; ЧАС(B2-A2) & » час.,  «;»») &

ЕСЛИ(МИНУТЫ(B2-A2)>0; МИНУТЫ(B2-A2) & » мин. и  «;»») &

ЕСЛИ(СЕКУНДЫ(B2-A2)>0; СЕКУНДЫ(B2-A2) & » сек.»;»»)

Синтаксис может показаться чрезмерно сложным, но это работает

Кроме того, вы можете рассчитать разницу во времени, просто вычитая время начала из времени окончания (например =B2-A2 ), а затем применив к ячейке следующий формат времени:

Д «дн.,» ч «час.,» м «мин. и » с «сек.»

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

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

Как рассчитать и отобразить отрицательное время в Excel

При расчете разницы во времени в Excel вы иногда можете получить результат в виде ошибки ######, если вдруг она представляет собой отрицательное число. Это происходит, если время начала больше, чем время окончания события. Такое часто случается, если рассчитывают продолжительность каких-то событий без учета даты. Получается, что время окончания меньше, чем время начала, поскольку это просто время следующего дня. К примеру, мы начинаем работу в 17 часов и заканчиваем на следующий день в 10 часов.

Но есть ли способ правильно отображать отрицательное время в Excel? Конечно, способ есть, и даже не один.

Способ 1. Измените систему дат Excel на систему дат 1904 года.

Самый быстрый и простой способ нормально отображать отрицательное время (со знаком минус) — это перейти на систему дат 1904 года. Для этого нажмите « Файл» > «Параметры» > «Дополнительно », прокрутите вниз до раздела « При расчете этой книги» и поставьте галочку в поле «Использовать систему дат 1904».

Нажмите OK , чтобы сохранить новые настройки, и теперь отрицательные величины времени будут отображаться правильно, как отрицательные числа:

Как видите, время начала больше, чем время окончания, и результат получен со знаком «минус».

Способ 2. Рассчитать отрицательное время в Excel с помощью формул

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

=ЕСЛИ(A2-B2>0; A2-B2; «-» & ТЕКСТ(ABS(A2-B2);»ч:мм»))

=ЕСЛИ(A2-B2>0; A2-B2; ТЕКСТ(ABS(A2-B2);»-ч:мм»))

Они обе проверяют, является ли отрицательной разница во времени (A2-B2). Если она меньше нуля, первая формула вычисляет абсолютное значение (без учёта знака) и объединяет этот результат со знаком «минус». Вторая формула делает точно такой же расчет, но использует отрицательный формат времени «-ч::мм «.

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

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

На случай если время окончания меньше, чем время начала, мы можем проверить это при помощи функции ЕСЛИ.

=ЕСЛИ(B2<A2;B2+1;B2)-A2

Если разница во времени отрицательная, ко времени окончания добавляем 1 день (24 часа).  В результате получим правильную разницу во времени, но без знака «минус».

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

По сути, есть 2 способа сложения и вычитания времени в Excel:

• При помощи функции ВРЕМЯ.
• Применяя арифметические вычисления, основанные на количестве часов (24), минут (1440) и секунд (86400) в одних сутках.

Функция ВРЕМЯ(часы; минуты; секунды) делает вычисления времени в Excel очень простыми, однако не позволяет добавлять или вычитать более 23 часов, 59 минут или 59 секунд. 

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

Как добавить или вычесть часы

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

Функция ВРЕМЯ для добавления до 24 часов

= Время начала + ВРЕМЯ( N часов ; 0 ; 0)

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

=A2 + ВРЕМЯ(4; 0; 0)

Примечание. Если вы попытаетесь добавить более 23 часов с помощью функции ВРЕМЯ, указанные часы будут разделены на 24, а остаток от деления будет добавлен к времени начала. Например, если вы попытаетесь добавить 28 часов к «10:00» (ячейка A2) с помощью формулы =A4 + ВРЕМЯ(28; 0; 0), результатом будет «14:00», т. е. A2 + 4 часа.

Как добавить любое количество часов (меньше или больше 24 часов)

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

= Время начала + ( N часов / 24)

Например, чтобы добавить 36 часов к времени начала в ячейке A2:

=A2 + (36/24)

Чтобы вычесть часы из заданного времени, вы используете аналогичные формулы и просто заменяете «+» знаком «-»:

Например, чтобы вычесть 40 часов из времени в ячейке A2, можно употребить формулу:

=A2-(40/24)

Если вычитаем менее чем 24 часа, то используйте функцию ВРЕМЯ:

=A2 — ВРЕМЯ(4; 0; 0)

Как прибавить или вычесть минуты

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

Чтобы добавить или вычесть менее 60 минут

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

= Время начала + ВРЕМЯ(0; N минут ; 0)

Вот несколько примеров для расчета минут в Excel:

Чтобы добавить 30 минут ко времени в A2:  =A2 + ВРЕМЯ(0;30;0)

Чтобы вычесть 50 минут из времени в A2:     =A2 — ВРЕМЯ(0;50;0)

Как добавить или вычесть более 60 минут

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

= Время начала + ( N минут / 1440)

Чтобы вычесть минуты из времени, просто замените плюс знаком минус. Например:

Чтобы добавить 600 минут:     =A2 + (600/1440)

Чтобы вычесть 600 минут:        =A2 — (600/1440)

Как прибавить или вычесть секунды

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

Чтобы добавить менее 60 секунд к заданному времени, вы можете использовать функцию ВРЕМЯ:

= Время начала + ВРЕМЯ(0; 0; N секунд )

Чтобы добавить более 59 секунд, используйте следующую формулу:

= Время начала + ( N секунд / 86400)

86400 – это количество секунд в сутках.

Чтобы вычесть секунды, используйте те же формулы, только со знаком минус (-) вместо плюса (+).

Это может выглядеть примерно так:

Чтобы добавить 50 секунд к A2:         =A2 + ВРЕМЯ(0;0;50)

Чтобы добавить 1500 секунд к A2:      =A2 + (1500/86400)

Чтобы вычесть 25 секунд из A2:          =A2 — ВРЕМЯ(0;0;25)

Чтобы вычесть 2500 секунд из A2:      =A2 — (2500/86400)

Примечание:

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

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

Формула суммирования времени в Excel — это обычная функция СУММ, и применение правильного формата времени к результату — это то, что помогает получить верный результат.

Предположим, у вас есть сведения об отработанном времени в столбце B, и вы хотите его суммировать, чтобы посчитать общее количество рабочих часов за неделю. Вы пишете простую формулу СУММ 

=СУММ(B2:B8)

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

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

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

• Чтобы отобразить общее время в часах и ​​минутах: [ч]:мм
• Чтобы отобразить общее время в минутах и ​​секундах: [м]:сс
• Чтобы отобразить общее время в секундах: [сс]

Примечание. Вышеупомянутые пользовательские форматы времени работают только для положительных значений. Если результатом ваших расчетов времени является отрицательное число, например, когда вы вычитаете большее время из меньшего, результат будет отображаться как #####. Чтобы по-другому отображать отрицательное время, см. раздел Пользовательский формат для отрицательных значений времени .

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

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

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

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

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

Как быстро вставить сегодняшнюю дату в Excel? — Это руководство показывает различные способы ввода дат в Excel. Узнайте, как вставить сегодняшнюю дату и время в виде статической метки времени или динамических значений, как автоматически заполнять столбец или строку…

Количество рабочих дней между двумя датами в Excel — Довольно распространенная задача: определить количество рабочих дней в период между двумя датами – это частный случай расчета числа дней, который мы уже рассматривали ранее. Тем не менее, в Excel для…