Add Days, if weekend, subtract 1 or 2 days

[ker9]

Hi,
S1 = 10/7/2010
I want T1 to be S1 +45 but to subtract 1 or 2 days if the result ends on a weekend

I've got it spread out into 4 cells, but would like to combine into one if that is possible or find a better way (the final result to below should be 11/19/10).
Then there is the question of how to take out holidays in the same way, always going to the working day before.

S1
10/7/10

T1

Please Login or Register  to view this content.

U1

Please Login or Register  to view this content.

(result is 1)

V1

Please Login or Register  to view this content.

Thank you.

[DonkeyOte]

If you're using XL2007 a simple alternative:

Please Login or Register  to view this content.

[ker9]

Thank you - but that won't work if the start date were 10/8 - the start date will vary but will always be a weekday. I am in 2007 but need formulas to work with 2003.

I've got it to down to one cell for the date and then a 2nd cell for the holidays (Holidays is a range)

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[DonkeyOte]

edit: below posted prior to OP revising above post and introducing holidays component

Thank you - but that won't work if the start date were 10/8

For 8th Oct the earlier suggestion would return 22-Nov ... which I believe to be the correct value, if not please outline why.

edit: regards your holidays requirement - this can be added to WORKDAY
(which can be used in earlier versions but necessitates ATP be activated)

If you need a non-ATP dependent version one approach would be:

Please Login or Register  to view this content.

there are others

edit: above not accounting for holidays as was not originally stipulated

[daddylonglegs]

I like the WORKDAY option that DonkeyOte suggested, you can add in the holiday range, i.e.

=WORKDAY(S11+45+1,-1,holidays)

I believe that will work as required whatever the start date

In Excel 2003 you can add in Analysis ToolPak by using

Tools > add-ins > tick "Analysis ToolPak". All users would need the add-in enabled.

Your 2 cell approach won't work if you might have successive holiday dates. This version will take account of holidays without using WORKDAY

=S11+45-SMALL(IF(WEEKDAY(S11+45-{0,1,2,3,4,5},2)<6,IF(COUNTIF(holidays,S11+45-{0,1,2,3,4,5})=0,{0,1,2,3,4,5})),1)

That will cope with a maximum of 5 successive non working days.....

[ker9]

DonkeyOte:
My bad - I looked at the calendar wrong and thought 11/22 was a Sunday - sorry!
Your code works - thank you!

[ker9]

daddylonglegs

Thank you! Both code options work wonderfully. I'm am going to try using the second option because there are successive holidays coming up in November.

You are all so very clever and I appreciate your help very very much!!

[ChrisCione2020]

Is there anyway to make this work backwards? In other words, instead of adding to the date, subtracting from a particular date but still accounting for holidays and weekends. I tried changing the 3 plus signs to minus signs, but it didn't work properly.

S11+45-SMALL(IF(WEEKDAY(S11+45-{0,1,2,3,4,5},2)<6,IF(COUNTIF(holidays,S11+45-{0,1,2,3,4,5})=0,{0,1,2,3,4,5})),1)

Say S11 was March 1 and I wanted to go back 5 days, skipping weekends; the date returned should be Feb. 23. And if (for some strange reason) Feb 23 was a holiday, then I would like it to return the date of Feb 22.

[ChrisCione2020]

Formula is no longer calculating correctly. =S11+45-SMALL(IF(WEEKDAY(S11+45-{0,1,2,3,4,5},2)<6,IF(COUNTIF(holidays,S11+45-{0,1,2,3,4,5})=0,{0,1,2,3,4,5})),1)


I hadn't used the worksheet (where this formula resided) for several years. When I put it back into use, I noticed that this formula is no longer working correctly. For example, if S11 = 12 June 2015 and I want to add 15 days (this number is coming from a reference cell), Excel is returning 26 June, not 6 July, which is correct (ignoring weekends and July 3rd as a lieu holiday for July 4).

Any idea what's going on?

[Jonmo1]

It seems like over the years you've forgotten what the intended purpose of the formula is.

It adds the #of days FIRST (regardless if they are weekend or holiday).
THEN tests if that resulting date is a weekend or holiday.
And if it is, then it falls back to the previous workday.

So June 26th IS the correct answer for that formula if S11 is June 12 2015.

[Jonmo1]

Actually, the formula seems to add 45 days, not 15

=S11+45-SMALL(IF(WEEKDAY(S11+45-{0,1,2,3,4,5},2)<6,IF(COUNTIF(holidays,S11+45-{0,1,2,3,4,5})=0,{0,1,2,3,4,5})),1)

So the correct answer would actually be July 27th.