Results 1 to 3 of 3

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

  1. #1
    New Member
    Join Date
    Sep 2019
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #2
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    40,976
    Post Thanks / Like
    Mentioned
    90 Post(s)
    Tagged
    21 Thread(s)

    Default 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"})
    Last edited by Peter_SSs; Sep 18th, 2019 at 03:43 AM.
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  3. #3
    MrExcel MVP Tetra201's Avatar
    Join Date
    Oct 2016
    Posts
    3,386
    Post Thanks / Like
    Mentioned
    15 Post(s)
    Tagged
    1 Thread(s)

    Default 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"})
    Last edited by Tetra201; Sep 18th, 2019 at 02:51 AM.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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