I need to compare sales volumes YoY by week. However I want to ensure the days of week are aligned (i.e. Sun of week x in 2017 = Sun of same week in 2016). I want the values on the X-axis to be week ending Saturday for the current year. The formula for week ending Saturday which I'm using is: =A1+(7-WEEKDAY(A1))
For example: lets look in 2017 at week ending sat of 2/4/17 (1/29-2/4). For 2016, the week which aligns has a week ending of 2/6/16 (1/31-2/6). And for 2015 the corresponding week has a week ending sat of 2/7/15 (2/1-2/7).
How do I align the 2016 and 2015 weeks to match the days of week with the current year (2017)? I need "adjust" 2016 by 2 days and 2017 by 1 day so all of the Sundays-to-Saturdays are aligned, so I'm comparing apples to apples.
Is there a formulaic way of doing this? Or do I have to maintain a formula to manually adjust days based on a given year?
Thanks in advance for your thoughts. Dan
For example: lets look in 2017 at week ending sat of 2/4/17 (1/29-2/4). For 2016, the week which aligns has a week ending of 2/6/16 (1/31-2/6). And for 2015 the corresponding week has a week ending sat of 2/7/15 (2/1-2/7).
How do I align the 2016 and 2015 weeks to match the days of week with the current year (2017)? I need "adjust" 2016 by 2 days and 2017 by 1 day so all of the Sundays-to-Saturdays are aligned, so I'm comparing apples to apples.
Is there a formulaic way of doing this? Or do I have to maintain a formula to manually adjust days based on a given year?
Thanks in advance for your thoughts. Dan
