How to get custom date count while excluding holidays & weekends?

[TRIPAVI6]

Hi All,

Need one help with the calculation. I need to get day count from the latest date in data. This latest date will be dynamic and keep updating on daily basis as I get new data everyday.

I need to calculate days passed using this dynamic latest date while excluding weekends, & if any date missing b/w. For weekends, day count of last weekday comes.

For Ex:

25/11/20 - Day1

24/11/20 - Day2

23/11/20 - Day3 

22/11/20 - Day3 (weekend)

21/11/20 - Day3 (weekend)

20/11/20 - Day4

18/11/20 - Day6 ( As 19/11/20 is missing hence, Day 6)

17/11/20 - Day7

Can anyone help with the solution for the same?
Thanks,

[DavidP]

Hi @TRIPAVI6 

Have a look at the attached workflow. First I converted your input dates to Alteryx date format, then created a list of all dates in the range and removed weekend days and added a day number. 

If you now join this back to your original dates, you get the day numbers associated with each date in the range.

For holidays you'll have to create a separate list of dates and filter them out of the list in the box.

[atcodedog05]

Hi @TRIPAVI6 

Here is a workflow for the task.

Output:

Workflow:

Hope this helps 🙂 Feel to ask if you have any questions

If this post helps you please mark it as solution. And give a like if you dont mind 😀👍

[TRIPAVI6]

Hi There, Thanks for sharing the solution but it's not 100% accurate. I've attached one output got using your formula but there are some bugs.

Can you please check & suggest how to rectify?
Thanks,

[atcodedog05]

Hi @TRIPAVI6 

I have updated the workflow with correction.

Output:

Please do check and let me know

[TRIPAVI6]

Thanks for the efforts but there is still gap.

1. Latest date 24/11/20 is not available in final output.
2. Check the below snapshot with highlighted rows. It should be Day3

Thanks,

[atcodedog05]

Hi @TRIPAVI6 

Thank you for bringing that up will fix it right away.

[atcodedog05]

Hi @TRIPAVI6 

Sorry about the issue.

Fixed it

Updated output:

Please check and let me know

[TRIPAVI6]

Unfortunately one issue is still there.
If you see in your snapshot, 20/11 Friday is Day 3 but Thursday is Day5 which should be Day 4

[atcodedog05]

Hi @TRIPAVI6 

My bad. Updated the workflow

Output

Please check and let me know.