Time Clock Six Minute Rounding Formula needed

[momtoaaj]

Hi there,

I am trying to figure out the written formula for needing this answer:

For a time clock with 6 minute rounding.

If I type in 8:20 as the arrival time, I need it to round to 8:18, so that the final time will be in tenths.

Here is the rounding:

Actual Rounded Minutes in Time Clock
:57, :58, :59, :00, :01, :02
:03, :04, :05, :06, :07, :08
:09, :10, :11, :12, :13, :14
:15, :16, :17, :18, :19, :20
:21, :22, :23, :24, :25, :26
:27, :28, :29, :30, :31, :32
:33, :34, :35, :36, :37, :38
:39, :40, :41, :42, :43, :44
:45, :46, :47, :48, :49, :50
:51, :52, :53, :54, :55, :56

Anything in the same line will round to the bolded time.

Example: I arrive to work at 8:09 and go to lunch at 11:59, it will round to 8:12 and 12:00, making my time worked 3:48 or 3.80. Then I get back from lunch at 12:59 and leave at 17:14, which will round to 13:00 and 17:12, making my time worked 4:12 or 4.2 for the 2nd 1/2 of the day and 8:00 or 8.0 hours for the day.

Time In----------Time Out----------Total Time----------Decimal Time----------Total Hours
...8:12.................12:00..................3:48......................3.80
..13:00................17:12..................4:12......................4.20..........................8.00

The problem is I want to write the actual time and not have to round everything myself and yet I need the decimal time to match what my time clock actually calculates from.

My current formula for the decimal shown above as 3.80 is this: =(HOUR(F7)*60+MINUTE(F7))/60
My current formula for the total time for the day (regular hours) is this: =IF((((E7-D7)+(I7-H7))*24)>8,8,((E7-D7)+(I7-H7))*24)
My current formula for overtime is this: =IF(((E7-D7)+(I7-H7))*24>8,((E7-D7)+(I7-H7))*24-8,0)

NOTE: This is not a time clock, it is my personal copy of what my time for the week was/is, so that I can compare it to the actual time card that I have to sign off on.

Please help!

BTW- I tried to search the forums and for some reason, I can't pull up anything without going through the main page and looking at each topic It's my work computer, so I can't fix it I'm sorry if this was posted previously.

Please submit your formulas in this post so that I can get them

Thanks!!

 

[RalphA]

Assuming you have your minutes in the ranges A1:F1, A2:F2, A3:F3, etc. enter the following formula in G1:

=IF(A1/6=INT(A1/6),A1,"")&IF(A1/6=INT(A1/6)+1,A1+1,"")&IF(A1/6=INT(A1/6)+2,A1+2,"")&IF(A1/6=INT(A1/6)+3,A1+3,"")&IF(A1/6=INT(A1/6)+4,A1+4,"")&IF(A1/6=INT(A1/6)-1,A1-1,"")&IF(A1/6=INT(A1/6)-2,A1-2,"")

and copy to the range G1:L3. You should get a blank in all that range, except in the range I1:I3, where you should see 0, 6, 12. Hope this helps you figure out your problem

 

[barry houdini]

My current formula for the decimal shown above as 3.80 is this: =(HOUR(F7)*60+MINUTE(F7))/60
My current formula for the total time for the day (regular hours) is this: =IF((((E7-D7)+(I7-H7))*24)>8,8,((E7-D7)+(I7-H7))*24)
My current formula for overtime is this: =IF(((E7-D7)+(I7-H7))*24>8,((E7-D7)+(I7-H7))*24-8,0)

Hello Heidi, welcome to the board

You can simplify these formulas a little. To convert a time in F7 to the decimal equivalent

=F7*24

for total time

=MIN(8,(E7-D7+I7-H7)*24)

for overtime

=MAX(0,(E7-D7+I7-H7)*24-8)

But for your original question, to round times to the nearest 6 minutes, because 6 mins is 1/240 of a day you could use the formula

