3 Replies Latest reply on May 9, 2018 2:03 PM by Okechukwu Ossai

    Holiday, weekend/off hours and business calculation

    Albert Alaluf

      Hello,

       

      I searched this on the forums and even Google it, but I couldn't find what I'm looking for.

       

      The request is simple, however, I have the challenge to calculate;

       

      - Busines Hours: Mon to Fri, 8:00 AM to 5:00 PM

      - Off Hours: Excluding business hours

      - US Holidays

       

      I am able to do this request, however, if the holiday is Saturday or Monday, I should mark the first business day that it is Monday as a holiday. Basically, this means if the holiday_day is Saturday, adds 2 days and if Sunday adds 1 day to my date. I didn't mark weekends, it is not very important at the moment. The formula below doesn't consider if the holiday day is Saturday or Sunday.

       

      [Business_Hours]

      IF DAY([REQUEST_DATE]) = 1 AND MONTH([REQUEST_DATE]) = 1

         OR (MONTH([REQUEST_DATE]) = 1 and DAY([REQUEST_DATE])>=15 AND DAY([REQUEST_DATE])<=21 AND DATENAME('weekday', [REQUEST_DATE]) = 'Monday')

         OR (MONTH([REQUEST_DATE]) = 5 and DAY([REQUEST_DATE])>=25 AND DAY([REQUEST_DATE])<=31 AND DATENAME('weekday', [REQUEST_DATE]) = 'Monday')

         OR (DAY([REQUEST_DATE]) = 4 AND MONTH([REQUEST_DATE]) = 7)

         OR (MONTH([REQUEST_DATE]) = 9 AND DAY([REQUEST_DATE])>=1 AND DAY([REQUEST_DATE])<=7 AND DATENAME('weekday', [REQUEST_DATE]) = 'Monday')

         OR (DAY([REQUEST_DATE])>=26 AND MONTH([REQUEST_DATE]) = 11)

         OR (DAY([REQUEST_DATE])>=25 AND MONTH([REQUEST_DATE]) = 12) THEN 'HOLIDAY'

      ELSE

         IF (DATENAME('weekday', [REQUEST_DATE]) = 'Monday' OR DATENAME('weekday', [REQUEST_DATE]) = 'Tuesday' OR DATENAME('weekday', [REQUEST_DATE]) = 'Wednesday' OR DATENAME('weekday', [REQUEST_DATE]) = 'Thursday' OR DATENAME('weekday', [REQUEST_DATE]) = 'Friday') THEN

           IF DATEPART('hour', [REQUEST_DATE])>=8 AND DATEPART('hour', [REQUEST_DATE])<=17 THEN 'BUSINESS HOURS'

           ELSE 'OFF HOURS'

           END            

         ELSE 'OFF HOURS'

         END

      END

       

      So I was trying to do this;

       

      [Date_Adjust]

      IF [Business Hours] = 'Holiday' THEN

          IF DATENAME('day', [REQUEST_DATE]) = 'Saturday' THEN DATEADD('day', 2, [REQUEST_DATE])

          ELSEIF DATENAME('day', [REQUEST_DATE]) = 'Sunday' THEN DATEADD('day', 1, [REQUEST_DATE])

          END

      ELSE [Business Hours]

      END

       

      This returns as 'Expected type datetime, found string...........'

       

      Does anyone have a suggestion how to fix this?