Calculating week number in Excel (WEEKNUM function)

While Microsoft Excel provides an array of functions to work with days, months and years, only one is available for weeks - the WEEKNUM function. So, if you are looking for a way to get a week number from a date, WEEKNUM is the function you want.

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.

Excel WEEKNUM function - syntax

The WEEKNUM function is used in Excel to return the week number of a specific date in the year (a number between 1 and 54). It has two arguments, the 1st is required and the 2nd is optional:

WEEKNUM(serial_number, [return_type])
  • Serial_number - any date within the week whose number you are trying to find. This can be a reference to a cell containing the date, a date entered by using the DATE function or returned by some other formula.
  • Return_type (optional) - a number that determines on which day the week begins. If omitted, the default type 1 is used (the week beginning on Sunday).

Here is a complete list of the return_type values supported in WEEKNUM formulas.

Return_type Week begins on
1 or 17 or omitted Sunday
2 or 11 Monday
12 Tuesday
13 Wednesday
14 Thursday
15 Friday
16 Saturday
21 Monday (used in System 2, please see the details below.)

In the WEEKNUM function, two different week numbering systems are used:

  • System 1. The week containing January 1 is considered the 1st week of the year and is numbered week 1. In this system, the week traditionally starts on Sunday.
  • System 2. This is the ISO week date system that is part of the ISO 8601 date and time standard. In this system, the week starts on Monday and the week containing the first Thursday of the year is considered week 1. It is commonly known as the European week numbering system and it is used mainly in government and business for fiscal years and timekeeping.

All of the return types listed above apply to System 1, except for return type 21 that is used in System 2.

Note. In Excel 2007 and earlier versions, only options 1 and 2 are available. Return types 11 through 21 are supported in Excel 2010 and Excel 2013 only.

Excel WEEKNUM formulas to convert date to week number (from 1 to 54)

The following screenshot demonstrates how you can get week numbers from dates with the simplest =WEEKNUM(A2) formula:

A WEEKNUM formula to convert date to week number

In the above formula, the return_type argument is omitted, which means that the default type 1 is used - the week beginning on Sunday.

If you'd rather begin with some other day of the week, say Monday, then use 2 in the second argument:

=WEEKNUM(A2, 2)

Instead of referring to a cell, you can specify the date directly in the formula by using the DATE(year, month, day) function, for example:

=WEEKNUM(DATE(2015,4,15), 2)

The above formula returns 16, which is the number of the week containing April 15, 2015, with a week beginning on Monday.

In real-life scenarios, the Excel WEEKNUM function is rarely used on its own. Most often you would use it in combination with other functions to perform various calculations based on the week number, as demonstrated in further examples.

How to convert week number to date in Excel

As you have just seen, it's no big deal to turn a date into a week number using the Excel WEEKNUM function. But what if you are looking for the opposite, i.e. converting a week number to a date? Alas, there is no Excel function that could do this straight away. So, we will have to construct our own formulas.

Supposing you have a year in cell A2 and a week number in B2, and now you want to calculate the Start and End dates in this week.

Converting a week number to a date in Excel

Note. This formula example is based on ISO week numbers, with a week starting on Monday.

The formula to return the Start date of the week is as follows:

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

Where A2 is the year and B2 is the week number.

Please note that the formula returns the date as a serial number, and to have it displayed as a date, you need to format the cell accordingly. You can find the detailed instructions in Changing date format in Excel. And here is the result returned by the formula:

Excel formula to convert a week number to a date

Of course, the formula to convert a week number to a date is not trivial, and it may take a while to get your head round the logic. Anyway, I will do my best to provide meaningful explanation for those who are curious to get down to the bottom.

As you see, our formula consists of 2 parts:

  • DATE(A2, 1, -2) - WEEKDAY(DATE(A2, 1, 3)) - calculates the date of the last Monday in the previous year.
  • B2 * 7 - adds the number of weeks multiplied by 7 (the number of days in a week) to get the Monday (start date) of the week in question.

In the ISO week numbering system, week 1 is the week containing the first Thursday of the year. Consequently, the first Monday is always between December 29 and January 4. So, to find that date, we have to find the Monday immediately before January 5.

In Microsoft Excel, you can extract a day of week from a date by using the WEEKDAY function. And you can use the following generic formula to get Monday immediately before any given date:

=date - WEEKDAY(date - 2)

If our ultimate goal were to find Monday immediately before the 5th of January of the year in A2, we could use the following DATE(year, month, day) functions:

=DATE(A2,1,5) - WEEKDAY(DATE(A2,1,3))

But what we actually need is not the first Monday of this year, but rather the last Monday of the previous year. So, you have to subtract 7 days from January 5 and hence you get -2 in the first DATE function:

=DATE(A2,1,-2) - WEEKDAY(DATE(A2,1,3))

Compared to the tricky formula you have just learned, calculating the End date of the week is a piece of cake :) To get Sunday of the week in question, you simply add 6 days to the Start date, i.e. =D2+6
Calculating the end date of the week

Alternatively, you could add 6 directly in the formula:

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

To make sure the formulas always deliver the right dates, please have a look at the following screenshot. The Start Date and End Date formulas discussed above are copied across column D and E, respectively:
The start and end dates of the week based on the week number

Obviously, you can wrap the Start date formula in the Excel MONTH function to get a month corresponding to the week number.

=MONTH(DATE(A2, 1, -2) - WEEKDAY(DATE(A2, 1, 3)) + B2 * 7)
A formula to get a month corresponding to the week number

Note. Please remember that the above formula works based on the ISO week date system, where the week starts on Monday and the week containing the 1st Thursday of the year is considered week 1. For example, in the year 2016, the first Thursday is January 7, and that is why week 1 begins on 4-Jan-2016.

Other ways to convert week number to date in Excel

If the above formula based on the ISO week date system does not meet your requirements, try one of the following solutions.

Formula 1. A week containing Jan-1 is week 1, Mon-Sun week

As you remember, the previous formula works based on the ISO date system where the first Thursday of the year is considered week 1. If you work based on a date system where the week containing the 1st of January is considered week 1, use the following formulas:

Start date:
=DATE(A2,1,1) - WEEKDAY(DATE(A2,1,1),2) + (B2-1)*7 + 1

End date:
=DATE(A2,1,1)- WEEKDAY(DATE(A2,1,1),2) + B2*7

Converting a week number to date, where a week containing Jan-1 is considered week 1 (Mon-Sun week)

Formula 2. A week containing Jan-1 is week 1, Sun-Sat week

These formulas are similar to the above ones with the only difference that they are written for Sunday - Saturday week.

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

End date:
=DATE(A2,1,1)- WEEKDAY(DATE(A2,1,1),1) + B2*7
Converting a week number to date, where a week containing Jan-1 is considered week 1 (Sun-Sat week)

Formula 3. Always start counting on January 1, Mon-Sun week

While the previous formulas return Monday (or Sunday) of week 1, regardless of whether if falls within this year or the previous year, this start date formula always returns January 1 as the start date of week 1 regardless of the day of the week. By analogy, the end date formula always returns December 31 as the end date of the last week in the year, regardless of the day of the week. In all other respects, these formulas work similarly to Formula 1 above.

Start date:
=MAX(DATE(A2,1,1), DATE(A2,1,1) - WEEKDAY(DATE(A2,1,1),2) + (B2-1)*7 + 1)

End date:
=MIN(DATE(A2+1,1,0), DATE(A2,1,1) - WEEKDAY(DATE(A2,1,1),2) + B2*7)

An alternative way to convert a week number to a date in Excel for Mon-Sun week

Formula 4. Always start counting on January 1, Sun-Sat week

To calculate the start and end dates for a Sunday - Saturday week, all it takes is one small adjustment in the above formulas :)

Start date:
=MAX(DATE(A2,1,1), DATE(A2,1,1) - WEEKDAY(DATE(A2,1,1),1) + (B2-1)*7 + 1)

End date:
=MIN(DATE(A2+1,1,0), DATE(A2,1,1) - WEEKDAY(DATE(A2,1,1),1) + B2*7)

An alternative way to convert a week number to a date in Excel

How to get a week number in a month (from 1 to 6)

If your business logic requires converting a specific date to the week number within the corresponding month, you can use the combination of WEEKNUM, DATE and MONTH functions:

Assuming that cell A2 contains the original date, use the following formula for a week beginning on Monday (notice 21 in WEEKNUM's return_type argument):

=WEEKNUM($A2,21)-WEEKNUM(DATE(YEAR($A2), MONTH($A2),1),21)+1

For a week beginning on Sunday, omit the return_type argument:

=WEEKNUM($A2)-WEEKNUM(DATE(YEAR($A2), MONTH($A2),1))+1
Getting  a week number in a month

How to sum values and find average by the week number

Now that you know how to convert a date to a week number in Excel, let's see how you can use week numbers in other calculations.

Suppose, you have some monthly sales figures and you want to know the total for each week.

To begin with, let's find out a week number corresponding to each sale. If your dates are in column A and sales in column B, copy the =WEEKNUM(A2) formula across column C beginning in cell C2.
A WEEKNUM formula to get a week number from a date

And then, make a list of week numbers in some other column (say, in column E) and calculate the sales for each week using the following SUMIF formula:

=SUMIF($C$2:$C$15, $E2, $B$2:$B$15)

Where E2 is the week number.

In this example, we are working with a list of March sales, so we have week numbers 10 to 14, as demonstrated in the following screenshot:
The SUMIF formula to calculate sales based on the week number

In a similar manner, you can calculate the sales average for a given week:

=AVERAGEIF($C$2:$C$15, $E2, $B$2:$B$15)
The AVERAGEIF formula to calculate the sales average for each week

If the helper column with the WEEKNUM formula does not fit well into your data layout, I regret to tell you that there is no simple way to get rid of it because Excel WEEKNUM is one of those functions that doesn't accept range arguments. Therefore, it cannot be used within SUMPRODUCT or any other array formula like the MONTH function in a similar scenario.

How to highlight cells based on the week number

Let's say you have a long list of dates in some column and you want to highlight only those that relate to a given week. All you need is a conditional formatting rule with a WEEKNUM formula similar to this:

=WEEKNUM($A2)=10

As demonstrated in the screenshot below, the rule highlights sales that were made within week 10, which is the first week in March 2015. Since the rule applies to A2:B15, it highlights values in both columns. You can learn more about creating conditional formatting rules in this tutorial: Excel conditional formatting based on another cell value.
Highlighting cells based on the week number

This is how you can calculate week numbers in Excel, convert week number to date and extract week number from date. Hopefully, the WEEKNUM formulas you have learned today will prove useful in your worksheets. In the next tutorial, we will talk about calculating age and years in Excel. I thank you for reading and hope to see you next week!

You may also be interested in:

53 Responses to "Calculating week number in Excel (WEEKNUM function)"

  1. Lucas says:

    Great article! Thank you very much.

  2. Heidi says:

    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.

  3. Miles says:

    For Heidi, better late than never :)

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

  4. Mike says:

    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.

  5. Sridhar says:

    hi,

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

    • Deepak says:

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

  6. Deepak says:

    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

  7. Emmanuel says:

    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?

  8. Abid says:

    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.

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

      • Abid says:

        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

        • 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

  9. Rafael Dornellas says:

    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.

  10. Sie says:

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

  11. Sie says:

    Thanks Svetlana. It works. You made my day.

  12. Seif says:

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

  13. Paul says:

    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

    • William Buckley says:

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

      • William Buckley says:

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

  14. Mark says:

    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

  15. rutvik says:

    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

  16. rutvik says:

    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

  17. Bill Black says:

    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

  18. Daniel says:

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

  19. Terri says:

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

  20. Deba says:

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

  21. Vicki says:

    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?

  22. Sunder says:

    Hi,

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

  23. Suren says:

    Hi,

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

  24. Ruban says:

    Is it possible to get weekday name to week day number

    For Example
    Sunday as 1
    Monday as 2
    etc

  25. Rob H says:

    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

  26. VAGEESH KANTHAPPA says:

    Solved my purpose...thank you

  27. Koolman says:

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

  28. Enrique says:

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

  29. Mihai says:

    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

  30. mouuztov says:

    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

  31. girish says:

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

  32. Toyin Ajayi says:

    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

  33. Cham says:

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

  34. Excelnoob123 says:

    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!

  35. Sugi says:

    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

  36. Bambi says:

    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?

  37. Priyank Mavani says:

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

  38. syed says:

    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

  39. vettri says:

    Very good ideas and much helpful us.

  40. Wilson,Mangaluru says:

    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.

  41. Thanu J says:

    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,

Post a comment



Unfortunately, due to the volume of comments received we cannot guarantee that we will be able to give you a timely response. When posting a question, please be very clear and concise. We thank you for understanding!
Excel add-ins and Outlook tools - Ablebits.com
33
Ultimate Suite 2018.5 for Excel
33
60+ tools for Excel
December offer: Dec. 8 – Dec. 17