Excel question

[pontifex]

Someone is using a spreadsheet to keep track of time worked for several employees. Everything works fine until you hit the 2nd/3rd shift guys.

column a holds start times
column b end time
column c uses a formula of =b#-a# to get the amount of hours worked, where # = row #
everything is using military 24 hour time format

Bob starts at 10:00 pm - 22:00
Bob leaves at 1:00 am - 01:00
he works 3 hours but the formula returns #############

how do we get it to work properly? tried different formulas and different formatting already.

 

[bmacd]

I set something similar up myself to track my screwy hours years ago when I was working retail (1st shift, 2nd shift, 3rd shift). The best way I found to correctly calculate the hours was to treat an evening shift (like 10pm/2200HRS) as a morning shift (10am/1000 HRS), and an early morning shift (1am/0100 HRS) as an afternoon shift (1pm/1300). Let me know if this makes any sense. It worked for me, however, I had to annotate off to the side for auditing purposes to show it was a 3rd shift.

Bill

 

[KLin]

I got it to work, but I had to add in the date into each field, subtract them and multiply the result by 24 since it returns days (3 hours=.13 days).