Comments on: Calculating week number in Excel (WEEKNUM function)

In this short tutorial, we will briefly talk about the syntax and arguments of Excel WEEKNUM, and then discuss a few formula examples demonstrating how you can use the WEEKNUM function to calculate week numbers in your Excel worksheets. Continue reading →

Comments page 2. Total comments: 80

  1. I was wondering. I am not very computer / excel savvy.
    I need to work out the week number from a specific date.
    eg If someone has surgery on 19/8/2020 and it always based on 'todays date' what is the week number.
    e.g date of surgery: 19/8/2020. today's date: 14/10/2020 ... this is 8 weeks post surgery... I need to do this in my excel spreadsheet.... thanks in advance

  2. Hi,

    I sincerely appreciate any help that can be offered to help me solve this problem:

    I'm dealing with reports that provide only week numbers and years - I believe the week numbering system is the same as Excel's WEEKNUM type 21 (ISO, Mon-Sun). I used the formulas in the article ...

    Start Date
    =DATE([Year], 1, -2) - WEEKDAY(DATE([Year], 1, 3)) + ([Week Num] * 7)

    End Date
    =DATE([Year], 1, -2) - WEEKDAY(DATE([Year], 1, 3)) + ([Week Num] * 7) + 6

    ... to try to calculate the start and end date for each week, but I'm seeing obviously wrong dates being calculated at the end/beginning of each year -- the week start/end dates are wrong for 1/1/2021 through 1/3/2021.

    Date Year Wk Nm Wk Start Date Wk End Date
    12/21/2020 2020 52 12/21/2020 12/27/2020
    12/22/2020 2020 52 12/21/2020 12/27/2020
    12/23/2020 2020 52 12/21/2020 12/27/2020
    12/24/2020 2020 52 12/21/2020 12/27/2020
    12/25/2020 2020 52 12/21/2020 12/27/2020
    12/26/2020 2020 52 12/21/2020 12/27/2020
    12/27/2020 2020 52 12/21/2020 12/27/2020
    12/28/2020 2020 53 12/28/2020 1/3/2021
    12/29/2020 2020 53 12/28/2020 1/3/2021
    12/30/2020 2020 53 12/28/2020 1/3/2021
    12/31/2020 2020 53 12/28/2020 1/3/2021
    1/1/2021 2021 53 1/3/2022 1/9/2022
    1/2/2021 2021 53 1/3/2022 1/9/2022
    1/3/2021 2021 53 1/3/2022 1/9/2022
    1/4/2021 2021 1 1/4/2021 1/10/2021
    1/5/2021 2021 1 1/4/2021 1/10/2021
    1/6/2021 2021 1 1/4/2021 1/10/2021
    1/7/2021 2021 1 1/4/2021 1/10/2021
    1/8/2021 2021 1 1/4/2021 1/10/2021
    1/9/2021 2021 1 1/4/2021 1/10/2021
    1/10/2021 2021 1 1/4/2021 1/10/2021

    Is there an improved version of the formula that will correctly calculate the week start/end dates for between year transitions?

    Thank you.

    Respectfully,
    Mark

    1. Hello!
      The WEEKNUM function can use 2 counting systems for the first week of the year. See the syntax for this function in more detail and choose the system that works best for you.
      I hope this will help, otherwise please do not hesitate to contact me anytime.

    2. I just realized my mistake -- I'm deriving the year from the date, rather than from the weekly period, which would be 2020-53, not 2021-53, for 1/1/2021 through 1/3/2021.

  3. Hello, can some please help me write a formula for below:
    Input = Year+Month+Week(1 to 6)
    Output = Start date of the week (Sun-Sat) & End date of the week (Sun-Sat)
    e.g [2020 July Week2] = [Sun, 05-Jul-2020] & [Sat, 11-Jul-2020]

    Many thanks in advance!

    1. Hello!
      If I understand your task correctly, the following formula should work for you:

      We calculate the date by the number of the month and the number of the week in this month -

      =CONCATENATE("[",TEXT(DATE(A12,C12,7*(B12))-WEEKDAY(DATE(A12,C12,1),1)-5,"ddd, dd-mmm-yyyy"),"] & [",TEXT(DATE(A12,C12,7*(B12))-WEEKDAY(DATE(A12,C12,1),1)-5+6,"ddd, dd-mmm-yyyy"),"]")

      A12 - year, C12 - month, B12 - week

      Hope this is what you need.

      1. Wow, this is Perfect!! I can't thank you enough sir!!!

  4. Hi, my financial week starts from 1st Sunday of February month each year. In this case, If I select Jul-21-2020, I want to receive 25 while using WEEKNUM function. Is it possible to achieve it? Can you advise?

    1. Hello!
      Please use the formula below:

      =IF(WEEKNUM(D1,1)>5,WEEKNUM(D1,1)-5,WEEKNUM(D1,1)+48)

      Hope this is what you need.

  5. Hi, I am having an issue and I do not understand why this is happening.
    I am using Excel 2013

    My year begins on a Sunday, December 29th (cell B1) and ends on Saturday, January 4th (cell B4)
    Since the formula WEEKNUM refers to the date in cell B1, and is a Sunday, I omitted the type since the week begins on a Sunday, per Excel reference sheet: -> System 1: The week containing January 1 is the first week of the year, and is numbered week 1.

    But whatever I do, it always returns as week number 53 instead of 1, as it should be.

    The second issue is that the second week begins on Sunday, January 5th (cell J1) and ends on Saturday, January 11th (cell P1). Since the formula still refers to the Sunday of the first day of this week (January 5th), It returns as week number 2.
    That would be correct week number is the first week wasn't considered as week #1.
    Because of this issue, Week 1 does not exist and causes problems with futur weeks and years.

    I would be very grateful if you could help me find a solution to my problem.
    Thank you very much and have a good day :)

    1. Hello Claudine!
      The ISO 8601 definition for week 01 is the week with the first Thursday of the Gregorian year (i.e. of January) in it. The first week of 2020 - from 30.12.2019 to 4.01.2020

  6. Can I get some help on calculating the week number & year from 2017-05-16? It is supposed to return a value of 20' 17. How can I get this done using formulas.. Pl help

    1. Hello,

      You can use a formula like this:

      =WEEKNUM(A1)&"' "&RIGHT(YEAR(A1),2)

      where cell A1 is “2017-05-16”

      Hope it will help you.

  7. How do I create a formula that returns the week of the month where the week of the month always starts on the 1st day or the month(no matter the day of the week) and ends on the last day. Following the 1st day of the month the week begins on Monday.
    ex. July 1 = week 1
    July 3 = week 1
    July 4 = week 2

    1. I'm also looking for this. It doesn't seem to have been answered yet

      1. Ha...figured it out right after posting this comment.

        If it hasn't already been pointed out, here's the formula I copy/pasted:

        =WEEKNUM(A1,2)-WEEKNUM(DATE(YEAR(A1),MONTH(A1),1),2)+1

        replace A1 with whichever cell you're working out of :)

  8. How do I keep the cell with the weeknum formula blank if no date is entered in the reference cell?

    1. Hi Sie,

      Embed your Weeknum formula into the IF function like this:

      =IF(A1="", "", WEEKNUM(A1))

      1. Thank you very much

  9. Hi Need Help for formula for calculating weeknum for starting day = sunday,
    but in year 2016, i want it to ignore Jan 1 and 2 (which is taking as by default week=#1).

    Expecting output for Jan 3 - 9 as week #1, Jan 10 to 16 = week #2 and so on.

    Thanks In Advance.

    1. Hi Abid,

      Here you go:
      =DATE(A7,1,1) - WEEKDAY(DATE(A7,1,1),1) + (B7)*7 + 1

      Please keep in mind this specific formula is only for year 2016 (as per your requirements), and it won't work correctly for other years.

      1. Hi Svetlana,

        thanks for quick response, could you please confirm what is the B7 here?

        or is it possible to send me the excel screenshot or the excel file you used for writing this formula? to my email id?
        And thanks in Advance again.

        REgards,
        Abid

        1. Abid,

          Oops, I seem to have copied the formula from row 7 by accident, sorry for this. If row 2 is the topmost row with data, then of course it should be A2 and B2:

          =DATE(A2,1,1) - WEEKDAY(DATE(A2,1,1),1) + (B2)*7 + 1

          1. Just to make myself clear this is output I would require

            30-10-2022 30-10-2022
            31-10-2022 07-11-2022
            01-11-2022 07-11-2022
            02-11-2022 07-11-2022
            03-11-2022 07-11-2022
            04-11-2022 07-11-2022
            05-11-2022 07-11-2022
            06-11-2022 07-11-2022
            07-11-2022 07-11-2022
            08-11-2022 14-11-2022
            09-11-2022 14-11-2022
            10-11-2022 14-11-2022
            11-11-2022 14-11-2022
            12-11-2022 14-11-2022
            13-11-2022 14-11-2022
            14-11-2022 14-11-2022
            .
            .
            .
            26-02-2023 27-02-2023
            27-02-2023 27-02-2023
            28-02-2023 06-03-2023
            .
            .
            06-03-2023 06-03-2023

            Regds ,Sunil

          2. Dear Svetlana,

            Your formula works perfectly fine until I required a change in the starting day from 06-11-2022 to be accounted on Monday 07-11-2022.

            I made change in your formula =DATE(A2,1,1) - WEEKDAY(DATE(A2,1,1),2) + (B2)*7 + 1 which gives the correct result as 07-11-2022 for the entry on 06-11-2022, but does not work for the entry on 07-11-2022 ,whose starting date goes to 14-11-2022, entries on 14-11-2022 get accounted on 21-11-2022. what am I doing wrong?

            28-10-2022 30-10-2022
            29-10-2022 30-10-2022
            30-10-2022 30-10-2022
            06-11-2022 07-11-2022
            07-11-2022 14-11-2022
            08-11-2022 14-11-2022
            09-11-2022 14-11-2022
            10-11-2022 14-11-2022
            11-11-2022 14-11-2022
            12-11-2022 14-11-2022
            13-11-2022 14-11-2022
            14-11-2022 21-11-2022
            15-11-2022 21-11-2022

            Also the leap month of this year 2023 creates havoc
            15-11-2022 21-11-2022
            25-02-2023 27-02-2023
            26-02-2023 27-02-2023
            27-02-2023 06-03-2023

            Kindly help.

            With regards
            Sunil

  10. It seems like the formula needs to be changed according to the year. The formula which works out for 2015 doesn't work for 2016

    1. Hello Deepak,

      You are probably talking about the formula that converts week number to date.

      Please note that it works based on the ISO week date system, where the week containing the 1st Thursday of the year is considered week 1. In 2016, the first Thursday is January 7, and that is why the week beginning on 4-Jan-2016 is considered week 1.

      I understand that it may be confusing and this approach may not work well for your date system. Therefore, we have added a few other formulas under Other ways to convert week number to date in Excel that treat the week containing the 1st of January as week 1.

  11. hi,

    how to display the current week (like week1, week2, week3 and so on) for a day in a month?

    1. bring the week number to a cell using formula A1=WEEKNUM(date) and concatenate that with the text "Week"(=CONCATENATE("Week",A1))

  12. One of the formulas above didn't work for me on dates in 2016.

    I was seeking a formulas that would tell me for today's date (or any date) what is the first day of the week, base on a Sunday - Saturday week.

    This worked for me in all years:

    =((DATE(YEAR(TODAY()), 1, -3) - WEEKDAY(DATE(YEAR(TODAY()), 1, 2)) + WEEKNUM(TODAY()) * 7)-1)

    If you want to start your week on a Monday then remove that -1 at the end of the formula.

    1. Thanks Mike - the formula for week end date wasn't working for me either, and your formula solved it.

  13. For Heidi, better late than never :)

    =IF(AND(MONTH(L2)=7,DAY(L2)=1),1,IF(WEEKDAY(L2,1)=1,M1+1,M1))

    1. M1 is what?

  14. Is it possible to use this function but have the week numbers start 1st July in the year? To match our financial year that we use here in Australia.

    1. Hi Heidi,

      I am not sure I can figure out a formula that would be 100% true. You'd probably better ask on the following Australian forum targeting Excel:
      http://www.ozgrid.com/forum/

Post a comment



Thanks for your comment! Please note that all comments are pre-moderated, and off-topic ones may be deleted.
For faster help, please keep your question clear and concise. While we can't guarantee a reply to every question, we'll do our best to respond :)