Date calculations

[Shank]

HI All,

I am struggling a bit on calculating the dates for below scenario can some help me on this .

I have start date and end date and also the list of holidays. I need to check if my start date and end date falls in between holiday list, then I need to add no of holiday that falls in between start date and end date to end date (i.e., End date + no of holidays)

I tried this by applying generate rows formula to start and end date and join with holiday list to count no holidays and add back to end date. but this is tedious when we have huge set of data. is there any other workaround for this?

I have attached the Alteryx flow what I tried to achieve.

Data: 

Project	Start Date	End Date
A	11/2/2020	11/20/2020
B	11/9/2020	11/27/2020
C	11/9/2020	11/27/2020
D	11/9/2020	11/27/2020
E	11/16/2020	12/4/2020
F	11/16/2020	12/4/2020
G	11/16/2020	12/4/2020
H	11/16/2020	12/4/2020
I	11/16/2020	12/4/2020
J	11/23/2020	12/11/2020
K	11/23/2020	12/11/2020
L	11/23/2020	12/11/2020

Holiday List:

Date

1/1/2021

2/11/2020

2/12/2020

2/13/2020

2/14/2020

2/15/2020

2/16/2020

2/17/2020

2/18/2020

4/4/2021

5/1/2021

6/14/2021

9/21/2021

10/1/2021

10/2/2021

10/3/2021

10/4/2021

10/5/2021

10/6/2021

10/7/2021

11/5/2020

11/6/2020

11/7/2020

11/8/2020

11/9/2020

[JosephSerpis]

Hi @Shank I mocked up a workflow let me know what you think?

[atcodedog05]

Hi 

Here is a workflow for the task.

Output:

Workflow:

Hope this helps 🙂

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

[atcodedog05]

Hi @Shank 

Just checked both the workflows your and mine where i am using append tool.

Using append tool data will explode more (i,e create more rows )

Let me look for a better approach efficient approach.

[atcodedog05 · Accepted solution]

Hi @Shank 

Here is a workflow for the task.

Output:

Workflow:

Previous workflow:

I am generating rows for only months and year. So the number of rows generated is reduces by a lot.

Previous workflow : Rows generated 288

Proposed workflow : Rows generated 60

Rows reduced by a lot.

Hope this helps 🙂

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

[atcodedog05]

Happy to help 🙂 @Shank 

Cheers and Happy Analyzing 😀

[Shank]

@atcodedog05 Thanks for the workaround this will really help us to reduce number of columns in the workflow.

[Shank]

@JosephSerpis This solutions will work but we might tend to end up more number of rows when we use Append tool.