Calculate date excluding weekends and holidays

[MattR79]

I am trying to figure out how to calculate what the date will be a specified number of days after a provided date, while excluding both weekends and holidays.  For instance if the closing date was 2025-06-04 then the date available for QC (the columns from my file) should be 2025-06-20.  I have attached a file showing the closing date as well as the desired outcome based on the calculations that the end user provided.

[binuacs]

@MattR79 Can you provide the calculation to get the desired date, 6/20/2025, based on the closing date 6/4/2025?

[MattR79]

I think they are using 10 business days after the closing date excluding weekends and holidays, but honestly, the day count is a bit immaterial.  I really just need to understand how to calculate a future date excluding those two categories, then I can adjust the day count as needed to align with what they show.

[Pilsner]

Hello @MattR79 

I've attempted to create a workflow that looks "x" number of work days ahead of a given date. In the example below, I looked 10 days ahead; however, this can be changed via the text input.

The steps are as follows:

1) Generate a unique list of date your interested in looking at:

2) Append the number of days your interesting in, to your dates (in this case 10)

3) Generate enough rows such that the date we are looking for is either met or exceeded. 

4) Perform a date time add to add the number of days from the generate rows too, too the original date. 

5) Find the day of the week each date is on.

6) Filter out weekends.

 7) Sort the data and then apply a record ID.

8) Filter to the data so the record ID = the desired number of work days ahead (i.e 10)


If you are wanting to filter out holiday days too you will need to include a separate input with all holiday dates. From there I would recommend using a join tool to join on holiday dates, then continue from either the left or right anchor. 


I have attached the workflow below with some annotations but please let me know if you have any questions.

Regards - Pilsner

[MattR79]

Thank you, but this doesn't quite seem to be doing what I need, as I would expect to see as many rows in the browse tool as are coming in from the file, but there is only one record coming out due to the last filter, which is only giving the record where the ID matches the number of days to add on.  I also had some questions about why certain things were done, for example in the Formula tool right after the generate rows tool, you have it adding the row count versus the days to add on, with that none of the dates match.  I made some changes and have separated the original results and what I got with my changes.  I was able to get some to match up but not all, and I'm unsure as to why.  I think it might have something to do with the generate rows tool, but I'm not familiar enough with that to be sure.

[Pilsner]

Hello @MattR79 

I've added a join tool, which joins on closing data, which should allow for the number of rows to be matched. I've also unioned on all the empty records so that the record count matches exactly. 

I've attached the updated workflow below, please let me know how you get on.

Regards - Pilsner

[MattR79]

It looks like the calculation is working and giving me the right date, but only for items that were closed on 2025-06-02, because in the final filter there is a formula that only keeps items where the record ID equals the days to add on +1.  I need the workflow to give me the date for all of the records with a closing date in the file.  Based on what is in the file there should be a date for 02 to 09 June, as shown below.

Closing Date	Date available for QC (Desired Result)
6/2/2025	6/17/2025
6/3/2025	6/18/2025
6/4/2025	6/20/2025
6/5/2025	6/23/2025
6/6/2025	6/24/2025
6/9/2025	6/25/2025