|
how do i calculate hours worked in excel?
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 |
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 . |
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 . |
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 -----Original 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 . . |
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 . |
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 "Biff" wrote: 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 -----Original 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 . . |
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$1A2)*($B$1<=TIME(HOUR(A2),30,0))*0.5) and copy down B4:B14 -- Regards Roger Govier "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 . |
Hi Tracy
Previous formula not tested thoroughly enough. It requires an additional test. Try =SUMPRODUCT(--(A6$A$1)*(A6<=$B$1)*1)+SUMPRODUCT(--($B$1A5)*($B$1<=TIME(HOUR(A5),30,0))*0.5)+SUMPROD UCT(--($B$1A5)*(AND($B$1<=TIME(HOUR(A5),59,0),$B$1=TIM E(HOUR(A5),30,0))*1)) -- Regards Roger Govier "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 . |
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 -----Original Message----- 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 "Biff" wrote: 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 -----Original 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 . . . |
Used your website - complex but it works! Thanks
"Rob van Gelder" wrote: 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 . |
| All times are GMT +1. The time now is 10:45 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com