Загальна формула
=MOD(time+(hours/24),1)
Резюме
Для перетворення часу з одного часового поясу в інший можна використовувати формулу, яка перетворює години, введені як цілі числа, у десяткові значення, які Excel розпізнає як раз. У наведеному прикладі формула в F5 має вигляд:
=MOD(C5+(E5/24),1)
Ця формула повертає число, яке Excel розпізнає як 02:00.
Пояснення
Часи в Excel - це дробові значення числа 1. Отже, 12 PM - це 12/24 = .5, 6:00 AM - 6/24 = .25 тощо. Отже, щоб перетворити час на задане число, потрібно розділити кількість годин на 24, щоб отримати необхідне десяткове значення:
E5/24 // convert adjustment to Excel time
Додаємо результат до часу початку:
C5+(E5/24)
Щоб переконатися, що ми маємо справжнє значення часу, нам потрібно переконатися, що ми маємо лише десяткове значення. Іншими словами, якщо додати 12 годин (.5) до 6 вечора (.75), ми отримаємо 1,25, але насправді хочемо лише 0,25.
Щоб переконатися, що ми отримуємо лише десяткове значення, ми використовуємо функцію MOD із дільником 1, як розумний спосіб спростити формулу.
MOD повертає залишок після ділення, тому повертає десяткове значення у випадках, коли результат перевищує 1 (тобто перевищує 24 години).
Ще краще, якщо ми отримаємо негативне дробове значення, MOD поверне зворотне. Отже, якщо у нас вийде -25, MOD поверне 0,75 (еквівалентно 6 вечора).
Це важливо, оскільки 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 |
Працюючи з датами, які включають як дату, так і час (час часу), вам не потрібно використовувати MOD, оскільки немає потреби робити щось розумне, оскільки час перетинає півночі. Операція стає простим додаванням, оскільки дата включена, і ви можете скористатися такою формулою:
=datetime+(hours/24)
Це дозволить змінювати значення дати за необхідності (вперед або назад), коли коригування часу перетинає 00:00.
