Help! Need to calculate the next instance (date) of a variable weekday beyond a fixed date

[Graesen]

Hi. I've never used Excel to calculate dates before. I have a basic grasp of some date related functions but not enough to be able to solve the following problem. I'm hoping someone with more knowledge than I have will be able give me some help.


Basically, I need to display the next (date) instance of a weekday following a specified date.


For Example


In cell A1 I've recorded a 'start date'
in cell A2 I have a formula that calculates a weekday based on other data that outputs a weekday, e.g Monday, 2, Tuesday, 3, etc.
in cell A3 I would like to display the next date of the same weekday found in cell A2, following the date in cell A1


The output would look something like this


(A1) 01/05/2018 [dd:mm:yyyy, fixed]
(A2) Monday (the variable)
(A3) 07/05/2018 (the Output)


I'm playing around with WEEKDAY function to add an appropriate number of days to A1 +7 for the week but each time I change either the start date, or the start day, the formula I have breaks. I think it's down to the 'Return Type' not being dynamic as the formula I've created will work if the return type is change to account for the change in day.


I've deliberately not included my formula as I'm sure it isn't correct or appropriate!


Any suggestions you make would be gratefully appreciated. At this stage I'm open to anything!


With Thanks
Graesen

 

[StephenCrump]

Assuming A2: 1=Sunday, 2 = Monday ... 7 = Saturday

Try A3: =A1+8-WEEKDAY(A1-A2+1)