Excel. date and time = workshift X

Question

I have a cell with date and time in the format: (dd-mm-yyyy tt:mm)
Based on this i'd like to extract the shift that made the data in my excel file.

Shift times are as follows

• Dayshift: 06:01-14:00 (Monday-Thursday) 06:00-13:20(Friday)
  
• Eveningshift: 14:01-22:50 (Monday-Thursday)
  
• Nightshift: 22:36-06:00 (Sunday evening - Monday morning) 22:51-06:00 (Monday-Friday)
  
• Weekendshift: 13:21-01:20 (Friday) 10:40-22:35 (Sunday)
  
• Any event outside those shift should be returned as shift: Overtime
  
  I've been @ this for hours with no result. Anyone up for the challenge? :)

Answer 1

I think the way to do this is to set up a table with columns for the various parameters, and a calculated column for the weekday.

I Named the table Shifts and am using structured references for ease of maintenance and future understanding, but you could use regular references if you must.

Shift Table

Formula in E2:

=MATCH([@Day],{"Sunday";"Monday";"Tuesday";"Wednesday";"Thursday";"Friday";"Saturday"},0)

A formula that will return the Shift, given a timestamp:

=IFERROR(
       LOOKUP(2,1/(
              (MOD(timeStamp,1)>=Shifts[startTime])*
              (MOD(timeStamp,1)<=Shifts[endTime])*
              (WEEKDAY(timeStamp)=Shifts[wdNum])),Shifts[Shift]),
      "Overtime")

random results

using formula in a Table with structured references

=IFERROR(
       LOOKUP(2,1/(
              (MOD([@timeStamp],1)>=Shifts[startTime])*
              (MOD([@timeStamp],1)<=Shifts[endTime])*
              (WEEKDAY([@timeStamp])=Shifts[wdNum])),Shifts[Shift]),
      "Overtime")