Forum Discussion

NekoGTz's avatar
NekoGTz
New Member
3 years ago
Solved

If/Then Between Times

Good Morning All,

I think I'm overcomplicating here but I'm struggling to create a calculated column to identify "Work Shift" by looking at the hours of the day.

 

I have my times split into a single column.

 

 

I need one column that looks at the times and displays what shift that falls under.

 

0600AM to 0200PM = "Days"

0201PM to 1000PM = "Afternoons"

1001PM to 0559AM = "Midnights"

 

Any help is greatly appreciated.

 

Nick

Need Help
  • BA_Pete's avatar
    BA_Pete
    3 years ago

    Hi NekoGTz ,

     

    The basic syntax for your new column will be like this:

    if [ReceivedTime.2] >= #time(06, 00, 00) and [ReceivedTime.2] < #time(14, 00, 00) then "Days"
else ...etc

     

    Pete

3 Replies

  • BA_Pete's avatar
    BA_Pete
    Super User
    3 years ago

    Hi NekoGTz ,

     

    The basic syntax for your new column will be like this:

    if [ReceivedTime.2] >= #time(06, 00, 00) and [ReceivedTime.2] < #time(14, 00, 00) then "Days"
else ...etc

     

    Pete

    • NekoGTz's avatar
      NekoGTz
      New Member
      3 years ago

      Pete - Can't thank you enough! I've been scratching my head for days with incorrect time format.

       

      Final statement is: (and works perfectly)

       

      if [ReceivedTime.2] >= #time(06, 00, 00) and [ReceivedTime.2] < #time(14, 00, 00) then "Days" else if [ReceivedTime.2] >= #time(14, 01, 00) and [ReceivedTime.2] < #time(22, 00, 00) then "Afternoons" else "Midnights"

      • BA_Pete's avatar
        BA_Pete
        Super User
        3 years ago

        Hi NekoGTz ,

         

        Glad this got you sorted.

        You just need to adjust your second line to the following, otherwise you'll miss any times between 14:00:00 and 14:00:59 :

        [ReceivedTime.2] >= #time(14, 00, 00)  // changed to 14:00:00

         

        Pete