Как рассчитать количество часов> 24 в Excel?

Question

У меня есть выдержка из моей таблицы ниже. Это приложение для отслеживания времени, содержащее дату / день начала, идентификатор сеанса и продолжительность сеанса. Столбец A заполняется путем добавления длительности в столбец D. Я пытаюсь суммировать длительности, но продолжаю получать неправильные значения в случаях, когда общая длительность составляет> 24 часа.

Рассматривать

     A                   B        C                D         
1  2012/05/22 01:00  |  Wed  |  Session 23341  |  9:00
2  2012/05/23 10:00  |  Wed  |  Session 23342  | 22:00
3  2012/05/25 08:00  |  Fri  |                 |  0:00

Как видите, разница между датами А2 и А3 показывает, что значение 22:00 в D2 соответствует продолжительности 46 часов (23.05 10:00 + 46:00 = 05/25 08:00). Однако в столбце D отображается только 22:00 сверх 24-часового дня.

Мои попытки прочитать D2 включали int(d2) = 0.00 / int(d2)*24 = 0.00 / hour(d2) = 22.00 / int(a3-a2)*24 =0.00 и т.д. И не смогли получить значение из "46".

Как я могу точно представлять D2 для получения точных сводных данных?

Answer 1

Excel может отображать время в формате отметки времени, но для этого вам нужно определить собственный формат ячейки , например:[h]:mm:ss - обратите внимание на скобки, которые обозначают, что часы не переворачиваются на 24, но продолжай считать. Если вы определите это и назначите его для столбца D, вы сможете ввести продолжительность 46:00:00 в D2 (вы можете пропустить секунды в своем формате, если они вам, конечно, не нужны).

Есть ошибка: внутренне, Excel все еще хранит время, считая вверх от его 0 базового значения времени даты (1 января 1900, 00:00 - если вы не установите его для использования 1904 подсказок даты и времени, чтобы разрешить отрицательные значения, в этом случае это будет через 4 года), поэтому все семейство функций времени (HOUR , MINUTE , SECOND) будет по-прежнему возвращать значение в пределах 24-часового периода времени (по модулю деления на 24, умножить на 24 или 22 в вашем случае, чтобы быть точным).

• Если вы хотите отобразить часы, минуты или секунды такой отметки времени, вам придется применить другой формат времени к ее значению (например, [h] для часов, m для минут, s для секунд). Обратите внимание, что это влияет только на отображение - внутренне значение все еще является обычным значением даты и времени, как описано выше;
• Вы можете вычислить свободно, если все операнды имеют тип даты и времени. Если вашему значению в A1 назначен формат «дата-время» yyyy/dd/mm hh:mm , ввод =A1+D1 в ячейку A2 даст вам правильные новые дату и время.

Answer 2

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

1. Введите часы в столбце и минуты в отдельном столбце. Введите, скажем, часы в ячейке A1 и минуты в столбце B1.

2. В конце каждого из приведенных выше столбцов добавьте числа, например, от A1 до A19 и от B1 до B19, в виде простых чисел в столбцах A20 и B20.

   Вставьте формулу в ячейку A20 со значением = SUM(A1:A19) и в B20 со значением = SUM(B1:B19).

3. Вставьте формулу в ячейку A21, которая гласит = A20+(B20-B21)/60, и в ячейку B21, которая гласит = MOD(B20,60)

Вуаля! , Значения Ячейки A21 и B21 дают итоговые значения в часах и минутах. сильный текст

PS: Вы можете вырезать и вставлять формулы с этой страницы!

Answer 3

Обратите внимание, что вы можете умножить на 24, чтобы получить часы в десятичном виде, поэтому эта формула должна вернуть 46

=(A3-A2)*24

форматировать ячейку результата как число

Answer 4

Возможно, разделите дату и время на разные столбцы, чтобы затем можно было использовать новый столбец B для выполнения вычисления в новом столбце E