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

Screenshot 2020-01-04 at 18.32.10

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.