Bottom line: Learn a few tips and techniques for calculating week numbers from dates, the number of weeks between dates, and how to display the week number in cells for reporting.

Skill level: Beginner

3 Tips for Calculating Week Numbers in Excel - Excel Campus - 640x360

As I mentioned in this week's newsletter, our baby boy was born last week.  Everything went well with the delivery and we are so happy to have our new little 8-pound bundle of joy in our lives.

Baby Acampora 2017

During my wife's pregnancy, everything was measured in weeks.  There are 40 weeks in a full term pregnancy, we seemed to have a doctor's appointment every other week, and we were definitely counting down the weeks until his estimated arrival date.

Of course, I kept track of most of these schedules and calculations in Excel.  So, I wanted to share some of the formulas and calculations I used to calculate week numbers.  These tips will also be useful for financial models, data analysis, and summary reports.  Babies aren't the only things measured in weeks… 🙂

Download the Example File

Download the example Excel file to follow along with the article.

Tips For Calculating Week Numbers In Excel.xlsx (19.6 KB)

#1 – WEEKNUM Function: Calculate the Week Number of the Year

We can use the WEEKNUM function to quickly determine how many weeks have elapsed from January 1st  to a given date in the same year.  The following formula returns 11 because 11 weeks have elapsed since January 1, 2017.

Result: 11

We can also reference a cell that contains a date.  If the date 3/15/2017 was in cell A2, then the following formula would return the same result.

WEEKNUM Function Calculates Number of Weeks Since Jan 1st

Result: 11

The WEEKNUM function has an optional argument that allows us to specify the day of the week to start on.  By default, WEEKNUM starts the week on Sunday.  We can change that by adding any of the following numbers to the second argument (return_type).  The image below shows the numbers and days for the return_type argument.

WEEKNUM return_type Argument to Specify Start Day of Week

It's important to note that the function only calculates the number of weeks from the specified date in the same year.  If we used “3/15/2016” in the WEEKNUM function, the result would be the number of weeks from January 1st, 2016, which is 12.  The week starts on Sunday, so the same date might have a different week number result from year to year.

One drawback of the WEEKNUM function is that we are stuck with January 1st (system 1) or the week containing the first Thursday of the year (system 2).  It would be great if there was a 3rd argument that allowed us to specify the day of the year to start on.  This would be helpful for fiscal year calendars.  Unfortunately, we don't have that feature yet.

Check out my article on how the calendar date system works in Excel for more details on how dates are stored as numbers.

#2 – Calculate the Number of Weeks Between Dates

To determine how many weeks elapsed between two dates, we can use a simple formula to find the number of days between the dates, then divide by 7.


The formula will return a decimal number. We can change the number format to a Fraction to display the number of days.

Change Number Format for Week Number WEEKNUM to Fraction

Or, we can use one of the ROUND functions to ROUND UP or ROUND DOWN to the nearest whole number.

Round functions to round to nearest week number in Excel

This formula can also be used to countdown the number of weeks until your next birthday, vacation, holiday, quarter-end, year-end, etc.  We can use the TODAY() function to return today's date in a cell.  The date will be updated with the current date every time we open or calculate the workbook.

TODAY Function to Return Current Date for Countdown in Week Numbers

#3 – Display the word “Week” in the Number Format

We might want to display the word “Week” before the week number in the cell.  There are a few ways to do this in Excel.  My preferred method is to use a custom number format to add the word “Week” before the week number.

Custom Number format to Display the word Week before the week number

The custom number format is:

"Week" #

The advantage of the custom number format is that the number remains a number, instead of text.  This makes it easier to sort by week number in filtered ranges and pivot tables.

Sorting Week Numbers with Custom Number Format

If the number is stored as text, it will not sort properly unless we use two digits for the week number.

Week Number stored as text does not sort properly

Therefore, I recommend using the custom number format to keep the data type of the cells as numbers.  This also helps keep the sort order when we create slicers on the Week Number field with the custom number format.

Custom Number Format keeps Sort Order in Pivot Tables and Slicers

I also have an article on 3 ways to display the weekday name of a date.

Other Methods for Calculating Week Numbers?

I hope you enjoyed those tips for working with week numbers in Excel.  These can be very useful for summary reports and week-over-week analysis.

