3 Replies Latest reply on Oct 19, 2018 3:42 PM by Don Wise

    Creation of a Calculated Field to Categorize Orders Based on Time Ordered

    Casey Johnson

      I am looking to find the best way to utilize a Date & Time field to create a grouping of orders based on the time of day the order was placed.

       

      Need (regardless of the date):
      If OrderDate is between 6:00AM and 2:30PM then "First Shift"

      If OrderDate is between 2:31PM and 10:30PM then "Second Shift"

      If OrderDate is between 10:31PM and 5:59AM then "Third Shift"

       

       

      Thank you!

        • 1. Re: Creation of a Calculated Field to Categorize Orders Based on Time Ordered
          Matt George

          if datepart('hour', OrderDate) >6 and (datepart('hour',OrderDate) >=14 and datepart('hour',OrderDate)<15 and datepart('minute', OrderDate)<=30 then "First Shift"

           

          Same style for the other shifts should get you there!

          1 of 1 people found this helpful
          • 2. Re: Creation of a Calculated Field to Categorize Orders Based on Time Ordered
            Casey Johnson

            Your calculation definitely got me going down the right path but I'm still not getting the expected result.

             

            if datepart('hour', OrderDate) >6 and (datepart('hour',OrderDate) >=14 and datepart('hour',OrderDate)<15 and datepart('minute', OrderDate)<=30) then "First Shift"

             

            I had to add a ) after the 30, and the results were only orders between 2:00PM and 2:30PM.

             

            I tweaked the formula to this, but for some reason the minutes piece won't work. This gives me everything from 6AM to 2:59PM, even with the minutes logic:

            if (datepart('hour', [Order Date]) >= 6 and datepart('hour',[Order Date]) <=14 or

            (datepart('hour', [Order Date]) >= 14 and datepart('hour',[Order Date]) < 15 and datepart('minute', [Order Date]) <= 30)) then "First Shift"

             

            Any other ideas for logic/parenthesis to fix the minutes?

            • 3. Re: Creation of a Calculated Field to Categorize Orders Based on Time Ordered
              Don Wise

              Hi Casey,

               

              Maybe the attached (2018.2) workbook and below screenshots and example calc will help you. 

               

              I noticed that you want your shift date/time to carry forward into the next day.  In that case you'll want to set a Shift Period for the encompassing 24-hour period.  That will involve setting the period from 0600-1159 and then use of a DATEADD function to grab the additional period of time from 0000-0559 into the next day (otherwise you'd be grabbing the prior period).  That will be a calculation by itself:

              Screen Shot 2018-10-19 at 3.28.12 PM.png

              // 24-hour Shift Period

              // For the first shift segment (hours 0600-0000), keep the Date as is

              // For second shift segment crossing over midnight (hours 0000-0559), set the Date to pick up the next day from 0-6

               

              // From Hours 0600-0000

              IF  DATEPART('hour', [Your Date/Time]) >=6 and DATEPART('hour', [Your Date/Time]) <=23 then [Your Date/Time]

               

              // From Hours 0000 to 0559

              ELSEIF  DATEPART('hour', [Your Date/Time]) >=0 and DATEPART('hour', [Your Date/Time]) <6 then

                  DATEADD('day', 1, [Your Date/Time]) END

               

              Thereafter, a new calculation to set the segmented 'Shifts'.  Because you're using a specific time frame down to the minute the following calc is needed:

              Screen Shot 2018-10-19 at 3.28.51 PM.png

               

              This calc is based on the first one:

               

              //defines 6a-2:30p shift

              IF DATEPART('hour', [Shift Period 0600-0600]) >=6 AND DATEPART('minute', [Shift Period 0600-0600]) >=00 AND DATEPART('minute', [Shift Period 0600-0600]) <=30 AND DATEPART('hour', [Shift Period 0600-0600]) <=14

              THEN "1st Shift"

               

              //defines 2:31p-10:30p shift

              ELSEIF DATEPART('hour', [Shift Period 0600-0600]) >=14 AND DATEPART('minute', [Shift Period 0600-0600]) >=30 AND DATEPART('minute', [Shift Period 0600-0600]) <=30 AND DATEPART('hour', [Shift Period 0600-0600]) <=22

              THEN "2nd Shift"

               

              //defines 10:30p-6a shift

              ELSEIF DATEPART('hour', [Shift Period 0600-0600]) >=22 AND DATEPART('minute', [Shift Period 0600-0600]) >=30 AND DATEPART('minute', [Shift Period 0600-0600]) <=59 AND DATEPART('hour', [Shift Period 0600-0600]) <=23

              THEN "3rd Shift"

              ELSEIF DATEPART('hour', [Shift Period 0600-0600]) >=0 AND DATEPART('minute', [Shift Period 0600-0600]) >=0 AND DATEPART('minute', [Shift Period 0600-0600]) <=0 AND DATEPART('hour', [Shift Period 0600-0600]) <6

              THEN "3rd Shift"  END

               

              Because you're using very distinct periods, the time calc needs to be defined as such as well. 

               

              Hope it helps! Thx, Don