Общая формула
=MOD(time+(hours/24),1)
Резюме
Чтобы преобразовать время из одного часового пояса в другой, вы можете использовать формулу, которая преобразует часы, введенные как целые числа, в десятичные значения, которые Excel распознает как время. В показанном примере формула в F5:
=MOD(C5+(E5/24),1)
Эта формула возвращает число, которое Excel распознает как 2:00 ночи.
Объяснение
Время в Excel - это дробное значение числа 1. Таким образом, 12 часов дня - это 24 декабря = 0,5, 6 часов утра - это 24 июня = 0,25 и так далее. Итак, чтобы преобразовать время по заданному числу, вам нужно разделить количество часов на 24, чтобы получить требуемое десятичное значение:
E5/24 // convert adjustment to Excel time
Добавляем результат к времени старта:
C5+(E5/24)
Чтобы убедиться, что у нас есть истинное значение времени, нам нужно убедиться, что у нас есть только десятичное значение. Другими словами, если мы добавим 12 часов (0,5) к 18 часам вечера (0,75), мы получим 1,25, но на самом деле нам нужно только 0,25.
Чтобы убедиться, что мы получаем только десятичное значение, мы используем функцию MOD с делителем 1, как умный способ упростить формулу.
MOD возвращает остаток после деления, поэтому возвращает десятичное значение в случаях, когда результат больше 1 (т.е. больше 24 часов).
Еще лучше, если мы получим отрицательное дробное значение, MOD вернет обратное. Итак, если мы получим -25, MOD вернет 0,75 (эквивалент 18:00).
Это важно, потому что Excel не отображает отрицательные значения времени.
Дата и время
Некоторые значения даты включают дату и время и иногда называются «датой». Эти значения включают как серийный номер, представляющий дату, так и дробное значение, представляющее время. В таблице ниже приведены некоторые примеры:
| Дата и время | Исходное значение |
|---|---|
| 06.03.18 6:00 | 43165,25 |
| 1 января 1999 г. 21:00 | 36161,875 |
| 01.04.2020 0:00 | 43922 |
| 3 июня 1980 г., 12:00 | 29375,5 |
При работе с датами, которые включают как дату, так и время (datetimes), вам не нужно использовать MOD, потому что нет необходимости делать что-то умное, когда время пересекает полночь. Операция превращается в простое добавление, потому что дата включена, и вы можете использовать такую формулу:
=datetime+(hours/24)
Это позволит изменять значение даты по мере необходимости (вперед или назад), когда корректировка времени пересекает 12:00.
