Calculating Hours Worked from Shift Begin and Shift End

**lukeflegg** · 08-12-2011, 10:12 AM

Argh!

I cannot figure this out!

I have

SHIFT STARTS SHIFT ENDS SHIFT LENGTH
0930 1715 ?

Any ideas how to calculate the lengths of the shifts? I REALLY don't want to have to manually put in all of the start and end times again in another format if i can avoid it!

Thanks so much

**ChemistB** · 08-12-2011, 10:22 AM

I'm going to assume your numbers are entered as text since you have the 0 in front of the 9 and I'm going to assume that all your time values are 4 digits.

If Start is in A1 and End is in B2, Try
=(LEFT(B1,2)&":"&RIGHT(B1,2))-(LEFT(A1,2)&":"&RIGHT(A1,2))
Format the column as Custom [hh]:mm
Does that work for you?

**lukeflegg** · 08-12-2011, 10:32 AM

Originally Posted by ChemistB

I'm going to assume your numbers are entered as text since you have the 0 in front of the 9 and I'm going to assume that all your time values are 4 digits.

If Start is in A1 and End is in B2, Try
=(LEFT(B1,2)&":"&RIGHT(B1,2))-(LEFT(A1,2)&":"&RIGHT(A1,2))
Format the column as Custom [hh]:mm
Does that work for you?

Afraid not...
am I doing something really stupid? all I'm getting is this:

http://i204.photobucket.com/albums/b...el12th1533.jpg

**ChemistB** · 08-12-2011, 11:28 AM

Okay, your numbers are numbers (note: there's no zero in front of the 9 in your picture, unlike your post). Try this

=MOD(TEXT(B1,"00\:00")-TEXT(A1,"00\:00"),1)
Does that work for you?