# Round up/down to nearest 5 minutes **URL:** \ **Category:** Ask the Community\ **Created:** [July 14, 2020, 9:46pm UTC](https://connect.superhuman.com/t/round-up-down-to-nearest-5-minutes/17210 "2020-07-14T21:46:47Z")\ **Posts on this page:** 12\ **Page:** 1
**Author:** ![Mike\_Hewitt](https://yyz1.discourse-cdn.com/coda1/user_avatar/connect.superhuman.com/mike_hewitt/32/22936_2.png) [@Mike\_Hewitt](https://connect.superhuman.com/u/Mike_Hewitt)\ **Post date:** [July 14, 2020, 9:46pm UTC](https://connect.superhuman.com/t/round-up-down-to-nearest-5-minutes/17210/1 "2020-07-14T21:46:47Z")
I have a date and time column that I wish to round either up or down to the nearest 5 minutes. ---
**Author:** ![Ben\_Lee](https://yyz1.discourse-cdn.com/coda1/user_avatar/connect.superhuman.com/ben_lee/32/49867_2.png) [@Ben\_Lee](https://connect.superhuman.com/u/Ben_Lee)\ **Post date:** [July 15, 2020, 12:54am UTC](https://connect.superhuman.com/t/round-up-down-to-nearest-5-minutes/17210/2 "2020-07-15T00:54:50Z")
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. https://coda.io/embed/OpFM0dnOXm/_su_yK?viewMode=embedplay&hideSections=true ---
**Author:** ![Kelly\_Jordan](https://yyz1.discourse-cdn.com/coda1/user_avatar/connect.superhuman.com/kelly_jordan/32/14419_2.png) [@Kelly\_Jordan](https://connect.superhuman.com/u/Kelly_Jordan)\ **Post date:** [July 15, 2020, 6:24pm UTC](https://connect.superhuman.com/t/round-up-down-to-nearest-5-minutes/17210/3 "2020-07-15T18:24:31Z")
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) ---
**Author:** ![Paul\_Danyliuk](https://yyz1.discourse-cdn.com/coda1/user_avatar/connect.superhuman.com/paul_danyliuk/32/27228_2.png) [@Paul\_Danyliuk](https://connect.superhuman.com/u/Paul_Danyliuk)\ **Post date:** [July 15, 2020, 7:29pm UTC](https://connect.superhuman.com/t/round-up-down-to-nearest-5-minutes/17210/4 "2020-07-15T19:29:49Z")
The easiest and clearest way would be to apply this formula: ```auto 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: ```auto thisRow.[Date and Time].DateTimeTruncate("hour") + Minutes(Round( thisRow.[Date and Time].Minute() / 5 + thisRow.[Date and Time].Second() / 300 ) * 5) ``` ---
**Author:** ![Saul\_Garcia](https://yyz1.discourse-cdn.com/coda1/user_avatar/connect.superhuman.com/saul_garcia/32/16406_2.png) [@Saul\_Garcia](https://connect.superhuman.com/u/Saul_Garcia)\ **Post date:** [July 16, 2020, 4:54pm UTC](https://connect.superhuman.com/t/round-up-down-to-nearest-5-minutes/17210/5 "2020-07-16T16:54:12Z")
Hello everyone! I found one formula that does pretty much what you want! > **[Coda | Formula library](https://coda.io/formulas#RoundTo)** > > A.k.a. the official book of Coda spells. Our formula library is a comprehensive list of everything cool that comes after "=" in Coda. 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() https://coda.io/embed/mAlMGEg-XD/_suvWe?viewMode=embedplay&hideSections=true ---
**Author:** ![Paul\_Danyliuk](https://yyz1.discourse-cdn.com/coda1/user_avatar/connect.superhuman.com/paul_danyliuk/32/27228_2.png) [@Paul\_Danyliuk](https://connect.superhuman.com/u/Paul_Danyliuk)\ **Post date:** [July 16, 2020, 4:58pm UTC](https://connect.superhuman.com/t/round-up-down-to-nearest-5-minutes/17210/6 "2020-07-16T16:58:44Z")
Oh! That’s even better! Just instead of the non-obvious 0.0347222 value you can use `Minutes(5)`: ```auto 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 ---
**Author:** ![Saul\_Garcia](https://yyz1.discourse-cdn.com/coda1/user_avatar/connect.superhuman.com/saul_garcia/32/16406_2.png) [@Saul\_Garcia](https://connect.superhuman.com/u/Saul_Garcia)\ **Post date:** [July 16, 2020, 5:01pm UTC](https://connect.superhuman.com/t/round-up-down-to-nearest-5-minutes/17210/7 "2020-07-16T17:01:58Z")
😁 Once in a while I look at all the formulas to see what I can use. > [@Paul\_Danyliuk](#): > > `RoundTo([Any time], Minutes(5)).ToTime()` Ufff that looks more intuitive! ---
**Author:** ![Jean\_Pierre\_Traets](https://yyz1.discourse-cdn.com/coda1/user_avatar/connect.superhuman.com/jean_pierre_traets/32/16275_2.png) [@Jean\_Pierre\_Traets](https://connect.superhuman.com/u/Jean_Pierre_Traets)\ **Post date:** [July 16, 2020, 5:03pm UTC](https://connect.superhuman.com/t/round-up-down-to-nearest-5-minutes/17210/8 "2020-07-16T17:03:41Z")
@Saul_Garcia and @Paul_Danyliuk Amazing, thanks a lot 🏆 ---
**Author:** ![Mike\_Hewitt](https://yyz1.discourse-cdn.com/coda1/user_avatar/connect.superhuman.com/mike_hewitt/32/22936_2.png) [@Mike\_Hewitt](https://connect.superhuman.com/u/Mike_Hewitt)\ **Post date:** [July 16, 2020, 7:06pm UTC](https://connect.superhuman.com/t/round-up-down-to-nearest-5-minutes/17210/9 "2020-07-16T19:06:12Z")
@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. ---
**Author:** ![Paul\_Danyliuk](https://yyz1.discourse-cdn.com/coda1/user_avatar/connect.superhuman.com/paul_danyliuk/32/27228_2.png) [@Paul\_Danyliuk](https://connect.superhuman.com/u/Paul_Danyliuk)\ **Post date:** [July 16, 2020, 7:33pm UTC](https://connect.superhuman.com/t/round-up-down-to-nearest-5-minutes/17210/10 "2020-07-16T19:33:06Z")
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: ![image](https://global.discourse-cdn.com/coda1/original/3X/4/f/4fedad0995f04b97bd97402ca3cd7735109af2b5.png) Also while we’re at it, I discovered that `ToNumber()` doesn’t preserve precision: ![image](https://global.discourse-cdn.com/coda1/original/3X/0/b/0bfe223d2603dd866a00ce647286530e6471aeeb.png) And not only that, but in this scenario it doesn’t really return an actual number either 😕 ![image](https://global.discourse-cdn.com/coda1/original/3X/7/b/7b5456870b4f51084a54433167b91cee05fab7af.png) 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. ---
**Author:** ![Mike\_Hewitt](https://yyz1.discourse-cdn.com/coda1/user_avatar/connect.superhuman.com/mike_hewitt/32/22936_2.png) [@Mike\_Hewitt](https://connect.superhuman.com/u/Mike_Hewitt)\ **Post date:** [July 16, 2020, 10:39pm UTC](https://connect.superhuman.com/t/round-up-down-to-nearest-5-minutes/17210/11 "2020-07-16T22:39:45Z")
I will see what I can do. ---
**Author:** ![Mike\_Hewitt](https://yyz1.discourse-cdn.com/coda1/user_avatar/connect.superhuman.com/mike_hewitt/32/22936_2.png) [@Mike\_Hewitt](https://connect.superhuman.com/u/Mike_Hewitt)\ **Post date:** [October 12, 2020, 6:00pm UTC](https://connect.superhuman.com/t/round-up-down-to-nearest-5-minutes/17210/12 "2020-10-12T18:00:31Z")
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.