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)
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)
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()
Oh! Thatâ€™s even better! Just instead of the nonobvious 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
Once in a while I look at all the formulas to see what I can use.
Ufff that looks more intuitive!
@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.
I will see what I can do.