Find the previous Mon or Wed from a given Date

[JustHooch]

I need to find the Monday or Wednesday prior to a given date minus 4 days weekdays in order to Calculate a submission date.

Examples:

• Date given is 7/31, then I want to return 7/23/18
• Date given is 7/26, then I want to return 7/18/18
• Date given is 7/25, then I want to return 7/18/18

I have been looking around but I can't find that fits my need. I know I need to use WEEKDAY and CHOOSE might be helpful but that is about how far I have gotten.

 

[JustHooch]

Sorry I meant WORKDAY not WEEKDAY.

 

[MUKESHY12390]

perhaps this....

=A1-(WEEKDAY(A1,2)+VLOOKUP(WEEKDAY(A1,2),{1,7;2,6;3,5;4,4;5,3;6,2;7,1},2,0))

first try this formula, if it works then I will explain how it works.

put date in column "A" and paste this formula in columnB

 

[Tetra201]

Try

=WORKDAY(MAX(WORKDAY.INTL(A1+1,-1,{"0111111","1101111"})),-5)

 

[Tetra201]

Actually, the formula form Post # 4 can be shortened further to

=MAX(WORKDAY.INTL(A1-6,-1,{"0111111","1101111"}))

 

[JustHooch]

Thank you both very much. I decided to go with Tetra201 formula since it was simpler. It works perfectly. Can you explain to me how it is working? I love to have a better understanding of the mechanics.

 

[Tetra201]

You are welcome.

Here is how it works:
WORKDAY.INTL(A1+1,-1,"0111111") returns the previous Monday for the date in cell A1 (7/30/18 for 7/31/18)
WORKDAY.INTL(A1+1,-1,"1101111") returns the previous Wednesday for the date in cell A1 (7/25/18 for 7/31/18)
WORKDAY.INTL(A1+1,-1,{"0111111","1101111"}) returns a two-element array, namely {previous_Monday,previous_Wednesday}
MAX(WORKDAY.INTL(A1+1,-1,{"0111111","1101111"})) returns the date that is closest to the date in cell A1 (7/30/18 for 7/31/18)
WORKDAY(MAX(WORKDAY.INTL(A1+1,-1,{"0111111","1101111"})),-5) pushes it back by 5 working days (7/23/18 for 7/31/18)
Since 5 working days is 7 calendar days, the formula can be simplified to MAX(WORKDAY.INTL(A1-7+1,-1,{"0111111","1101111"})), or

=MAX(WORKDAY.INTL(A1-6,-1,{"0111111","1101111"}))

 

[JustHooch]

That makes total sense. Thank you for the explanation.