Általános képlet
=IF(end>start, end-start, 1-start+end)
Összegzés
Kétszer eltelt órák számításához használjon olyan képletet, amely kivonja a kezdési időt a befejezési időből. Ha a kezdési és befejezési idő éjfélig tart, akkor a képletet az alábbiakban leírtak szerint kell beállítania. A bemutatott példában a D5 képlete a következő:
=IF(C5>B5,C5-B5,1-B5+C5)
Magyarázat
Az idők közötti órák kiszámításához, amikor mindkét idő ugyanazon naptári napon van, egyszerűen kivonhatja a kezdési időt a befejezési időből:
=end-start
És alkalmazza a számformázást az órák megjelenítéséhez.
Amikor azonban az idő átlépi a napi határt (éjfél), a dolgok bonyolulttá válhatnak. Az alábbiakban olvashatja el a kihívás kezelésének több módját.
Hogyan követi az Excel az időt
Az Excel programban egy nap 1-nek felel meg, ami 24 órát jelent. Ez azt jelenti, hogy az idők és az órák 1-es tört értékek, az alábbi táblázat szerint:
| Órák | Idő | Töredék | Érték |
|---|---|---|---|
| 3 | 03:00 | 3/24 | 0,125 |
| 6. | 06:00 | 6/24 | 0,25 |
| 4 | 4:00 AM | 4/24 | 0,167 |
| 8. | REGGEL 8:00 ÓRA | 8/24 | 0,333 |
| 12. | 12:00 PM | 12/24 | 0.5 |
| 18 | 18:00 | 18/24 | 0,75 |
| 21 | ESTE 9:00 | 21/24 | 0,875 |
Egyszerű időtartam-számítás
Ha a kezdési és a befejezési idő ugyanazon a napon van, akkor az időtartam órákban történő kiszámítása egyszerű. Például 9:00 AM kezdési idővel és 17:00 PM befejezési idővel egyszerűen ezt a képletet használhatja:
=end-start =5:00PM-8:00AM =0.375-0.708=.333 // 8hours
Amikor az idők átkelnek éjfélkor
Az eltelt idő kiszámítása bonyolultabb, ha az idők átlépik a napi határt (éjfél). Például, ha a kezdés időpontja egy nap 22:00, a vége pedig másnap 5:00, a befejezési idő valójában kevesebb, mint a kezdési idő, és a fenti képlet negatív értéket ad vissza, és az Excel a twill egy hash karakterből álló karakterláncot jelenít meg (########).
A probléma kijavításához használhatja ezt a képletet azokra az időkre, amelyek átlépik a napi határt:
=1-start+end
Ha levonja a kezdési időt 1-ről, akkor megkapja az első nap időtartamát, amelyet egyszerűen hozzáadhat a 2. nap időtartamához, amely megegyezik a befejezési idővel .
Ez a képlet nem fog működni ugyanazon a napon, így általánosíthatjuk és kombinálhatjuk mindkét képletet egy IF utasításban:
=IF(end>start, end-start, 1-start+end)
Most, amikor mindkét idő ugyanazon a napon van, a vég nagyobb, mint a kezdési idő , ezért az egyszerű képletet alkalmazzuk. De amikor a napi határokon átnyúló idők a második képletet alkalmazzák.
MOD funkció alternatív
A MOD függvény 1-es osztóval történő használatával egyszerűsíthetjük a fenti képletet:
=MOD(end-start,1)
Itt a MOD funkció gondoskodik a negatív problémáról azáltal, hogy a MOD funkcióval negatív értékeket a kívánt pozitív értékre "fordít". A képlet ezen verziója mindkét esetet kezeli, így kiküszöbölhetjük a feltételes IF utasítást.
Megjegyzés: a fenti képletek sem kezelik a 24 óránál hosszabb időtartamokat. Ha erre van szüksége, olvassa el az alábbi dátum és idő opciót.
A modulo-ról itt talál egy jó linket a Khan Akadémián.
Időtartamok formázása
Alapértelmezés szerint az Excel az AM / PM használatával megjelenítheti az időt, még az időt is, amely egy időtartamot jelent. Például, ha a számított idő 6 óra, akkor az Excel ezt 6: 00-kor jelenítheti meg. Az AM / PM eltávolításához alkalmazzon egy egyedi számformátumot, például h: mm.
Olyan esetekben, amikor a számított idő meghaladja a 24 órát, érdemes egyéni formátumot használni, például (h): mm. A szögletes zárójeles szintaxis (h) azt mondja az Excel-nek, hogy 24 óránál hosszabb óraszámot jelenítsen meg. Ha nem használja a zárójeleket, akkor az Excel egyszerűen "átgördül", amikor az időtartam 24 órát ér el (mint egy óra).
A probléma egyszerűsítése a dátum és az idő segítségével
Egyszerűen megoldhatja az eltelt idő kiszámításának problémáját, ha olyan értékekkel dolgozik, amelyek dátumot és időt egyaránt tartalmaznak. A dátum és az idő együttes megadásához használjon egyetlen szóközt az idő és a dátum között: 2016.09.01. 10:00
Ezután egy alapképlet segítségével kiszámíthatja az eltelt időt:
=end-start
Az alábbi példában a kezdő és a végérték egyaránt tartalmazza a dátumot és az időt:
A képlet:
=C5-B5
Formázva az egyedi számformátummal (h): mm, az eltelt órák megjelenítéséhez.
