Compare two week numbers from different years in Excel

heathxp

New Member
Joined
Sep 17, 2019
Messages
1
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".
 

Some videos you may like

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,837
Office Version
  1. 365
Platform
  1. Windows
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"})
 
Last edited:

Tetra201

MrExcel MVP
Joined
Oct 14, 2016
Messages
3,598
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"})
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,114,002
Messages
5,545,439
Members
410,684
Latest member
LakTik
Top