Same Weekday and Calendarweek last year
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.
2. 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)
Last modified 2yr ago
Copy link