Formulas relate to last years dates, need to update to reflect this year's dates

This file was created by my employer last year and has not been updated. I want to use a modified version this year for looking at student behaviors.

On the first sheet is where the data is entered, on the second sheet it is then summarized by work week. The problem is, last school year started Monday August 27 and this school year started Monday August 26 so all the formulas are off by one column (and things are complicated by weeks crossing over two months).

What's the best way to fix all the formulas so they pull the right data? I changed the week dates on the second sheet to what they should be.

Thanks!
Frequency Behavior Template .xlsx

It's all the formulas that look like this that need to be corrected to correspond to the dates on the first sheet. This is from cell B4:

=IF(AND('Targeted Behaviors by Day'!AB2="",'Targeted Behaviors by Day'!AC2="",'Targeted Behaviors by Day'!AD2="",'Targeted Behaviors by Day'!AE2="",'Targeted Behaviors by Day'!AF2=""),NA(),(SUM('Targeted Behaviors by Day'!AB2:AF2)))

That's giving me August 27-August 31. I need August 26-August 30. I have been manually changing formulas but I need a more efficient fix. I simply don't have enough time in the day. Changing the value of B3 on Targeted Behaviors by Week doesn't have any effect on where the formulas pull from.

Hi Tony, any ideas yet?