Обща формула
=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 | 3: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 AM на следващия ден, крайният час всъщност е по-малък от началния час и горната формула ще върне отрицателна стойност кепър показва низ от хеш символи (########).
За да коригирате този проблем, можете да използвате тази формула за пъти, които преминават граница на деня:
=1-start+end
Като извадите началното време от 1, получавате времето за първия ден, което можете просто да добавите към времето за втория ден, което е същото като крайното време .
Тази формула няма да работи за пъти в един и същи ден, така че можем да обобщим и комбинираме двете формули в оператор IF по следния начин:
=IF(end>start, end-start, 1-start+end)
Сега, когато и двата пъти са в един и същи ден, краят е по-голям от началния час , така че се използва простата формула. Но когато времето през дадена граница се използва втората формула.
Алтернатива на функцията MOD
Използвайки функцията MOD с делител 1, можем да опростим формулата по-горе до това:
=MOD(end-start,1)
Тук функцията MOD се грижи за отрицателния проблем, като използва функцията MOD, за да „обърне“ отрицателните стойности до необходимата положителна стойност. Тази версия на формулата ще се справи и с двата случая, така че можем да премахнем условния оператор IF.
Забележка: нито една формула по-горе не може да се справи с продължителността, по-голяма от 24 часа. Ако имате нужда от това, вижте опцията за дата и час по-долу.
За повече информация по модул, ето една добра връзка в Khan Academy.
Форматиране на продължителността на времето
По подразбиране Excel може да показва време, дори време, което представлява продължителност, използвайки AM / PM. Например, ако имате изчислено време от 6 часа, Excel може да покаже това като 6:00 AM. За да премахнете AM / PM, приложете персонализиран формат на числа като h: mm.
В случаите, когато изчисленото време надвишава 24 часа, може да искате да използвате персонализиран формат като (h): mm. Синтаксисът на квадратната скоба (h) казва на Excel да показва продължителност на часа, по-голяма от 24 часа. Ако не използвате скобите, Excel просто ще се „преобърне“, когато продължителността достигне 24 часа (като часовника).
Опростяване на проблема с дата + час
Можете просто да решите проблема с изчисляването на изминалото време, като работите със стойности, които съдържат както дата, така и час. За да въведете дата и час заедно, използвайте едно интервал между час и дата: 01.09.2016 г. 10:00 ч
След това можете да използвате основна формула за изчисляване на изминалото време:
=end-start
В примера по-долу началните и крайните стойности съдържат както дати, така и часове:
Формулата е:
=C5-B5
Форматиран с персонализиран формат на числото (h): mm, за да покаже изминалите часове.
