Subtract Saturday, Sunday, & Monday

[Blue1823]

In Microsoft Excel, I would like to subtract Saturday, Sunday, & Monday from the days of the week and still have the formula give me a calculated day. Right now I have a WEEKDAY formula that calculates 29 days subtracted from a final date (i.e. January 22, 2010 - 29 --> final answer Dec 15, 2009) but excluding weekends (Sat & Sun) & Mon...I'm not sure how to utilize that formula or one similar to also subtract Mondays.

I'm using Excel 2007.

 

[Dave Patton]

Welcome to the forum.

You could use a formula like =WORKDAY(A2,-29,Holidays)

The named range "Holidays" includes all holidays including Mondays.

 

[Blue1823]

Dave, thx for your help. So I have to list all the Mondays in my range? There's no simple way less script writing to eliminate Mondays from formula?

 

[barry houdini]

Assuming you have the date in A2 (e.g. 22-Jan-2010) and number of days to subtract (e.g. 29) in B2 then you can use this formula

=A2-SMALL(IF(ISNA(MATCH(WEEKDAY(A2-ROW(INDIRECT("1:"&B2*5))),{1,2,6},0)),ROW(INDIRECT("1:"&B2*5))),B2)

confirmed with CTRL+SHIFT+ENTER

 

[Jonmo1]

Dave, thx for your help. So I have to list all the Mondays in my range? There's no simple way less script writing to eliminate Mondays from formula?

Also, it's VERY easy to create a list of mondays.....

Just type the date of the first monday in say A1,
Then in A2 put
=A1+7
and fill down as far as you'd like.
There's your list of mondays

 

[Blue1823]

@ barry houdini...your formula worked although I sat in front of my computer for a minute trying to figure out how the heck you came up with it and what each part meant, but I couldn't. Could you explain what's what? Does the 1 represent Monday?

 

[Blue1823]

@ barry houdini...your formula worked although I sat in front of my computer for a minute trying to figure out how the heck you came up with it and what each part meant, but I couldn't. Could you explain what's what? Does the 1 represent Monday?

 

[Dave Patton]

You can try using Excel's built in Formula Evaluation.
I would test and review the results with a small number say 3 or 4.
You should be able to visualize each part of the formula.

 

[barry houdini]

Sorry, formula should be

=A2-SMALL(IF(ISNA(MATCH(WEEKDAY(A2-ROW(INDIRECT("1:"&B2*5))),{1,2,7},0)),ROW(INDIRECT("1:"&B2*5))),B2)

otherwise it excludes Fridays rather than Saturdays (thanks for pointing that out, Dave, good call)

To explain briefly......this part

=ROW(INDIRECT("1:"&B2*5))

builds an array of digits 1 to n based on the value in B2, e.g. if B2 is 3 you get

{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15}

5 is a somewhat arbitrary number, it needs to be at least 4 for the case where B2 = 1 and A2 is a Tuesday......

That array is then subtracted from A2 to give you an array of dates, the last 15 before A2 in my example.

WEEKDAY is then used to ascertain the weekday of those dates and those that don't match {1,2,7} 1 = Sun, 2 = Mon, 7 = Sat are effectively discarded and SMALL is used to take the B2th smallest date out of the others, i.e. the Tuesdays, Wednesdays, Thursdays and Fridays, hence giving the final result