Calculate number of hours worked

• I tried looking at old threads, but I can't seem to find anything that works for my case.

I have a time sheet similar to the one below.

[TABLE="width: 500"]

[tr]

[td]

day

[/td]

[td]

time in

[/td]

[td]

time out

[/td]

[td]

time in

[/td]

[td]

time out

[/td]

[td]

hours worked

[/td]

[/tr]

[tr]

[td]

Monday

[/td]

[td]

8:30

[/td]

[td]

4:30

[/td]

[td][/td]

[td][/td]

[td][/td]

[/tr]

[tr]

[td]

Tuesday

[/td]

[td]

8

[/td]

[td]

12:30

[/td]

[td]

1:30

[/td]

[td]

5

[/td]

[td][/td]

[/tr]

[/TABLE]

I'm trying to write a code to calculate the hours worked (in both cases 8 for the example above). I've tried different number formats, but I can't find one that works.

Any ideas or comments would be greatly appreciated

• Re: Calculate number of hours worked

Are the only two formats or you can have more?
Is this 8:30 4:30 morning or afternoon?
How do you know this?

• Re: Calculate number of hours worked

It should be 8:30am to 4:30 pm.
It can be just one format.

• Re: Calculate number of hours worked

Try:

F2 drag down
=IF(C2<>"", C2-B2+E2-D2,C2-B2)
format as h

• Re: Calculate number of hours worked

One problem I'm seeing from all the answers I've gotten so far is that if I work from 8:00AM to 4:30PM, then it says that the number of hours worked is 8:30. Is there a way to make it say 8.5 instead?

• Re: Calculate number of hours worked

=IF(C2<>"",(C2-B2+E2-D2)*24,(C2-B2)*24)
Format as General

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!