Need a Wednesday date that is the nearest to mid month

[jm594]

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

Original Date
9/15/2019
9/15/2019
1/31/2020
10/31/2020
6/15/2020

I have to add 90 days from each of these dates but the target date should be a "middle of the month" date and land on a Wednesday. For example, if I add 90 days to the three dates above I get the following:

New date plus 90
12/14/2019
12/14/2019
4/30/2020
1/29/2021
9/13/2020


But what I really need are the dates you see next because they are "mid-month" and select the closest Wednesday to mid-month. The Wednesday can be on either side of the mid month date.

12/18/2019
12/18/2019
4/15/2020
1/13/2021
9/16/2020

So, to recap, I need the *plus 90* date to fall near an earlier mid-month date on a Wednesday. Can someone help me with this? Thank you very much. Greatly appreciated.

[dflak]

Here is the magic formula: =DATE(YEAR(A2+90),MONTH(A2+90),15)+4-WEEKDAY(DATE(YEAR(A2+90),MONTH(A2+90),15))

In the attached, Columns B:F were used as intermediate steps to developing this formula. You can delete them if you want. They are there to show the logic.

First I add 90 days, that gives me a month. Then I find the 15th day of the month. Then I find out what day of the week it is. Sun = 1, Wed =4. Then I get the difference between Wednesday and the 15th. It's zero if the 15th is Wednesday, positive if before Wednesday and negative if after Wednesday. I add this number to the 15th date, and that advances or backs up the date to Wednesday.

[jm594]

Yep, worked like a charm.
Thank you so much.
Have a great week!!