Minus 30 days but must fall on a Wednesday

[jm594]

Here is my situation. I have a column of dates such as the following:

Original Date
9/1/2019
9/16/2019
10/1/2019


I have to subtract 30 days from each of these dates but if the *minus 30* date does not land on a Wednesday then I need to select the nearest previous Wednesday. For example, if I subtract 30 from the three dates above I get the following:

New date minus 30
8/2/2019
8/17/2019
9/1/2019

But what I really need are the dates you see next because they show the *previous* Wednesday.

New date minus 30 but showing earlier Wednesday
7/31/2019
8/14/2019
8/28/2019

So, to recap, if the *minus 30* date falls on a Wednesday, I'm good. If the *minus 30* date falls on anything other than a Wednesday then I need the calculated date to show the previous calendar Wednesday. Can someone help me with this? Thank you very much.

[canapone]

Ciao,

in A1 and below your dates.

=AGGREGATE(14,6,A1-ROW($30:$36)/(WEEKDAY(A1-ROW($30:$36))=4),1)

to be copied down


Regards

[Roel Jongman]

Hi and welcome..

Try with assumption that 9/1/2019 is in cell A1

=A4-30-WEEKDAY(A4-30,14)

[jm594]

Thanks Roel. This worked great and gave me just what I needed.