Identify earlier date based on Day of Week

[HomesickSurfer]

Hi All;

I'm stuck and need some help...maybe I'm overthinking what I've attempted.

I'd appreciate and tips, guidance or solution.

I need to identify the business date (less Sat. and Sun.) that is x SLA days before the upcoming x Due Day.

(The data does not have a Due Date or any other date)

In the case below, upcoming Monday from today 2024-02-23 is 2024-02-26.

3 days before Monday 2024-02-26 is today 2024-02-23.

Expected result should be 2024-02-21 (Monday 2024-02-26 less 3 days, excluding Saturday and Sunday)

Once I get this part done, I can additionally exclude observed holidays with a Join

Thank you

Due Day	SLA
Monday	3

[aatalai]

@HomesickSurfer 

does this post help?

https://community.alteryx.com/t5/Alteryx-Designer-Desktop-Discussions/Counting-number-of-business-da...

[HomesickSurfer]

Hi @aatalai 

Thx for the suggestion.  It wont help.  I'm aware how to do that, but my data doesn't have actual dates, just a day of the week, eg. Monday, and a numeric value to subtract and determine x day before the Monday

[binuacs · Accepted solution]

@HomesickSurfer you can find the next date based on the Due Day

[jdminton · Accepted solution]

This was a particularly interesting exercise. You would think that a simple formula would work, but it won't allow for holidays and changing SLA days without manual intervention. I attached a workflow that uses a batch macro to identify the number of SLA days preceding the day of the week identified. I also included holidays as a test. The SLA data feeds into the macro to determine the number of days to use. If that changes, the macro will automatically update. This could be further modified if you wanted different date ranges and SLA data.

 

 

 

[jdminton]

@HomesickSurfer Did this do what you needed?

[HomesickSurfer]

Hi @binuacs 

Thank you for this.  It works.  It identifies the current and next upcoming dates for which I can count back the # of SLA days.

[HomesickSurfer]

Hi @jdminton 

Thank you!  You created the entire flow for me :)  I like it a lot.  I will use this with some slight modifications to suit my input data.  Works very well, and; identifies future SLA dates...which I may need to consider in my use case.

[jdminton]

Great! Good luck!