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.
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)
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)
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)
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:
Oh! That’s even better! Just instead of the non-obvious 0.0347222 value you can use
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
Once in a while I look at all the formulas to see what I can use.
Ufff that looks more intuitive!
For some reason, it isn’t working for a DateTime:
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.
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
@Mike_Hewitt1 would you please log that as a bug? I think
Hours() etc precision should be fixed. We relied on them a lot without knowing that those values could be off a few seconds.
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.