Forum Discussion

SheilaAllas's avatar
SheilaAllas
Qrew Cadet
31 days ago

Creating a formula field to capture time intervals

I have a field that captures the time an event occurred, it is a Time of Day field type. These are examples of the output 8:13 am, 12:28 pm.

I would like to create a field that lumps times into 1/2 hour intervals. For example, If the Time of Day is =>8:00 am and =<8:29 am then the interval would be captured as 8:00 - 8:30.   If Time of Day is =>8:30 am and =<8:59am then the interval would be 8:30 - 9:00, etc. 

Nothing I've tried seems to work as I repeatedly run into sytax errors. 

Does anyone have suggestions?

Thanks

Sheila

formula query

2 Replies

Sort By
  • Mez's avatar
    Mez
    Qrew Assistant Captain
    30 days ago

    Since your field is a Time of Day type, you should be able to then use the minute() and within the if just check for less than 30. Assuming this field where you want the interval to display is Text: 

    var text hr = toText( hour( [Time of Day] ) );

    var text firstInterval = $hr & ":00 - " & $hr & ":29";

    var text secondInterval = $hr & ":30 - " & $hr & ":59";

    If(

      Minute( [Time of Day] ) < 30, $firstInterval,

      $secondInterval

    )

    *replace [Time of Day ] with your actual field name. 

    • SheilaAllas's avatar
      SheilaAllas
      Qrew Cadet
      30 days ago

      It took me a minute - but I figured it out. Thank you for your help!