# Thread: Compare two week numbers from different years in Excel Thanks: 0 Likes: 0

1. ## Compare two week numbers from different years in Excel

I have some calculations that are strongly dependent on "this week", "next week", etc.
Let's say I have a date (Column A). I can use `WEEKNUM((A1), 2)` in Column B to get the week number.
In Column C, I can enter this formula to check if the event is this week or next week:

`=IF(B1=WEEKNUM((TODAY()),2),"this week",IF(B1=WEEKNUM((TODAY()+7),2),"next week","way in the future"))`This all works great until I have events that cross into next year. So December 31, 2019, will be week 53 and December 31, 2020, will be week 53. There's no way for me to calculate that December 31, 2020 happens after December 31, 2019.
Additionally, January 1, 2020 will reset the week to 1 and if today was December 31, 2019 (i checked by setting my computer time to December 31, 2019), the formula I use would report "way in the future" instead of "this week" or "next week".

2. ## Re: Compare two week numbers from different years in Excel

Welcome to the MrExcel board!

Is this something that you could use? Note that it does not use/require the week # value.

=LOOKUP(A2-WEEKDAY(A2,3)-(TODAY()-WEEKDAY(TODAY(),3)),{-99999,0,7,14},{"Before this week","This week","Next Week","Way in the future"})

3. ## Re: Compare two week numbers from different years in Excel

The following formula returns the desired results:

=LOOKUP(YEAR(A2)+B2/53-YEAR(TODAY())-WEEKNUM(TODAY(), 2)/53,{-99999,-0.02,-0.001,0.01,0.03},{"Way in the past","Last week","This week","Next Week","Way in the future"})

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•