Formuła ogólna
=IF(end>start, end-start, 1-start+end)
Podsumowanie
Aby obliczyć liczbę godzin między dwoma czasami, możesz użyć formuły odejmującej czas rozpoczęcia od czasu zakończenia. Jeśli godziny rozpoczęcia i zakończenia obejmują północ, należy dostosować formułę, jak wyjaśniono poniżej. W przedstawionym przykładzie formuła w D5 to:
=IF(C5>B5,C5-B5,1-B5+C5)
Wyjaśnienie
Aby obliczyć godziny między godzinami, gdy obie godziny przypadają w tym samym dniu kalendarzowym, możesz po prostu odjąć czas rozpoczęcia od godziny zakończenia:
=end-start
I zastosuj formatowanie liczb do wyświetlania godzin.
Jednak gdy czasy przekraczają granicę dnia (północ), sprawy mogą się komplikować. Przeczytaj poniżej, aby zobaczyć kilka sposobów radzenia sobie z tym wyzwaniem.
Jak Excel śledzi czas
W programie Excel jeden dzień to 1, co oznacza 24 godziny. Oznacza to, że czasy i godziny są ułamkowymi wartościami 1, jak pokazano w poniższej tabeli:
| godziny | Czas | Frakcja | Wartość |
|---|---|---|---|
| 3 | 03:00 | 3/24 | 0,125 |
| 6 | 6:00 | 6/24 | 0,25 |
| 4 | 4:00 RANO | 4/24 | 0,167 |
| 8 | 08: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 |
Proste obliczanie czasu trwania
Gdy godzina rozpoczęcia i godzina zakończenia przypadają w tym samym dniu, obliczenie czasu trwania w godzinach jest proste. Na przykład dla godziny rozpoczęcia 9:00 i czasu zakończenia 17:00 możesz po prostu użyć tej formuły:
=end-start =5:00PM-8:00AM =0.375-0.708=.333 // 8hours
Kiedy czasy przekraczają północ
Obliczanie czasu, który upłynął, jest trudniejsze, jeśli czasy przekraczają granicę dnia (północ). Na przykład, jeśli godzina rozpoczęcia to 22:00 jednego dnia, a godzina zakończenia to 5:00 rano następnego dnia, czas zakończenia jest w rzeczywistości krótszy niż czas rozpoczęcia, a powyższa formuła zwróci wartość ujemną, a Excel twill wyświetla ciąg znaków krzyżyka (########).
Aby rozwiązać ten problem, możesz użyć tego wzoru dla czasów, które przekraczają granicę dnia:
=1-start+end
Odejmując czas rozpoczęcia od 1, otrzymujesz czas pierwszego dnia, który możesz po prostu dodać do czasu drugiego dnia, czyli tego samego co czas zakończenia .
Ta formuła nie będzie działać dla razy w tym samym dniu, więc możemy uogólnić i połączyć obie formuły w instrukcji IF w następujący sposób:
=IF(end>start, end-start, 1-start+end)
Teraz, gdy oba czasy są tego samego dnia, koniec jest większy niż czas rozpoczęcia , więc używana jest prosta formuła. Ale gdy czasy przekraczają granicę dnia, używana jest druga formuła.
Alternatywna funkcja MOD
Używając funkcji MOD z dzielnikiem 1, możemy uprościć powyższy wzór do tego:
=MOD(end-start,1)
W tym przypadku funkcja MOD rozwiązuje problem ujemny, używając funkcji MOD do „odwracania” wartości ujemnych do wymaganej wartości dodatniej. Ta wersja formuły będzie obsługiwać oba przypadki, więc możemy wyeliminować warunkową instrukcję JEŻELI.
Uwaga: żadna z powyższych formuł nie obsługuje czasu trwania dłuższego niż 24 godziny. Jeśli tego potrzebujesz, zobacz opcję daty i godziny poniżej.
Aby uzyskać więcej informacji na temat modulo, kliknij dobry link do Khan Academy.
Formatowanie czasu trwania
Domyślnie program Excel może wyświetlać czas, nawet czas reprezentujący czas trwania, używając opcji AM / PM. Na przykład, jeśli masz obliczony czas 6 godzin, program Excel może wyświetlić to jako 6:00 rano. Aby usunąć AM / PM, zastosuj niestandardowy format liczb, taki jak h: mm.
W przypadkach, gdy obliczony czas przekracza 24 godziny, możesz użyć formatu niestandardowego, takiego jak (h): mm. Składnia nawiasu kwadratowego (h) nakazuje programowi Excel wyświetlanie godzin trwania dłuższych niż 24 godziny. Jeśli nie użyjesz nawiasów, program Excel po prostu „przewróci”, gdy czas trwania osiągnie 24 godziny (jak zegar).
Uproszczenie problemu z datą i godziną
Możesz po prostu rozwiązać problem obliczania czasu, który upłynął, pracując z wartościami zawierającymi zarówno datę, jak i godzinę. Aby wprowadzić datę i godzinę razem, użyj pojedynczej spacji między godziną a datą: 2016-09-01 10:00
Następnie możesz użyć podstawowego wzoru do obliczenia upływającego czasu:
=end-start
W poniższym przykładzie wartości początkowe i końcowe zawierają zarówno daty, jak i godziny:
Formuła to:
=C5-B5
Sformatowany przy użyciu niestandardowego formatu liczb (h): mm, aby wyświetlać godziny, które upłynęły.
