Round date time

Ask for Help
1

Hi Gliders,

I built a punch system for one of my clients and he’d like to have the ins and outs date times to be rounded to 15 minutes.

Examples:

Employee clocks in at 7:04 AM
App records 7:04 but shows 7:00

Employee clocks in at 8:09 AM
App records 8:09 but shows 8:15

Employee clocks out at 10:57 AM
App records 10:57AM but shows 11h AM

It would produce a cleaner payroll and prevent employees from complaining to management to round their punch in/out times.

I can’t find an answer anywhere.

I tried to isolate the minutes and rounding that number but that means extra columns that could be done with maybe only 1-2 if there’s a formula or function for that. It also means rounding the hours like 23 to 00 and years and days… And then formatting the final result to a date. I started working on it but it’s a headache working with so many columns to achieve the result I want and I wonder if there is a better way I’m not aware of?

Thanks in advance for your replies!

2

Try this in a math column.

Date-(MINUTE(Date)/1440)+
(ROUND(MINUTE(Date)/60*4)*15/1440)
1 Like
3

If seconds are vital for accuracy, here is another version that is accurate down to the second.

Date-(MINUTE(Date)/1440)-(SECOND(Date)/86400)+
(ROUND((MINUTE(Date)+SECOND(Date)/60)/60*4)*15/1440)
4

Thank you that worked!

1 Like
5

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.