Excel Formula: weekly comparison with corresponding week of previous month

0
votes

I'm trying to create a week on week comparison in Google Sheets.

The nuance is that the Comparison Week needs to dynamically populate as the corresponding week of the previous month.

So if the dataset is the first week of May, the Comparison Week would be the first week of April. Likewise, week 2 of May would be compared to week 2 of April, etc.

As an example, if A1 is 5/1/2016 and A2 is 5/7/2016, cells B1 and B2 should populate as 4/3/2016 and 4/9/2016 respectively (where Sunday is the first day of the week).

I've built the following formula which I think gets me about halfway:

=WEEKNUM(A1)-WEEKNUM(DATE(YEAR(A1),MONTH(A1),1),1)+1

This formula returns the week number in the month from a given date. For example, 5/1/2016 returns the value '1' because the date occurs on the first week of the month. 5/8/2016 would return '2', etc.

1
google-sheetsexcel-formula

1 Answers

0
votes

Use formula below. It looks ugly, but it should work exactly as your example.

As an example, if A1 is 5/1/2016 and A2 is 5/7/2016, cells B1 and B2 should populate as 4/3/2016 and 4/9/2016 respectively (where Sunday is the first day of the week).

=DATE(YEAR(A1),MONTH(A1)-1,DAY(A1)+IF(WEEKDAY(A1-DAY(A1)+1)=1,8,WEEKDAY(A1-DAY(A1)+1))-WEEKDAY(DATE(YEAR(A1),MONTH(A1)-1,1),1))

here is how it works

=DATE(
   YEAR(A1),
   MONTH(A1)-1,

return date of previous month with day calculated as follows:

DAY(A1)

start at the current date

+IF(WEEKDAY(A1-DAY(A1)+1)=1,8,WEEKDAY(A1-DAY(A1)+1))

find out what day is the first day of the current month and add its number. Only if it is Sunday (1st day of week) it needs to skip week 0 so it adds 8

-WEEKDAY(DATE(YEAR(A1),MONTH(A1)-1,1),1)

deduct number representing first day of previous month

So for example if you have 5/3/2016 it does: (3rd day of month) + (first day of current month in Sunday, so add 8) - (first day of previous month is Friday, so it deduct 6)

3 + 8 - 6 =5 and this is day of previous month, so result is 4/5/2016