I was recently asked how to round a time to the nearest 15 minutes. The question was in relation to the “7 minute rule” where employee clock in and clock out times are rounded to the nearest quarter hour. It’s called the 7 minute rule because the cutoff is 7 minutes after the clock-in time. Suppose an employee is scheduled to clock in at 7:45am. If they clock in at 7:52am, it is rounded to 7:45am and they are on time. But if they clock in at 7:53am, it is rounded to 8:00am and they are late.
To do this in Excel use the MROUND function. To try this:
- In A1, type 07:52
- In A2, type =MROUND(A1,TIME(0,15,0))
- Format A2 as time
- Change A1 to 07:53
This combines the MROUND function with the TIME function. The TIME function requires 3 arguments – hour, minutes, seconds