I have a date and time column that I wish to round either up or down to the nearest 5 minutes.
I want to find a better way to do this, but here’s what I have so far.
The idea is to trim the “AM/PM”, take the right most digit and round, then piece back together into a new Date/Time.
Hi Ben,
One flaw I noticed is 5:08 PM should round to 5:10 PM instead of 5:00 PM. (which also leads to the issue with what it should do with 5:56 PM)
1 Like
The easiest and clearest way would be to apply this formula:
thisRow.[Date and Time].DateTimeTruncate("hour")
+ Minutes(Round(thisRow.[Date and Time].Minute() / 5) * 5)
Explanation:
-
thisRow.[Date and Time].DateTimeTruncate("hour")removes minutes -
thisRow.[Date and Time].Minute()takes just the minutes number from date/time -
Round()rounds to the nearest integer digit by comparing the decimal part with 0.5 — so in order to get rounding to the nearest 5 we need to divide, round, then multiply by 5. - Finally we add rounded minutes to the date/time. No conversion to/from text, no character manipulation, a clean and logical solution with time math only.
P.S. Just noticed that this formula doesn’t take seconds into account, i.e. if it.s 1:02:59, it will still get rounded down to 1:00, not 1:05. The updated formula would be:
thisRow.[Date and Time].DateTimeTruncate("hour")
+ Minutes(Round(
thisRow.[Date and Time].Minute() / 5
+ thisRow.[Date and Time].Second() / 300
) * 5)
2 Likes
Hello everyone! I found one formula that does pretty much what you want!
The only thing to do is get the value of 5min of one day and the use it in the next formula:
RoundTo([Any time],0.00347222).ToTime()
4 Likes
Oh! That’s even better! Just instead of the non-obvious 0.0347222 value you can use Minutes(5):
RoundTo([Any time], Minutes(5)).ToTime()
I had a feeling I saw a function for arbitrary rounding but wasn’t sure. Great catch @Saul_Garcia
2 Likes
Once in a while I look at all the formulas to see what I can use.
Ufff that looks more intuitive!
2 Likes
@Saul_Garcia and @Paul_Danyliuk
Amazing, thanks a lot 
2 Likes
@Saul_Garcia & @Paul_Danyliuk, fantastic cooperation! Your solution is very clean and works for dates.
For some reason, it isn’t working for a DateTime:
RoundTo(Now(), Minutes(5)).ToDateTime()
It ends up being off by 35 seconds in my test. Experimenting with converting Now() to a number, going through ToDate & ToTime, it all looks like it should work. I wonder if it is just a rounding error. Here is the work around I came up with for DateTime:
Now().ToDate().ToDateTime() + RoundTo(Now().ToTime(), Minutes(5)).ToTime()
It would be interesting to find out why the DateTime doesn’t work.
Could be a rounding error. I remember there were some. Totally could be I forgot about the RoundTo for a good reason — maybe it was degrading the floating point precision. Need to test.
Apparently Minutes(5) is not preserving precision as it should, resulting in some drift over time:
Also while we’re at it, I discovered that ToNumber() doesn’t preserve precision:
And not only that, but in this scenario it doesn’t really return an actual number either 
Thank God for Javascript!
@Saul_Garcia @Jean_Pierre_Traets @Kelly_Jordan don’t use Datetime.RoundTo(Minutes(5)), use Datetime.RoundTo(5 / 24 / 60) instead 
@Mike_Hewitt1 would you please log that as a bug? I think Minutes(), Hours() etc precision should be fixed. We relied on them a lot without knowing that those values could be off a few seconds.
1 Like
I will see what I can do.
Duration precision improvements & the ToNumber() fix have been rolled out for new documents.
Sometimes an update requires extra work to bring docs up to the new version. We run thousands of tests to ensure as few issues as possible and then we work on a roll out plan for existing docs. So all new docs will have the ToNumber() fix and some older docs have it, and we’re working on an upgrade path for the rest.
6 Likes