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

--
Rob van Gelder - http://www.vangelder.co.nz/excel


"Skip4t4" wrote in message
...
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

"Jason Morin" wrote:

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

-----Original Message-----
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
time08:00,if(clock in
time<08:30,if(clock out time09: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




.