Calculating the dates of the current status

[Maryfrances2024]

Please help me with my workflow, I don’t know what tool I should use. The result I wanted is to determine the length or days of the current status excluding holidays and weekends Please see the example data below. Thank you so muuuuuch!!!

Date Status
10/17/2024 non-compliant
10/14/2024 non-compliant
10/9/2024 non-compliant

10/4/2024 Escalated to managers
10/1/2024 Escalated to managers

Assuming that the 1st status is Escalated to leads, the status should be like this:

Date               Status Aging                     days
10/4/2024       Escalated to managers    3

But then after another 3 days the status changed to non-compliant, and the status does not change after 9 days. The count should be starting from 10/9/2024 to 10/27/2024

Date                       Status                    Aging days
10/4/2024               non-compliant        9

[alexnajm]

Calculating the days between two dates excluding weekends is not too bad, but do you have a list of holidays to also exclude? You will need that!

[Maryfrances2024]

Hello! Here's the Holidates. Thank you!

[alexnajm]

I am not sure I understand your logic, so here's a start to help 1) calculate the number of days between the status and today (you can adjust as you need) and 2) exclude weekends and holidays. Go ahead and take it from here and come back with a workflow if you have questions!

[Maryfrances2024]

Hello! Thank you for helping me out. However, I think this is not the result I needed. 

So lets say, on the first run I have this data (please see Input data 10.4.2024 1st run excel file) and the current status is Escalated to managers, the workflow should calculate the dates based on the status, so for the 1st data the result should be like this:

Date               Status                                  Aging days

10/4/2024       Escalated to managers       3

And for the second run, assuming that we have an updated file which is the (Input data 10.7.2024 2nd run excel file), and the current status changed from Escalated to managers to non-compliant- now the status should be like this:

Date               Status                                  Aging days

10/4/2024      non-compliant                       9

The workflow should calculate the date and time based on the current status and when the status change it should will calculate as 1 day aging.

Hope this explanation helps! Thank you soooo much!!

[alexnajm]

Ok that's a different ask - you want the number of days (inclusive) between the first status' date and the subsequent date for when the status changes. Is that correct?

If so, why is the result this?

Date               Status                                  Aging days

10/4/2024      non-compliant                       9

Shouldn't it be this?

Date               Status                                  Aging days

10/1/2024      non-compliant                       9

I still think my workflow will get you a good amount of the way there - it will at least get you a way to exclude certain days from your aging calculation. so please re-try using my workflow as a foundation, and if you need further help post your workflow (Export it by using Options > Export Workflow)

[Maryfrances2024]

Apologies, I don't think I explained it correctly and in detail, and I missed including the unique code which I think is a very important part of this data, which I have included in the attached file.

There are multiple data for one specific unique code and the workflow should only capture the latest date and the current status, and calculate the dates based on how long the same status remains for days. 

The result is also in the excel file.

Please bear with meee! This is my first project. Thank you so much for your patience! 

[alexnajm]

@Maryfrances2024 please take the time then to use what has already been created and adapt it to your situation - find all dates between two dates, remove weekends/holidays, and count remaining days. I do not want to help further without an attempt on your end to learn the tools and utilize the Alteryx platform. The requirements also keep changing which makes it difficult to advise

Also the unique code is an important clarifier, but I do not understand your logic - why is 50505 aging for 3 days? There is not a calculation I can think of between those dates that would result in 2 days