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 3. Total comments: 127

  1. Hi,

    i have dates from 2014 to 2017. I want to highlight dates using conditional formatting based on numbers of weeks i.e. all dates (from 2014-2017) falling between week 1 - 5 highlight in "green", week 6-12 in "yellow" and week 13 - 26 in "orange" and remaining with no colors. Could anyone please advise formula (s) to do this. Thanks

  2. =DATE(A2,1,-2) << I just checked this, it receives the last tuesday of the previous year, and not the last monday.

  3. I'm trying to calculate the previous 15 weeks from a current date. Typically, I use ="Week "&ISOWEEKNUM('GM 2018-19'!$A$2)-1, ="Week "&ISOWEEKNUM('GM 2018-19'!$A$2)-2, etc...
    However, when I get to the previous year I can't figure out how to return Week# 52 and prior. I've tried several different formulas but always end up with Week -1 and so on. Any ideas?

  4. 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.

  5. Hello,

    I've done the weeknum. However, may I know how do I make the column update automatically when the date updates? Because keep having to drag it down to update is a hassle. Thank you!

  6. Great article. The screenshots with detailed captions makes it awesome and super easy to read/understand. Thanks for sharing your wisdom.

  7. Hi Svetlana,

    Can I get some help on calculating the week number from 08/04/2016. It is supposed to return a value of (week) 14 but all the calculations I have tried keep returning a value of (week) 15. Can you help please

  8. hi i want formula that week start from april-16 and end to march-17 1-52 count

  9. hai, how about to knows date on based

    weeknum and day. example

    weeknum= 2 and day is Sunday for every month. How to convert to date ?

    thanks, sorry for bad english

  10. Hello
    I need a formula that will show in a single cell week and year and I got that:
    = WEEKNUM('Rap.gen.an curent'!E7)&" "&YEAR('Rap.gen.an curent'!E7)
    This will appear as 2 2017.Now,on the next cell of the row I need another one to show me 3 2017 and so on but when will reach the end of the year to appear as 1 2018

  11. Solved my life - Thank you very much. Muchas gracias.

  12. I was seeking an excel formula that would tell me the Friday date of the week for random dates

  13. Solved my purpose...thank you

  14. Hi all,
    Is there a simple formula to display the Sunday date two weeks prior to today's date?
    So if today is the 6th Jan i would want the formula to show the date of the 26th December. So no matter what day the sheet is on this week it will always show that Sunday until the date changes to the first date of the next week. This make sense?

    I was thinking =today()-14. however this would not show the Sundays date unless it was exactly 14 days prior.

    Any help would be welcomed.

    Thanks
    Rob

  15. Is it possible to get weekday name to week day number

    For Example
    Sunday as 1
    Monday as 2
    etc

  16. Hi,

    How to the week number and year?
    Example : 8/11/2016 to CW45'16.
    Tq

  17. Hi,

    Was looking on how to calculate the weeknum based on Nov'16 to Oct'17 as the financial year, need help? thanks

  18. I'm trying to use the WEEKNUM() function on a defined name and having some difficulty. My defined name plannedDate refers to a cell range containing only date formatted cells. I want WEEKNUM(plannedDate) to return the week number, but I am getting some sort of error. For reference, MONTH(plannedDate) returns the correct value between 1 and 12.

    Any suggestions?

  19. Hi,Iam Deba. I wnat to know, what is the formula to know
    date of birth..('Date of Birth'=?)

  20. Daniel, you are not alone. I am looking for the same thing!! I want a single cell to show Monday-Sunday as ##/## to ##/##. I already have a weekly project schedule created so I know what needs designed for which event. I don't want to mess the whole thing up by adding new columns and messing the whole thing up!!

  21. Once again I'm the ONLY human on the ENTIRE PLANET who wants to have a cell with a Mon-Sun in date format in ONE CELL!!!!!!

    Like: 8/1-8/7

    It's so awesome blazing the trail for humanity! No, it's really not because I have NO IDEA how to do this!!!!

    Why oh why am in this computer hell paradigm??????

  22. I found what I think is the easiest way to convert a date to its preceding Monday (first day of week). Its also easily adjustable to find preceding Sunday.

    =A1-WEEKDAY(A1,3)

    WEEKDAY(cell,3) returns number from 0-6 for days Monday-Sunday, simply subtracting that from a given date will result in the preceding Monday.

    You get the last day of week with same formula adding 6.
    =A1-WEEKDAY(A1,3)+6

  23. If i enter 1) Lead time - 4 weeks & 2) Item in store (10th oct 2016)

    then PO to be make field ( will have autom. date before 4 week date of item in store i.e 1st sept 2016)

    if any one can help in Excell. appreciated

  24. I need help on one logic to create one function for the date.

    I) Lead time Ordering date II) item in store
    4 (weeks date gen.auto(sept 1st 2016) With 10-01-2016
    If i enter I & II

    i required to creat in excell

  25. Hi

    I need to a formula to calculate the date of the beginning of the week starting Monday. So if I look at 7/7/2016 I want the formula to produce the date of Monday in the same week which would be 4/7/2016.

    Cheers,
    Mark

  26. 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 :)

  27. Hi.. I need formula that can display week number with month in excel chart, please assist

  28. Thanks Svetlana. It works. You made my day.

  29. 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

  30. Hello Svetlana!

    I have a similar question of Abid's.
    I hava a column A:A with dates from 01/01/2015 until 31/12/2020, and the column B:B will have the week numbers, considering the topmost row with data: 2.
    I want to know the week number considering the switch of years.

    For example: if I have dates from 28/12/2015 to 31/12/2015 as week 53, I have to consider dates 01/01/2016 until 03/01/2016 as week 53 as well to complete a full week (7 days). So, week number 1 for the year of 2016 will only start on 04/01/2016, and so on.

    Could you please help me out, answering through my e-mail or through this page?
    Thanks in advance!

    Regards,
    Rafael Dornellas.

  31. 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

  32. Hello:

    In your example above (Converting weekday to Date, the formula returned the start date and End Date of the specified week number and year

    How do i convert weekday(Monday, Tuesday,Wednesday...) to date of the year e.g lets say i have week number 2 in one cell and Year 2016 in another cell.

    How can i find the date of Saturday?

  33. 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.

  34. 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))

  35. 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.

  36. 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?

  37. 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/

  38. Great article! Thank you very much.

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)