Categorizing time of day field into groups

  • 0
  • 1
  • Question
  • Updated 3 years ago
  • Answered

We have a table for Hospital Admission, which includes the exact time of day the patient was admitted (11:46am, 2:34pm, 5:03pm, etc.).  We would like to group those times into 3 buckets, 12:00am-6:00am, 6:01am-6:00pm, & 6:01pm-11:59pm.

Is there a formula that will calculate the bucket for each time of day? It is fine for the formula to output a text field. 

Photo of John

John

  • 60 Points

Posted 3 years ago

  • 0
  • 1
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,664 Points 50k badge 2x thumb
Try this

var timeofday TOD = ToTimeOfDay([Admission Time]);

IF(

$TOD >= ToTimeOfDay("12:00 am") and $TOD <= ToTimeOfDay("6:00 am"), "Midnight Shift",

$TOD >= ToTimeOfDay("6:01 am") and $TOD <= ToTimeOfDay("6:00 pm"), "Day Shift",

$TOD >= ToTimeOfDay("6:01 pm") and $TOD <= ToTimeOfDay("11:59 pm"), "Evening Shift")
Photo of John

John

  • 60 Points
Thank you so much, this worked perfectly.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,664 Points 50k badge 2x thumb
thx for letting me know.