Here are some related articles on working with dates in Excel.

What did I miss?  Do you have any additional tips for working with weeks?  If so, please leave a comment below with any suggestions or questions.

Well, I'm off to change a diaper and take a nap…  Thank you! 🙂


Your email address will not be published. Required fields are marked *

  • What formula do I use to show how many weeks have elapsed (passed) since the start date of a project?
    In other words, if I enter a start date of a project/task I want it to show me how many weeks are spent on that project/task at any given time. This must be updated every time I open the file.

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

  • Hope all is well with the family, many congratulations to you! I am looking to write the formula for Weeks 1-12 of a Fiscal Quarter that starts on Feb. 1.

    So dates anywhere within Feb 1 – 7 cell result is Week 1, Feb 8-14 result is Week 2, and it starts over at the beginning of the next fiscal Qtr. May 1-7 get Week 1.

    This formula seems to work for the first 6 days of the week, but switches to the next week on day 7:

    I am afraid I don’t know the adjustment needed to make it work.

    Thank you!

  • how can I get the exact # of weeks & days between two dates. I tried the =(M27-N27)/7 function and I am not getting the exactly # of days.

  • Hi,
    In excel my week is start on monday, I had stuck in some logic could you please help me to come out from it.
    i want to calculate week number as if month is ends before mid week then this date’s week is consider in next months 1 week and if month ends after mid week then next months dates are consider in first months last week …. so and forth other dates week number are calculated depend on this logic.

  • Does anyone know how to do the following please:

    I volunteer to coach Football to 9yr old boys. As it’s a nightmare to organise their availability via parents for Match Day / Training Days etc, I have created a Google Form for the Parents to simply type their child’s firstanme + surname + status for upcoming game.

    That aspect is working nicely and a weight off all our shoulders already. However, it would be sweet IF: The Google Sheet (almost identical to excel) could total/count/sum the number of kids/rows that status=confirmed since/as of last Sunday. So I would know whether I’ve enough kids for matchday at a glance each week (I plan to report & automate alerts from there too for other coaches who work shifts etc).

    So this is how the sheet is laid out:
    Col A (Timestamp) – this tells me when they filled in my form (I can see if it’s since our last game, therefore their declared Status is regarding the upcoming game/Saturday.
    Col B (Firstname)
    Col C (Surname)
    Col D (status)

    Col A (Total Available Kids for this Saturday).

    It’s fairly simple for someone who know how to Sum Rows/Cells =x since most recent Saturday. For me, I’m confused with where to start.

    Thanks in anticipation of your help!
    Steve (a stressed football / soccer coach).

  • Muchas Felicitaciones!!!!!

    Bendiciones para ti y tu familia, disfruta cada instante con tu hijo, el tiempo pasa volando y crecen rapidisimo asi que aprovecha para mimarlo y quererlo, luego ya no se dejan 😀 .

    Saludos a la distancia desde Quito-Ecuador

  • Jon I’m Brazilian, sorry if English is not very good I’m using Google Translator. Congratulations on your son and may God bless you, a hug.

  • Jon,
    Congratulations on your beautiful baby boy! May you not have too many sleepless nights.

    Thanks Jon, for all this great info. I like the way you explain things so that even I can understand. I appreciate all you do.

  • ISO Week 1 is actually a little more complicated since the weeks start on Monday, not Sunday. The first week is the one that contains a majority of the days of the week, starting with Monday, so if 1/1 is on Friday, Saturday or Sunday it belongs to the previous year. If you use return_type 21 in the weeknum formula you get the proper international week numbering. Wikipedia has a good article about week numbering schemes.

  • Congratulations Jon. I guarantee your life will never be the same. Still it brings up the Law of Diminishing Return – I wouldn’t take a million dollars for my kids, but I wouldn’t give you a nickel for another one. Put that in a spreadsheet. 🙂

  • Congratulations Jon,

    Great news, Happy to hear that one more geek is here to help future generations.
    God bless him with love and knowledge an everything in this world. Hope to see him online soon like father like son :).

  • Congratulations to you and your wife on the birth of your beautiful baby boy!!
    Cherish every moment as it flies quickly.. Many blessings to your family.

  • Congratulations Jon, may you be proud of your new baby as you watch him grow to be the future Excel geek in Jesus name. Amen.

  • Congratulations an my best wishes!..))))
    ★ ☆ ☼ ☺ ✾((((((((((((♥( ͡° ͜ʖ ͡° )♥))))))))))))✾ ☺ ☼ ☆ ★

  • Congratulations!!!! What a beautiful baby, enjoy, time flies and they are into puberty before you know 😉
    Best regards from the Netherlands, Sabine ZP

  • Congrats Jon on your new little alien (whose last name is considerably bigger than he is lol)! ^_~ It’s the eyes, I called my niece a little alien when she was born too. =P

    Thanks for the custom number format tip (week 1, etc). I would normally just concatenate that in, but your way is a lot easier – and better for sorting… never could figure out WHY the sorting wouldn’t behave.

    But I was under the impression that the return argument in the WEEKNUM() had something to do with some international system? It was weird and I gave up trying to figure it out… why would you start by the 1st Thursday of the year??

    Anyhoo, congrats again, and hopefully you will not have too many sleepless nights! =p

    • Thanks Carrie! Learning to pronounce and spell that last name will be a good challenge for him. I actually just bought some alien and rocket ship shirts for him. Too funny!

      In regards to the return_type for system 2 (first Thursday), this is the ISO or European week numbering system. The rule is that the week containing the first Thursday of the year is the first week of the year. Sorry, I did not explain that clearly in the post.

      I believe this calendar is used to prevent the first week from starting on a Friday or Saturday. In that case you would have a really short week 1 and the first day of the year would also contain the Holiday. So, depending on the business, you might be closed or have little activity for week 1.

      I hope that helps. Thanks again! 🙂

      • Alien shirts… that IS funny. =P

        Thanks for clarifying the system 2 return_type. I guess that kinda makes sense. I tried using that argument in this one workbook, just to see what would happen, and it made things worse lol.

        That particular workbook looks at each week in terms of the Monday … Even if the Monday happens to be the last day of the month, it would still considered week 5 of that month (like looking at a physical calendar). It was such a pain making that workbook… I was trying all sorts of date/week/month/day function combinations, but none of them would work out like that. I did get SOMETHING to finally work, and now I only need to change one date each month.

        Anyways, thanks again!

        • Hi Carrie,

          Those types of issues can be frustrating. Especially if your company uses a fiscal year calendar that does not start on Jan 1.

          I typically recommend using a Calendar Table for those type of custom calculations. A calendar table is just a table/range that has one row for each date in the year(s). It then has additional columns for the date groups like week numbers, quarters, etc.

          We can then use a lookup formula like vlookup to lookup the date in the table and return the right week number.

          The advantage here is that we can manually modify the calendar table for issues like a week with one day in it.

          I’m planning to write another post on the calendar table.

          Thanks again! 🙂

          • Huh, sounds interesting. Don’t think it would have helped me this case (but that’s mostly b/c I can’t quite picture it). I look forward to that post! =)

  • Congratulations on your new family member! You certainly look the proud dad. May your life and family be filled with joy, health and happiness.

  • Hey Jon – congratulations on the new baby! That’s very exciting! You’ll hear this most often, “they grow up so fast.” Just know that it’s so true!

    Also, this is a great article, and has some tips that will prove very useful.

  • Congratulations! you Have a baby now! wish you and your wife great time with your new family member! Also, thanks for your help on excel. You are nice person !

  • Dear John,

    Congratulation you both for your new role in life. The little princess is more than cute. God bless her and wish you all feel proud of her.

    I never miss your newsletters ever. Those are really great and helpful. Thanks for your effort.

  • Congratulations Jon for your beautiful baby and the joy of being a father for the first time, God bless your family always!!

Generic filters
Exact matches only
Filter by Custom Post Type


Learn 10 great Excel techniques that will wow your boss and make your co-workers say, "how did you do that??"
Plus weekly updates to help you learn Excel.

Download the eBook

About Me

Jon Acampora Profile

Hello and welcome! My name is Jon Acampora and I'm here to help you learn Excel.

This blog is updated frequently with Excel and VBA tutorials & tools to help improve your Excel skills and save time with your everyday tasks. Subscribe above to stay updated. More about me...