Calculate number of hours worked

Important Notice

Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

• 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!