Загальна формула
=IF(end>start, end-start, 1-start+end)
Резюме
Для обчислення кількості годин між двома разами можна скористатися формулою, яка віднімає час початку від часу закінчення. Якщо час початку та кінця охоплює опівночі, вам потрібно буде скоригувати формулу, як пояснено нижче. У наведеному прикладі формула в D5 має вигляд:
=IF(C5>B5,C5-B5,1-B5+C5)
Пояснення
Щоб обчислити години між часами, коли обидва рази припадають на один і той же календарний день, ви можете просто відняти час початку від часу закінчення:
=end-start
І застосуйте форматування чисел для відображення годин.
Однак, коли рази перетинають денну межу (опівночі), все може стати складним. Прочитайте нижче, щоб побачити кілька способів вирішити цю проблему.
Як Excel відстежує час
В Excel один день дорівнює 1, що означає 24 години. Це означає, що час і години є дробовими значеннями 1, як показано в таблиці нижче:
| Годин | Час | Дріб | Значення |
|---|---|---|---|
| 3 | 03:00 | 3/24 | 0,125 |
| 6 | 6:00 | 6/24 | 0,25 |
| 4 | 4:00 | 4/24 | 0,167 |
| 8 | 8:00 | 8/24 | 0,333 |
| 12 | 12:00 вечора | 12/24 | 0,5 |
| 18 | 18:00 | 18/24 | 0,75 |
| 21 | 21:00 | 21/24 | 0,875 |
Простий розрахунок тривалості
Коли час початку та час закінчення знаходяться в одному дні, обчислення тривалості в годинах є простим. Наприклад, з часом початку 9:00 та кінцем 17:00, ви можете просто скористатися цією формулою:
=end-start =5:00PM-8:00AM =0.375-0.708=.333 // 8hours
Коли перетинаються опівночі
Обчислення минулого часу є складнішим, якщо час переходить межу доби (опівночі). Наприклад, якщо час початку - 22:00 одного дня, а час завершення - 5:00 наступного дня, час закінчення насправді менший за час початку, і формула вище поверне негативне значення, і Excel twill відображає рядок хеш-символів (########).
Щоб виправити цю проблему, можна скористатися цією формулою для випадків, які перетинають денну межу:
=1-start+end
Віднімаючи час початку з 1, ви отримуєте кількість часу в перший день, який ви можете просто додати до кількості часу у 2-й день, що збігається з часом закінчення .
Ця формула не працюватиме разів в один день, тому ми можемо узагальнити та поєднати обидві формули всередині оператора IF так:
=IF(end>start, end-start, 1-start+end)
Тепер, коли обидва рази в один день, кінець більше, ніж час початку , тому використовується проста формула. Але коли межі часу через добу використовується друга формула.
Альтернатива функції MOD
Використовуючи функцію MOD із дільником 1, ми можемо спростити наведену вище формулу до цього:
=MOD(end-start,1)
Тут функція MOD опікується негативною проблемою, використовуючи функцію MOD, щоб "перевернути" негативні значення до необхідного додатного значення. Ця версія формули буде обробляти обидва випадки, тому ми можемо усунути умовне твердження IF.
Примітка: жодна з наведених формул не обробляє тривалість, що перевищує 24 години. Якщо вам це потрібно, дивіться опцію дати та часу нижче.
Щоб отримати докладнішу інформацію про модуль, ось гарне посилання на Академію Хан.
Форматування тривалості часу
За замовчуванням Excel може відображати час, навіть час, що представляє тривалість, за допомогою AM / PM. Наприклад, якщо у вас розрахований час 6 годин, Excel може відображати це як 6:00 ранку. Щоб видалити AM / PM, застосуйте власний формат чисел, наприклад h: mm.
У випадках, коли розрахований час перевищує 24 години, можливо, ви захочете використовувати власний формат, такий як (h): mm. Синтаксис квадратних дужок (h) вказує Excel відображати тривалість годин, що перевищує 24 години. Якщо ви не використовуєте дужки, Excel просто "перевернеться", коли тривалість досягне 24 годин (як годинник).
Спрощення проблеми з датою + часом
Ви можете просто вирішити проблему обчислення минулого часу, працюючи зі значеннями, які містять як дату, так і час. Щоб ввести дату та час разом, використовуйте єдиний пробіл між часом і датою: 01.09.2016 10:00
Тоді ви можете використовувати основну формулу для обчислення минулого часу:
=end-start
У наведеному нижче прикладі значення початку і кінця містять як дати, так і час:
Формула:
=C5-B5
Форматований у користувацькому форматі чисел (год): мм для відображення минулих годин.
