Help with Excel format

[daginge]

Hi ABers
Below is the format I use for calculating hours worked when working shifts that stretch across midnight. i.e. 20:00 to 06:00. However if I don't type in the date and time in each cell it calculates a minus figure. Any ideas I really don't want to put the date in each cell, I'm not lazy there's just not enough space for my particular application.

=SUM((C11-B10)*24,(B9-B8)*24)

[dr b]

You can enter the date in the cell, but then format the cell so that the date doesn't show.

Eg enter 9/4/09 20:00 in A1
9/5/09 06:00 in A2

but then format the cell so that it only shows the time (format...cells....time) (Note also I'm using US convention for MM/DD/YY).

then =a2-a1 will correctly compute as 10 (hours

[vascop]

A better way would be to add 24 if the result is negative and leave it alone otherwise
So if c6 contains the start time and b6 the end time, then put the following formula in the cells and then use sum on these.
=IF((C6-B6)

[vascop]

OOPS! Don't know what happened there but half my post is missing!!
better way would be to add 24 if the result is negative and leave it alone otherwise
So if c6 contains the start time and b6 the end time, then put the following formula in the cells and then use sum on these.
=IF((C6-B6)

[vascop]

OOPS! Happened again - must be caused by the "less than symbol". Trying again.
better way would be to add 24 if the result is negative and leave it alone otherwise
So if c6 contains the start time and b6 the end time, then put the following formula in the cells and then use sum on these.
=IF((C6-B6) "less than" 0,C6-B6+24) In your example this would give 6-20+24=10 (just what you wanted!
Replace "less than" in the above expression by the less than sign on your keyboard.(SHIFT+.)
Then add them all up.
This would only fail if someone works a shift longer than 24 hours. Unlikely I hope!!