☰

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

tag-icon • tag-icon microsoft-excel worksheet-function date-time
Как рассчитать количество часов > 24 в Excel?

Ниже приведен фрагмент моей таблицы. Это приложение для отслеживания времени, содержащее дату/день начала, идентификатор сеанса и его продолжительность. Столбец 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

Как вы можете видеть, разница дат между A2 и A3 показывает, что значение 22:00 в D2 представляет собой продолжительность в 46 часов (23.05 10:00 + 46:00 = 25.05 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, чтобы получить точные сводные данные?

решение1

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

Есть один нюанс: внутри 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 даст вам правильную новую дату и время.

решение2

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

=(A3-A2)*24

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

решение3

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

  1. Введите часы в столбец и минуты в отдельный столбец. Введите сказатьчасыв клеткеА1иминутв колонкеВ1

  2. В конце каждого из столбцов выше добавьте числа, например:А1-А19иB1-B19как простые числа в столбцахА20 и В20

    Введите формулу в ячейкуА20что читается=СУММ(A1:A19)И вВ20что читается=СУММ(B1:B19)

  3. Введите формулу в ячейкуА21что читается=А20+(В20-В21)/60и в ячейкеВ21что читается=MOD(B20,60)

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

P.S.:Вы можете скопировать и вставить формулы с этой страницы!

решение4

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

Связанный контент