Round time up/down using DATETIME()

[Tritonos_Gruppe]

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.

[Kevin_Burbidge]

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!

[Steve]

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))
  )
)

[Tritonos_Gruppe]

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

[Steve]

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

[Tritonos_Gruppe]

This is the result.
Real time is 18:30 at that time

[Steve]

You’re saying the Time In column has the value of 18:30?

[Tritonos_Gruppe]

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.

[AlexM]

It works like a dream!

[khuslid]

Steve:

(
  [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))
  )
)

Thanks for this, Steve:)

[Rustam]

Do you know why this formula works with a delay? That is, rounding is triggered only after the string is saved, and if the string contains calculations referring to this rounding, then the calculation will be based on unrounded time.
Do you know why this happens? How to fix it? I will be very grateful!