Calculate Working Dates According to two Dates Criteria

[almartinez]

Hello Team,

I'm trying to calculate the date in which an item needs to be addressed (actioned) depending on the status of this item (Assigned/ On Hold) and the number of days you have to do that (per rule), so as not to have it as out of SLA (Service Level Agreement). The tricky part is that if the item is "Assigned" you have 1 day to action that item (24 Hrs) this is given by the "Assigned Date" column, on the other hand if the item is "On Hold" the date is given by the "Due Date" column. I used to do this using the Excel function "=WORKDAY(start_date,days, holidays)" where working days exclude weekends and any dates identified as holidays, but in Alteryx I cannot find any function like that. The field days ("1" in the example below) tells me I have 1 day to action that item.

I've seen the other topics regarding Calculate Business Days but is not what I'm looking for since the results is a number instead of a date.

Your assistance is highly appreciated Alteryx Community.

Thank you in Advanced,

[andrewdatakim · Accepted solution]

Hi @almartinez,

This is what you are looking for. Because there is no weekday formula you need to fake one.

IF [Status]='Assigned'
THEN
(IF DateTimeFormat([Assigned Date],'%a')='Fri'
THEN DateTimeAdd([Assigned Date],3,'day')
ELSEIF DateTimeFormat([Assigned Date],'%a')='Sat'
THEN DateTimeAdd([Assigned Date],2,'day')
ELSE DateTimeAdd([Assigned Date],1,'day')
ENDIF)
ELSEIF [Status]='On Hold'
THEN [DueDate]
ELSE Null()
ENDIF

[SeanAdams]

Hey @almartinez

I've worked this up in an Alteryx flow (attached).   It looks very intimidating, but I'll talk you through it.   You can convert much of this to a macro to do what you're looking for, but I didn't want to complicate it too much - we can always convert this to a macro with you as a followup

Steps: 

1.  Work out which days are working days
   1. There are two pieces to this - weekends and holidays
   2. In some countries, Friday and Saturday are the weekends - in other countries, it's Saturday and Sunday.   So I've left this flexible with a lookup table that you can adjust as you need.
   3. Additionally - you need to be able to add holidays - so I've done this with a lookup table (just like in Excel) so that you can add your own holidays.
   4. The way that this whole process works is that it generates about 1000 numbers from 1 to 1000, and then converts these into dates (by adding 1 day at a time to 01 Jan 2017).   What you get then is 1000 sequential dates
   5. It then brings in the weekend flag so now you have 1000 dates with a weekend flag
   6. It then brings in the holidays so now you have 1000 dates, with a weekend flag and a holiday flag
2. Next you want to look at your tickets
   1. If the ticket is on hold - then the calc of the last date is easy (just take the due date)
   2. If the ticket is assigned - take the next working day
      1. To do this - just join the data to the working days calculated above
      2. Then sort this so that the smallest working day comes first
      3. Then take the first row.  
      4. This way, you get the first working day after the assigned date

I've attached the workflow to this post - you should be able to make this work from here outwards, and we can always clean this up by turning it into a macro later, and you could then also add features like multi-region (for different holiday calendars), and 2 and 3 day SLAs.

 @almartinez - if this gets you to a solution, would you mind marking this as solved - or if there are still additional questions, then feel free to reply on this thread.

Cheers

Sean

[almartinez]

This is great, thank you Andrew. I've just used some of the formula you wrote and works fine!  

[andrewdatakim]

Your welcome @almartinez. The only thing it should be missing are the holidays and you should be set.