=ROUND(D7*240,0)/240

You could incorporate this rounding into the other formulas for calculating time worked, overtime etc. but that could give you different results to those achieved by rounding the start finish and lunch times and then calculating. Is it the latter you want to do?

 

[momtoaaj]

Barry,

Will the 1/240 only round the total hours? I need the days separated into first half and last half of the day. I'm not sure how to upload or send my spreadsheet, except via email. Ideas?

ETA-It's not really rounding to the nearest 6 minutes. I need it to round to the nearest 6 minute increments following the rules of the chart that I posted.

 

[momtoaaj]

Great!

Assuming you have your minutes in the ranges A1:F1, A2:F2, A3:F3, etc. enter the following formula in G1:

=IF(A1/6=INT(A1/6),A1,"")&IF(A1/6=INT(A1/6)+1,A1+1,"")&IF(A1/6=INT(A1/6)+2,A1+2,"")&IF(A1/6=INT(A1/6)+3,A1+3,"")&IF(A1/6=INT(A1/6)+4,A1+4,"")&IF(A1/6=INT(A1/6)-1,A1-1,"")&IF(A1/6=INT(A1/6)-2,A1-2,"")

and copy to the range G1:L3. You should get a blank in all that range, except in the range I1:I3, where you should see 0, 6, 12. Hope this helps you figure out your problem

Thanks! I will play with this to fit it into my spreadsheet. I haven't gotten it to work yet though.

 

[momtoaaj]

Here is a PrtScn of my spreadsheet. I ended up having to put enter the times as if they WERE rounded, but those weren't the ACTUAL times that I clocked in/out.

ETA- I'm not sure, but I'm guessing that I'll have to add columns to the right of D7, E7, H7, I7, R7, S7 so that it will round before it calculates?

 

[mikerickson]

If A1 holds the time (as serial time)
=ROUND(A1*24*60/6,0)*6 will return that rounded to the nearest 6 minutes.

 

[barry houdini]

Disregarding lunch if you started a shift at 08:58 and ended at 17:32 that a total time worked of 8:34. If you round that to the nearest 10th of an hour you get 8:36 or, in decimals, 8.6

However if you round both the start and end times first you get 09:00 to 17:30 which gives you a shift length of 8.5.

Which way do you want to calculate that? It's easier to do it the first way because you can calculate the result as before and just round to 1 decimal place, e.g. for your regular hours formula

=MIN(8,ROUND((E7-D7+I7-H7)*24,1))

If you want to round the times first then you could use the formula I posted above to round each of your start finish and lunch times to the nearest 6 minutes and then calculate or all within one formula

=MIN(8,(ROUND(E7*240,0)/240-ROUND(D7*240,0)/240+ROUND(I7*240,0)/240-ROUND(H7*240,0)/240)*24)

You say the rounding didn't work, do you have an example, it works fine for me? Are your times all in whole minutes?

 

[momtoaaj]

Did you see the spreadsheet that I posted. Do you need the formulas in the box? (since you can't see those?)

I need to have each individual time stamp rounded. If it were up to me, I'd do it by the day, but if I work actual hours, the time clock and therefore, company, is cheating me out of many minutes each day. So, since I figured out how it was calculating, I tracked it for two weeks and now I know that it's the 6 minute rounding, but not necessarily to the "nearest" 6 minutes. If you clock in 3 minutes before 8:00am, it clocks you in at 8:00. If you clock in 3 minutes AFTER 8:00am, it clocks you in at 8:06am.

 

[momtoaaj]

But for your original question, to round times to the nearest 6 minutes, because 6 mins is 1/240 of a day you could use the formula

=ROUND(D7*240,0)/240

You could incorporate this rounding into the other formulas for calculating time worked, overtime etc. but that could give you different results to those achieved by rounding the start finish and lunch times and then calculating. Is it the latter you want to do?

Hey, that is working now. I was putting it in the wrong cell