Calculating new date from old date and workdays

So i have Dates(dd-mm-yyyy) in column A and "No. of workdays" in column B. I also have a list of public holidays in a separate file.

Say i have date "07-11-2022" which happens to be Monday. And let's say i have value "-2" in column B.(The number varies for each row and could be negative or positive)

Now, what i want is that it returns me the date, 2(for this case) working days prior to 07-11-2022 which would be "03-11-2022"(since 5th and 6th are Sat and Sun). Similarly, just like Sat/Sun, i don't want it to consider any public holidays,the list of which i have in a separate sheet.(in above example say 3rd Nov happens to be a holiday, so the answer i expect would be 2nd Nov)

How can i do this?

Date Time

Accepted answers

All comments

JamesCharnley

Edit: Misread this like a clown. Pretty hard coded to the -2 in my instance. Look to a logic like @DataNath for something more dynamic

Hi @Ayushthe1 , Someone will come in with a more efficient solve probably but I can try and explain the logic and this should work fine. I'm Generating Rows for all dates within my dataset, then finding the weekday and filtering out the weekends. If you didn't want public holidays too you could outer join the list of holidays against this list. Then I'm joining it with the date I want to check against, and using the Multi-row formula to look behind two days, which only have the relevant days left in the list.

2PriorDays.yxmd

DataNath

Here's one way I can think to do this @Ayushthe1:

1) We Generate Rows to get the raw 'finishing date' i.e. your [Starting Date] takeaway the [No of Days]

2) We then check the days in there to a) see if they're weekends and, b) check if they're part of the incoming 'holidays' list and assign a value of -1 to these if so

3) Based on the record we then calculate the sum of these new '-1' values to then work out how many more days we need to take off to consider the holidays/weekends

4) We then apply this new total of extra days to take off and arrive at our final date, before joining back to the original data

Here's an extra example with -10 days and the following holidays:

Edit: Tested my solution a little more and didn't stand up - the following looks to get more accurate results but generates all dates starting from Jan 1st 2020. If you have dates beyond this/way more recent then you can adjust the Generate Rows formula to increase performance.

(Takeaway N non-working days) workflow. The run above was done with the same holidays as shown further up.

DateTimeAdd skipping holidays and weekends.yxmd

Takeaway N non-working days.yxmd

gabrielvilella

Here is how I did this. I just created the dates for a week before the date you want, then keep just the valid one.

WorkingDays.yxmd

Ayushthe1

Thanks for all your efforts!!

Ayushthe1

Thanks a lot for your efforts. I was thinking somewhere along the lines of

1) Having a calendar from 2020 to 2024 with all dates and days.

2) A file with all public holidays from 2020 to 2024

3) Joining them to get a list of all Non-working days(Sat, Sun and Public Holidays)

4) Having another list of Working days(i.e. no sat sun or public holiday)

5) In list (4), i could arrange them in order with a serial Number

And then we could work things around

Note that working days column could also be positive meaning we would need date after that the starting date

Really appreciate your work, i am a beginner in alteryx, it would be great if you could build something to accommodate +ve ones as well

Thanks!

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703