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

Date Time

Accepted answers

binu_acs

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

Date_240224.yxmd

jdminton

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.

Identify days before.yxzp

Number of business days before.yxmc

All comments

aatalai

@HomesickSurfer

does this post help?

https://community.alteryx.com/t5/Alteryx-Designer-Desktop-Discussions/Counting-number-of-business-days-between-2-date-ranges-taking/td-p/918248

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

binu_acs

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

Date_240224.yxmd

jdminton

Identify days before.yxzp

Number of business days before.yxmc

jdminton

@HomesickSurfer Did this do what you needed?

HomesickSurfer

Hi @binu_acs

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!

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15874

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703