cancel
Showing results for 
Search instead for 
Did you mean: 

microsoft excel . . can u help?

N/A

microsoft excel . . can u help?

Hello, I'm trying to get a formula to calculate the difference between two times i.e 7:00 to 16:00 these are easy with them being in the daytime ( =TEXT(B2-A2,"h:mm") ) this formula works great but I want to find the difference past the midnight, i.e 20:00 to 06:00
Can anyone help??
The reason for wanting this is for a time sheet, I sometimes work nights.

Cheers
Dave
3 REPLIES
N/A

microsoft excel . . can u help?

=TEXT(IF(B2<A2,24+B2,B2)-A2,"h:mm")

Check out www.wopr.com for great assistance with all products Microsoft
N/A

microsoft excel . . can u help?

Thanks very much, it works a treat.
Cheers
Mark_Dowd
Grafter
Posts: 102
Registered: 08-08-2007

microsoft excel . . can u help?

Another take on this is that times are actually dates and times with the display of the "date" suppressed.

If you format the source cells as "dd-mmm hh:mm", input the date and time (e.g. 22-Jun 21:20), than format the target cell (containing =B2-A2) as ":mm" it will display the difference in hours, minutes and seconds, even if it is more than 24 hours.

This approach will allow you to perform arithmetic on the "results" cells because they are no longer TEXT, where a value of "1" is a day.