Rounding to 6 minutes for company time sheet

This is my company's increment system:

Record time in increments of 6 minutes:
0.1 = 6 minutes
0.2 = 12 minutes
0.3 = 18 minutes
0.4 = 24 minutes
0.5 = 30 minutes
0.6 = 36 minutes
0.7 = 42 minutes
0.8 = 48 minutes
0.9 = 54 minutes

The current time sheet I'm using is this:
12.02-12.15.xlsx

The timesheet automatically rounds to the nearest quarter hour (15, 30, 45, 60). I need each punch in and out to round to the nearest 6 minutes reflecting my employers increments, as well as the total time being from 8.0 to 8.9 hours.

Thanks so much!

Change the two 0.25s in the formula to 0.1

Originally Posted by Bob Phillips

Change the two 0.25s in the formula to 0.1

How do I round the Log in and Log Out times?

Originally Posted by ChemistB

If you want to round the Log in and out times within their own cells, you will need to use VBA. Alternately, you can have Excel round them internally while performing calculations on them.

Also, your bio says you are using 2003 but this is at least a 2007 worksheet. Will this need to be 2003 compatable? Will it be saved as an xls file or an xlsm (workbooks containing vba cannot be xlsx files)

It is 2007. Sorry. I have no idea what a VBA is. This is all like a foreign language to me

Originally Posted by ChemistB

Okay, as I mentioned, you could also do this during the calculation stage, rounding before subtracting, but keep the sign in/out times as is. However, this code will modify your times to the nearest 6 minutes. Then the calculations need to be modified,
i.e. in F9, simplify that to
=((E9-B9)-(D9-C9))*24

An explaination. Excel uses the day as the unit of measurement with time and dates. So an hour = 1/24 = 0.041667, a minute = 1/(60*24) = 0.000694 and 6 minutes = 6/(60*24) = 0.0041667. In the code, I use the Excel Function MROUND which rounds to the nearest value as specified by the second argument in the function (I used 0.00417). Excel formats that as time.

To enter your code into the workbook, Copy it,
Right click on the sheet tab and "View code" (this will open the VBA editor)
Paste the code into the large white text box on the right side of the VBA editor.
I've attached your example, already modified.

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next

If Target.Count = 1 Then
    If Intersect(Target, Range("B9:E23")) Is Nothing Then Exit Sub
        Target = WorksheetFunction.MRound(Target.Value, 0.00417)
End If

End Sub

Is that what you were looking for?

I just resaved the excel sheet you attached and saw the rounding for each punch in and out. For total time, it can only be 1 decimal so just reformat it, right?