# Round up/down to nearest 5 minutes

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:

1. `thisRow.[Date and Time].DateTimeTruncate("hour")` removes minutes
2. `thisRow.[Date and Time].Minute()` takes just the minutes number from date/time
3. `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.
4. 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)
``````
1 Like

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

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.

5 Likes