0

I'm trying to help a friend with making him a simple excel template for filling out a time sheet, unfortunately it turned out much harder than i expected a simple time-calculation to be.

What i need is to calculate the time difference between two time fields. So far it's easy but unfortunately it's a night job that regularly passes midnight and then excel simple doesn't get any calculations right without falling back to full date+time fields stuff.

So right now i do this:

=IF(C8>B8; C8-B8; TIMEVALUE("23:59")+TIMEVALUE("00:01")-B8+C8)

Given the start hour is in B8, and the end time is in C8.

I can't believe there is no easier way of doing it with this hack.

Also the calculation fails for empty values (empty sheet!) displaying #VALUE! which i cant't seem to solve. No statement like IF(NOT(ISBLANK(x));...) or other checks seem to catch the empty field before doing the calculation.

Improve this question
2

1 Answer 1

Reset to default
2

Consider:

=IF(OR(C8="",B8=""),"",IF(C8>=B8,C8-B8,1+C8-B8))

(naturally using the ; rather than the ,)

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .