Convert calendar date in Excel to same weekday in future years

Question

I have a spreadsheet I have created from Outlook calendar export for 2014. I need to rollover the StartDate dates in my spreadsheet (B3) to the same day of the week in 2015 (e.g., Monday appointment in 2014 needs to be on Monday in 2015). I've tried: =DATE(YEAR(TODAY()),MONTH(B3),DAY(B3)) This formula only gives me the same date already in my StartDate column. I've been working on this for hours, and would really appreciate any help possible.

Thanks!

Answer 1

Suppose your date is formatted like 1/10/2014 or "Monday September 2014". As long as the year is in the end you could do the following:

A less indirect route would be to create a new column next to the StartDate column, with any symbol e.g. a dot "."

Then:

=concatenate(B3,C3)

So now your new variable will be "Monday 2014."

Afterwards, select the cells of the StartDate you need to rollover to the year 2015, and press ctrl+F, and simply replace "4." with "5"

The concatenation is useful here because in case your date is formatted as 11/04/2014, then the replacement will only change the "4" of the year and not the "4" of the month.