how do i calculate hours worked in excel?

[Guest]

I want to track hours worked from clock in and out times...but I need to see
hours worked for each hour of the day rounded to the nearest 30 minutes using
the IF function MAYBE? I also need to use the same formula for each hour so
I can easily replicate it.
clock in time 08:20 clock out time 17:20

09:00 result = 0.5 attempted formula = if(clock in time>08:00,if(clock in
time<08:30,if(clock out time>09:00,1,0)))
10:00 result = 1.0
11:00 result = 1.0
12:00 result = 1.0 etc
18:00 result = 0.5

Clock in and out times will vary and the formula should include reference to
both the clock in and out time +/- 30 minutes.

Help, I am stuck and my formula sucks!
Skip4t4

 

[Jason Morin]

A1: clock in
B1: clock out

Total time:

=ROUND(B1/(1/24/2),0)*1/24/2-ROUND(A1/(1/24/2),0)*1/24/2

or

=ROUND(B1/"00:30",0)*"00:30"-ROUND(A1/"00:30",0)*"00:30"

Format the formula cell as time.

HTH
Jason
Atlanta, GA

 

[Guest]

Jason,
I have a table with hours in column A from 09:00 to 20:00 in 1 hour
increments.
If clock in is 09:00 and clock out is 11:00 I need to see ....
09:00 - 0:00
10:00 - 1:00
11:00 - 1:00
12:00 - 0:00
13:00 - 0:00
14:00 - 0:00

Sorry if I garbled a bit b4
Thanks
Skip4t4 aka Tracy
London

 

[Biff]

Hi!

Try this:

First, to make things easier use helper cells to round the
times.

A1 = Time In
B1 = Time Out

A2 = 8:20
B2 = 17:20

Round times to the nearest 30 min:

A3 =ROUND(A2*24/0.5,0)*0.5/24

Copy A3 to B3.

A9:A20 = 9:00, 10:00, 11:00, 12:00, 13:00, .... 20:00

In B9 enter this formula and copy down to B20:

=IF(A$3=A9,0,IF(OR(A$3+30/1440=A9,B$3+30/1440=A9),0.5,IF
(B$3>=A9,1,0)))

The above will return decimal values like you have posted
in your first post, 0, 0.5, 1

If you want h:mm format, use this formula and format the
cells as h:mm:

=IF(A$3=A9,0,IF(OR(A$3+30/1440=A9,B$3+30/1440=A9),0.5,IF
(B$3>=A9,1,0)))/24

Any "Time In" earlier than 8:00 AM will result in a return
of 1 for 9:00. Since your scale starts at 9:00 I assume
this is not an issue.

Biff

 

[Rob van Gelder]

I have this exact solution on my website. Hours affected by Dates.
It handles times spanning midnight.

 

[Guest]

Thanks, but... if I have a time in of say 12:00 then I see a return of 1 for
09:00, and 10:00 and each hour prior to 12:00 ? Is there any fix for that?

Skip4t4

 

[Roger Govier]

Hi Tracy

With start time in A1 and finish time in B1 and 08:00 - 20:00 in A2 through
A14
In cell B3
=SUMPRODUCT(--(A3>$A$1)*(A3<=$B$1)*1)+SUMPRODUCT(--($B$1>A2)*($B$1<=TIME(HOUR(A2),30,0))*0.5)
and copy down B4:B14

 

[Roger Govier]

Hi Tracy

Previous formula not tested thoroughly enough. It requires an additional
test.
Try
=SUMPRODUCT(--(A6>$A$1)*(A6<=$B$1)*1)+SUMPRODUCT(--($B$1>A5)*($B$1<=TIME(HOUR(A5),30,0))*0.5)+SUMPRODUCT(--($B$1>A5)*(AND($B$1<=TIME(HOUR(A5),59,0),$B$1>=TIME(HOUR(A5),30,0))*1))

 

[Biff]

Hi!

OK,

Thoroughly tested:

=IF(OR(A$3="",B$3=""),"",IF(A$3=A9,0,IF(OR
(A$3+30/1440=A9,B$3+30/1440=A9),0.5,IF(A$3>=A9,0,IF
(B$3>=A9,1,IF(B$3+30/1440<A9,0))))))

Biff

 

[Guest]

Used your website - complex but it works! Thanks

I have this exact solution on my website. Hours affected by Dates.
It handles times spanning midnight.