Excel spreadsheet for hours worked tracking

Question

I want to track my hours for work on my personal Excel spreadsheet.

My company records time in 6 minute intervals. So 8 hrs and 6 min worked is represented as 8.1 in the time card systems and so forth.

I have in cell A1 the header and A2 is the data.

+------------+-------------+-----------+---------+-------------+----------+
| Start Time | Start Lunch | End Lunch | End day | Total Hours | TC Hours |
+------------+-------------+-----------+---------+-------------+----------+
| 07:00      | 11:00       | 12:00     | 16:03   | 8:03        | 8        |
| 07:00      | 11:00       | 12:00     | 16:06   | 8:06        | 8.1      |
+------------+-------------+-----------+---------+-------------+----------+

I would like to achieve two results. The first is that anything that is from 0-3 minutes be rounded down and anything from 4-6 minutes be rounded up. The next one is to output in the Time Card (TC) format of 6 minutes =.1 and so forth. I have a previous code I used for both but need to adjust it.

This is for the rounding

=IF(ISBLANK($E10)," ", TIME(HOUR(F10), ROUND((MINUTE(F10)/60)*4, 0) * 15, 0))

This is for the TC

=IF(ISBLANK(E10)," ", ROUND((G10*24)/0.25,0)*0.25)

Answer 1

Get away from using math tricks to achieve your averaging and use MROUND and TIME instead. It is every bit as accurate (if not more so) in avoiding floating point problems with time and generally makes more sense to the user.

To average any time value to the nearest 6 minutes use,

=MROUND(E2, TIME(0, 6, 0))

To convert the total hours to hours as integers with minutes as floating points (in F2 as per the included image),

=HOUR(MROUND(E2, TIME(0, 6, 0)))+MINUTE(MROUND(E2, TIME(0, 6, 0)))/60