Compare two week numbers from different years in Excel

[heathxp]

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 <code style="margin: 0px; padding: 1px 5px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; font-size: 13px; vertical-align: baseline; box-sizing: inherit; background-color: rgb(239, 240, 241); white-space: pre-wrap;">WEEKNUM((A1), 2)</code> 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:

<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; box-sizing: inherit; white-space: inherit;">=IF(B1=WEEKNUM((TODAY()),2),"this week",IF(B1=WEEKNUM((TODAY()+7),2),"next week","way in the future"))</code>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".

 

[Peter_SSs]

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"})

 

[Tetra201]

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"})