Calculate What Day a Ticket Should Be Reviewed

[taschmies]

Hi all, I'm working on a project to calculate the date an incoming support ticket should be reviewed by. I am given a set of dates that contain the day the support ticket is requested.

From there I can calculate the day of the week this ticket was requested and the expected review date of the ticket (review date = request date +1). However, I need to apply the logic that if a ticket is requested on a friday or saturday then the expected review date should be the following monday rather than a saturday or sunday. Additionally, if the expected date is a holiday, then it should be adjusted to the next day that is not a holiday (I am provided a list of holiday dates).

I'm able to calculate the day of the week for the requested date and expected date, but I'm stuck on how to adjust the expected date if it lands on the weekend or holiday. Any advice or guidance would be appreciated. Thank you!

[abacon · Accepted solution]

@taschmies There might be other ways to do it but here is one way. I first check against the holidays list and set the review dates, then I get the weekday of those review dates and check if they are a Saturday or Sunday and push it up according to that criteria.

[KGT]

The solution by @abacon should give you all the methods you require to apply it to your data. You could pull a calendar and what every date maps to pretty easily from that as well.

I'll list some other techniques that I've seen.

A lookup table to map the days to 1-7, then using that in DateTimeAdd.

A simple IF/THEN to move the weekends.

IF [DayOfWeek]=="Saturday"

THEN DateTimeAdd([ReviewDate],2,'day')

ELSE

IF [DayOfWeek]=="Sunday"

THEN DateTimeAdd([ReviewDate],1,'day')

ELSE....

[abacon]

@taschmies If that worked for you, please mark it as the solution so others may find it quickly.