Excel Workdays Formula (Specific)

[JiveTurkey2123]

Hey all,

Hope all is well! I wanted to ask for a huge favor here with the workday formula.

I am looking for a formula that will calculate 7 days in the past from the start date.
Sunday's cannot be included in the 7 day "count".
Monday-Saturday AND Holidays CAN be included in the 7 day "count".
However, the final result can only land on a Monday-Friday and not on a holiday, saturday or sunday
So if it did land on a Holiday, Saturday or Sunday, the end date would revert to the previous workday
Ex. If it lands on MLK Day (Mon, 1/17/2022), the end result would be Friday 1/14/2022
Ex. If it lands on Veterans Day (Fri, 11/11/2022), the end result would be Thursday 11/10/2022

example of what i am testing but not working: Workday.intl(Q26, -7, 11, A2:A13)
A2:A13 is the holiday list
Q26 is start date
-7 is minus number of days

Your help would be greatly appreciated!!

 

[Tetra201]

See if the following formula works for you:

=WORKDAY(WORKDAY.INTL(Q26,-7,11)+1,-1,A2:A13)

 

[JiveTurkey2123]

See if the following formula works for you:

=WORKDAY(WORKDAY.INTL(Q26,-7,11)+1,-1,A2:A13)

Thank you! However i think i need to mention a bit more detailed info:

1. Out of the 7 total count days:
Day 1-3: cannot count Sunday or Holiday's
Day 4-7: cannot count Saturday, Sunday or Holiday's

So for example:
If i chose 1/18/2022 for the start date
It should return 1/7/2022
Your formula returns 1/10/2022 - which is correct, but it shouldn't count the 15th, 16th, or 17th by my new logic above here. Sorry for the clarification

If i chose 1/19/2022 for the start date
It should return 1/10/2022

 

[Tetra201]

See if the following formula works for your new logic:

=WORKDAY(WORKDAY.INTL(Q26,-3,11,A2:A13),-4,A2:A13)

 

[JiveTurkey2123]

We are getting closer and truly appreciate your continued help!!!!
I just realized, it should be 6 days since the return date would be Day 0

CORRECTED:
Out of the 6 total count days:
Day 1-3: cannot count Sunday or Holiday's
Day 4-6: cannot count Saturday, Sunday or Holiday's

If start date is 1/27/2022, it returns 1/18/2022, but it should return 1/20/2022
Day 0: 1/20 (return date)
Day 1: 1/21
Day 2: 1/22
Day 3: 1/24
Day 4: 1/25
Day 5: 1/26
Day 6: 1/27 (start date)

1/23 doesnt count since its a Sunday

One more example:
If start date is 1/18/2022, should return 1/7/2022, NOT 1/10/2022
Day 0: 1/7 (return date)
Day 1: 1/8
Day 2: 1/10
Day 3: 1/11
Day 4: 1/12
Day 5: 1/13
Day 6: 1/18 (start date)

The reason why it shouldn't return 1/10/2022, is for the following:

Day 0: 1/10 (return date)
Day 1: 1/11
Day 2: 1/12
Day 3: 1/13
Day 4: 1/14
Day 5: 1/18
Day 6: 1/19 (start date) - this would be past the Start Date of 1/18/2022, so the return date should revert to the previous workday (1/7/2022)

1/15 doesnt count since its a Saturday
1/16 doesnt count since its a Sunday
1/17 doesnt count since its a Holiday (MLK Jr)

I hope this makes sense!!!!!

 

[Tetra201]

...
Day 5: 1/13
Day 6: 1/18 (start date)
...
Day 4: 1/14
Day 5: 1/18
...
I hope this makes sense!!!!!

Unfortunately, it does not for me.

 

[JiveTurkey2123]

My apologies. I re-reviewed my dates above and it was a bit off!

CORRECTED:
Out of the 6 total count days:
Day 0: cannot count Saturday, Sunday or Holiday's
Day 1-3: cannot count Sunday or Holiday's
Day 4-6: cannot count Saturday, Sunday or Holiday's

One more example:
If start date is 1/18/2022, should return 1/7/2022, NOT 1/10/2022
Day 0: 1/7 (return date) since Day 0 cannot be a Sunday (see above), so the return date should revert to the previous workday (1/7/2022)
Day 1: 1/10
Day 2: 1/11
Day 3: 1/12
Day 4: 1/13
Day 5: 1/14
Day 6: 1/18 (start date)

1/15 doesnt count since its a Saturday
1/16 doesnt count since its a Sunday
1/17 doesnt count since its a Holiday (MLK Jr)

I hope this should help clear things up!

 

[Tetra201]

Regretfully, here is my final attempt to help you since I don't enjoy chasing the ever-shifting goalposts:

=WORKDAY(WORKDAY.INTL(WORKDAY(Q26,-3,A2:A13),-3,11,A2:A13)+1,-1,A2:A13)

 

[JiveTurkey2123]

You are a god amongst men. A legend!!!! Thank you Tetra!!