Set Initial Value of TIME column to nearest 15 minute interval

This one took me a minute to figure out so I figured I’d share here to save someone else the time. I needed a TIME column with an Initial Value rounded up to the nearest 15 minutes.

TIME(
  TIME(HOUR(NOW()-"0:00")+"1:00")-
    IFS(
      MINUTE(NOW()-"0:00")>45,"0:00",
      MINUTE(NOW()-"0:00")>30,"0:15",
      MINUTE(NOW()-"0:00")>15,"0:30",
      MINUTE(NOW()-"0:00")>0,"0:45"
    )
  )

NOW() is a time but HOUR() and MINUTE() take a duration as input, so subtracting β€œ0:00” converts the data type.

Then I go to the start of the next hour and come back an interval of 15 minutes based on the current time.

[EDIT] - Almost forgot! Add this to the Valid If expression to ensure the time is always on an even 15 minute interval.

OR(
MINUTE([TimeStart]-"0:00")=0,
MINUTE([TimeStart]-"0:00")=15,
MINUTE([TimeStart]-"0:00")=30,
MINUTE([TimeStart]-"0:00")=45
)

In my case, the client wants it auto-filled for convenience, but needs the option to edit. Therefore, they could change the value to some other time that’s not an even 15 minutes. This setup allows editing, but ensures the minutes are always 0, 15, 30 or 45.

8 Likes

This is Great.

Thank you.

1 Like