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".
iQTsO2z.png
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
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:
Upvote 0
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:
Upvote 0

Forum statistics

Threads
1,213,526
Messages
6,114,122
Members
448,550
Latest member
CAT RG

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top