Set Initial Value of TIME column to nearest 15 minute interval

[GreenFlux]

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.

[Jonathan_S]

This is Great.

Thank you.

[TyAlevizos]

@GreenFlux so close! your last switch needs to support the exact top of the hour:

MINUTE([TimeDesired]-"0:00")>=0,"0:45",

[GreenFlux]

Good catch! Thanks @TyAlevizos!

[Cullen_Prosper]

Can i use this to say a file playback length like a video is 2 minutes long , total newbe so not even sure if that goes in the yes/ no configuration

[GreenFlux]

Hi @Cullen_Prosper,

I’m not sure the technique in this post would apply in your case. I would suggest starting a new post to describe your issue, and include some background on your app with a few screen shots if possible.

[Josh_Klassen]

Hey,

Is this still working for you?

I tried inputting the information and it doesn’t seem to be working for me