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

Holiday, weekend/off hours and business calculation

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.

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;

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

END

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

Does anyone have a suggestion how to fix this?

• 1. Re: Holiday, weekend/off hours and business calculation

It appears you need to use upper case to match your Business Hours calculation

Trying changing the first line of code to;

IF [Business Hours] = 'HOLIDAY' THEN

Hope this helps.

Ossai

1 of 1 people found this helpful
• 2. Re: Holiday, weekend/off hours and business calculation

Thanks.

1 of 1 people found this helpful
• 3. Re: Holiday, weekend/off hours and business calculation

You're welcome.