Tested for zero and for empty in either or both cells
as well as start 23:05 finish 12:05 (13 hours total)
format the result as time [h]:mm
which is a format you can use for totals over 23hrs 59 minutes.
The formula is only good up to 23hrs 59 minutes.
=B2-A2+(A2B2)
adds 1 day (24 hours) if start time is greater than end time.
More on my webpage
http://www.mvps.org/dmcritchie/excel/datetime.htm
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages:
http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page:
http://www.mvps.org/dmcritchie/excel/search.htm
"sonar " wrote in message ...
Hi, I am using this formula, but everytime I have no hours in my start
and finish cells, or if its 00:00, it gives me a 7.00, how can I make
this not do that. But instead make it a 0.00 rather, as I dont want my
total of all the nightshift hours to show an amount if its not suppost
to exist.
=(IF(G14<TIME(7,0,0),TIME(7,0,0)-G14,0))+(IF(H14TIME(19,0,0),H14-TIME(19,0,0),0))
Thanks
---
Message posted from http://www.ExcelForum.com/