 # Round time up/down using DATETIME()

I am trying to round up or down time to whole 30 min but can not figure out what I am doing wrong in my formula.

DATETIME(NOW())(
DATETIME(TIMENOW())()(HOUR(TIMENOW()-“0:00”)+“1:00”)-
IFS(
MINUTE(NOW()-“0:00”)>45,“0:00”,
MINUTE(NOW()-“0:00”)>30,“0:30”,
MINUTE(NOW()-“0:00”)>15,“0:30”,
MINUTE(NOW()-“0:00”)>0,“0:30”
)
)
OR(
MINUTE([Time In]-“0:00”)=0,
MINUTE([Time In]-“0:00”)=30,
MINUTE([Time In]-“0:00”)=30,
MINUTE([Time In]-“0:00”)=30
)

Any help would be appreciated.

Hi Daniel

Something like FLOOR(MINUTE([Time In]),30) might be what you’re looking for…? I think this should read the Minutes value from [Time In] as an integer (assuming it’s a Time data type), then round that number to a multiple of 30 (so in the possible range of 0 - 59, that should be limited to 0 or 30).

Hope this helps!

Try:

``````(
[datetime]
-
(([datetime] - "00:00:00") - HOUR([datetime] - "00:00:00"))
+
INDEX(
LIST(
"000:00:00",
"000:30:00",
"001:00:00"
),
(1 + ROUND(MINUTE([datetime] - "00:00:00") / 30.0))
)
)
``````
1 Like

Hi Steve, my issue is that I am using it on a DATETIME() column and this setup will not set the correct date.

Hmmm…worked for me in testing. Screenshot of your results?

This is the result.
Real time is 18:30 at that time You’re saying the Time In column has the value of 18:30?

Hi
The value is set to 01:00, not 18:30 as the time should be as it si the current time when adding row.