CODEWITHSUNDEEP
excelexcel-formulaexcel-dates
Emily Whipple
Emily Whipple

Reputation: 1

Excel Formula - Next Wednesday UNLESS Input is Tuesday

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.

Upvotes: 0

Views: 89

Answers (1)

Mayukh Bhattacharya
Mayukh Bhattacharya

Reputation: 27233

Perhaps try the following the formula:

enter image description here

=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)))

Upvotes: 0

Related Questions