Forum Discussion

blfox33's avatar
blfox33
New Member
2 years ago
Solved

Help Please with an IF Statement with Time Values to create Interval Groups

Hi all,   My apologies in advance as I haven't ever asked a question using this community before but wanted to say "thank you" to the community because this support group has helped me out several ...
  • jgeddes's avatar
    jgeddes
    2 years ago

    This seems like a good case to use a SWITCH statement in a calculated column.

    Something like this should get you pointed in the right direction. (I used [Time] as the column with times in it where you had [Helper].)

     

    _condition =
    SWITCH(
        TRUE(),
        AND([Time] >= TIME(7,30,0), [Time] <= TIME(10,30,0)), "Breakfast",
        AND([Time] >= TIME(12,0,0), [Time] <= TIME(15,30,0)), "Lunch",
        AND([Time] >= TIME(16,30,0), [Time] <= TIME(18,30,0)), "Snacks",
        AND([Time] >= TIME(19,30,0), [Time] <= TIME(23,0,0)), "Dinner",
        AND([Time] >= TIME(0,0,0), [Time] <= TIME(4,0,0)), "Midnight",
        "Other Snacks"
    )
Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
2 years ago

blfox33 Time values are fractions of a day, like 1/24/60 would be in minutes so maybe something like:

Time Slot Conditions Column:
VAR __Minutes = 1/24/60
VAR __MinutesInHour = 60 * __Minutes
VAR __Helper = [Helper]
VAR __Resut = 
  SWITCH( TRUE(),
    [Helper] >= __MinutesInHour * 7.5 && <= __MinutesInHour * 10.5, "Breakfast",
    [Helper] >= __MinutesInHour * 16.5 && <= __MinutesInHour * 18.5, "Snacks",
    [Helper] >= __MinutesInHour * 19.5 && <= __MinutesInHour * 23, "Dinner",
    [Helper] >= __MinutesInHour * 0 && <= __MinutesInHour * 4, "Midnight",
    "Other Snacks"
  )
RETURN
  __Result