Excel: Hours Greater Than 24 1

[Shanachie]

I have an Excel worksheet that tracks hours worked on a job. The jobs often require overnight shifts (this is a tugboat company) and sometimes even shifts longer than 24 hours. (You can sleep some as long as a proper watch is kept.)

When I try to calculate the number of hours by subtracting starting time from ending time, the overnight shifts show as negative time. And a 25 hour shift shows as 1 hour. This happens even if the starting and ending times are in date/time format.

Suggestions?

Shanachie

 

[xlbo]

For overnight shifts, you need to take the Date & Time away from the Date & Time eg

29/01/2007 01:30:00 - 28/01/2007 13:00:00

If you are getting negative results, you are taking the time away from the time only and not including the dates

For shifts >24hrs, use a format of [hh]:mm:ss to display your results

Also worth a look in the FAQs as there are 2 on dates & times in excel

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Shanachie]

Thanks, that did it.