Excel Formula - Next Wednesday UNLESS Input is Tuesday

Question

I have searched many previous queries on this site for an answer and haven't found what I'm looking for. I know the formula that will display the following Wednesday after a given date. However, if the given date is a Tuesday, I want it to display the Wednesday of the following week, not the Wednesday immediately following that Tuesday. Basically if the input is a Tuesday, I want it to skip a Wednesday. Like so:

Input	Output
Tuesday 1/16/24	Wednesday 1/24/24
Wednesday 1/17/24	Wednesday 1/24/24
Thursday 1/18/24	Wednesday 1/24/24

Is there a way to get excel to do this?

Here is the formula I am currently using and understand (where H5 is the input date):

=IF(H5="",--"",(H5+8-WEEKDAY(H5,13)))

I'd love to stick with a version of this if possible and modify it so that it returns two Wednesdays out from a Tuesday.

Answer 1

Perhaps try the following the formula:

---

=IF(H5="","--",H5+8-MOD(H5-3,7))

---

Or, Using #Spill

=LET(α, H5:H7, IF(α="","--",α+8-MOD(α-3,7)))

---

Using WEEKDAY() it would be like as below:

=IF(H5="","--",H5+8-(WEEKDAY(H5,12)-1))

---

Or,

=LET(α, H5:H7, IF(α="","--",α+8-(WEEKDAY(α,12)-1)))

---