Timesheet Rounding

Our timeclock rounds times to the nearest 6th minute; however, if they clock in/out within 6 minutes of the half hour, it rounds to that half hour (i.e., 8:55 rounds to 9:00; 9:05 rounds back to 9:00). What formula can I use to get this?

Clock In
Actual - 2/24/2014 8:55:38 AM
Rounded time desired - 2/24/2014 9:00:00 AM

Clock Out
Actual - Tue, 2/24/14 5:36:40 PM
Rounded time desired - Tue, 2/24/14 5:30:00 PM

I've tried multiple formulas but I can't seem to configure them to get exactly what I need.

Thanks for your help with this!

Originally Posted by JRose0303

Our timeclock rounds times to the nearest 6th minute; however, if they clock in/out within 6 minutes of the half hour, it rounds to that half hour (i.e., 8:55 rounds to 9:00; 9:05 rounds back to 9:00). What formula can I use to get this?

Clock In
Actual - 2/24/2014 8:55:38 AM
Rounded time desired - 2/24/2014 9:00:00 AM

Clock Out
Actual - Tue, 2/24/14 5:36:40 PM
Rounded time desired - Tue, 2/24/14 5:30:00 PM

I've tried multiple formulas but I can't seem to configure them to get exactly what I need.

Thanks for your help with this!

I think you'd either have to supply lots more examples of desired results, or expand the rules that it needs to work to
The example you provided, clocking out at 5:36:40 PM is not within 6 minutes of 5:30:30 PM, so how should this be handled ?

It's crazy how it rounds. If the clock in or clock out time occurrs 6 minutes prior and 6 minutes after the half hour, it rounds to the nearest half hour:
8:24-8:29 will round to 8:30
8:31-8:36 will round to 8:30
8:54-8:59 will round to 9:00
9:01-9:06 will round to 9:00

Otherwise, the times round to the nearest 6 minute increment:
8:15 will round to 8:12
8:16 will round to 8:18