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

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

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

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

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: // 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

// From Hours 0000 to 0559

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: 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