Donate SIGN UP

Help with Excel format

Avatar Image
daginge | 14:36 Fri 04th Sep 2009 | Computers
4 Answers
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)
Gravatar

Answers

1 to 4 of 4rss feed

Avatar Image
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
14:41 Fri 04th Sep 2009
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
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)
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)
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!!

1 to 4 of 4rss feed

Do you know the answer?

Help with Excel format

Answer Question >>

Related Questions

Avatar Image
tpreece01
Avatar Image
Avatar Image
Avatar Image
Avatar Image
tpreece01