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

 

[momtoaaj]

You guys rock!!!

I knew I could find the answer, I was just looking the wrong places!!

 

[Jirayu]

Hi team,
I tried to follow this thread but I still have an issue when the minute are :03, :09, :15, :21, :27, : 33, :39, :45, : 51, : 57.

The Actual Rounded Minute Rule ex. In 8:03 Rounded to 8:00 (3 mins grace period)

:00 to :03 Rounded to :00
:04 to :09 Rounded to :06
:10 to :15 Rounded to :12
:16 to :21 Rounded to :18
:22 to :27 Rounded to :24
:28 to :33 Rounded to :30
:34 to :39 Rounded to :36
:40 to :45 Rounded to :42
:46 to :51 Rounded to :48
:52 to :57 Rounded to :54
:58 to :60 Rounded to :60

My excel looks like:
A…………B…………………………C………………D………………………………E……….
A1: IN………. Rounded IN………. OUT……….Rounded OUT………. Total Daily
B2: 8:00…….8:00…………………..16:03………16:06 (Wrong should be 16:00)………..8.1 (wrong it should be 8 hrs.)

Rounded IN/OUT formula(B &D clmn.) : =ROUND(A2*240,0)/240

Total Daily formula (E clmn): =ROUND((D2-B2)*24,1)

Thank you so much.