Abstract

You want to determine a day about one year ago but it should be the same weekday? You have two reasonable choices, I think:

  1. The same weekday and the same calendarweek of previous year:
=B3-(52+(CALENDARWEEK(B3-MONTH(B3)+6,21)<>CALENDARWEEK(B3-MONTH(B3)-358,21)))*7

Please note that this formula logically extends values if same calendarweek does not exist.

  1. Closest same weekday to direct day of source year:
=DATE(F$2,MONTH(B3),DAY(B3))+CHOOSE(WEEKDAY(B3)-WEEKDAY(DATE(F$2,MONTH(B3),DAY(B3)))+7,1,2,3,-1,-2,-1,0,1,2,3,-3,-2,-1)

Previous_Year_same_Weekday_and_Calendarweek