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: 127

  1. Thank you. I was looking for this.
    I use Excel 2010 so I had to convert all the ; and also it's a Danish version So I had to change the Max to Maks, Date to Dato, and weekday to Ugedag. Then it worked brilliantly ☺

    1. I made a typo ☺.
      I had to change all the , commas to ; semicolon for it work in Excel 2010

  2. Is there a way to use =WEEKNUM(TODAY()), but have it display in the cell with additional text before and after, i want the text to stay the same, but i want the Week Number to auto update each time someone opens the spreadsheet

    Week 47 Thu

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

  4. Rather is "WC 12 Oct'20" in this format

  5. Hi can someone help with to set week commencing in below format

    like if the week is start 12 Oct 2020 then I would like to see it as WC 12 Oct 2020

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

  7. This is an amazing post. Saved many hours of my life! Sincerely appreciate the hard work you have put in and your willingness to share.
    Best regards,
    Junaid.

  8. Can anyone explains to me the logic behind the formula:
    date - WEEKDAY(date - 2)?
    It is doing my head in.

  9. 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!!!

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

  11. Hi,I want to do a week num formula like this, but want to avoid non-working days in my formula.. any ideas on how I can do this?

    Many thanks

    1. I’m sorry but your task is not entirely clear to me. For me to be able to help you better, please describe your task in more detail. Explain - "formula like this" - what formula are you talking about? There are a lot of them in the article. Please specify what you were trying to find, what formula you used and what problem or error occurred. Give an example of the source data and the expected result.
      It’ll help me understand it better and find a solution for you. Thank you.

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

  13. Hi,
    Can someone please help to get the week number if the week starts on January 2, 2020? This is because our week starts on the first Thursday of the year.
    So week 1 is Jan 2 - Jan 8, week 2 is 9 to 15, and so on. The last week would be Dec 31 to Jan 6, 2021.
    Thanks so much

  14. Hello,
    Does someone know how to convert date-time format "20181101 Kl: 145732"
    into "2018W44" Or "1844"
    Thank you in advance for your help!
    BR
    Morgan

  15. Hello, I need a formula to sort out the weeks in sequent way .. meaning after week 1 I get week 10 and 11 etc.. how can I get week 1, then week 2
    Thank you in advance for your help

  16. Hello,
    I need a excel formula to do a weekly planning and should change the weeks automatically if it exceeds week 52 to week 1 ( if the plan ends somewhere next year) .
    Also the weekly planning will be linked to the date at the beginning as a reference .
    Example:-
    The planning start date is 07/03/2019 later it will become to =weeknum and the plan buildup in weeks to chase / followup weekly.
    Thank you in advance for your help.

  17. I need excel formula to get one fixed string after every 3 weeks in calendar

  18. I have data set with First date as 6-Sept-2018.
    I need to have 6 Sept to 12th Sept as Week 1, 13th Sept to 19th Sept as Week 2 and so on..How should I write the formula?

    Thanks,

  19. Dear Mam,
    many thanks for useful info.

    I have a start date : Wednesday,18-July-2018
    How can i write a formula to start week as Wednesday to Tuesday (The week start day will be whatever the start day) and the dates to form Week-1,Day-1, on first date preceded by
    Week-1,Day-2
    ....
    ....
    ....
    ....
    Week-1,Day-7,
    Week-2,Day-1 as so on...

    Thanks in advance.

  20. Very good ideas and much helpful us.

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

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

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

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

  25. 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!

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

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

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

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

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

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

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

  33. Solved my purpose...thank you

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

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

    For Example
    Sunday as 1
    Monday as 2
    etc

  36. Hi,

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

  37. Hi,

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

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

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

  40. 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!!

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

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

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

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

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

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

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

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

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

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

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