Creating a formula field to capture time intervals

Question

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

Mez · Answer

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.

Sign up

Login to the community