3 Tips for Calculating Week Numbers from Dates in Excel - Excel Campus
74

3 Tips for Calculating Week Numbers from Dates in Excel

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.

=WEEKNUM("3/15/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

=WEEKNUM("A2")
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.

=(B2-A2)/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! 🙂

Please share
Jon Acampora
 

Welcome to Excel Campus! I am excited you are here. My name is Jon and my goal is to help you learn Excel to save time with your job and advance in your career. I've been an avid Excel user and VBA developer for 10+ years. I am also a Microsoft MVP. I try to learn something new everyday, and want to share this knowledge with you to help you improve your skills. When I'm not looking at spreadsheets, I get outdoors and surf. :) more about me

Click Here to Leave a Comment Below 74 comments
Sandeep - November 4, 2017

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.

Reply
Steve Walters - September 15, 2017

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

Sheet2…
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).

Reply
Brenda - September 1, 2017

=FORMULATEXT(A2) – VERY COOL AND GOING TO BE VERY HANDY. THANKS!

Reply
Jam - April 3, 2017

Congrats…

Reply
Catalina Acosta - March 21, 2017

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

Reply
Peter Buyze - March 19, 2017

Jon, congrats with the baby, he looks just like his father !!
I posted about this article on G+ here https://plus.google.com/+PeterBuyze/posts/1VCyAUeTJor

Reply
Gerard - March 18, 2017

Congratulations and best wishes to the entire family !

Gerard

Reply
sergio durval da silva - March 17, 2017

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.

Reply
Bill - March 17, 2017

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.

Reply
Nayan Khandelwal - March 17, 2017

Thank you Acampora Sir….
very simple command yet very useful…….!!

Reply
Chitrasen - March 17, 2017

Congratulations Mr. Jon..

Reply
Seamonk - March 16, 2017

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.

Reply
Paul Whatley - March 16, 2017

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

Reply
Crispo Mwangi - March 16, 2017

Lovely Baby

Nice Article

Keep it up!

Reply
Salim - March 16, 2017

Congratulations Jon!

Reply
Salum - March 16, 2017

Congratulations Jon!

Reply
Greg - March 16, 2017

Congratulations on the baby, he’s supercute. 🙂

Reply
MansoorAli - March 15, 2017

Congratulations Jon!

Best wishes

Reply
Khaled Naser - March 15, 2017

May your son fills your life with joy and love!

Reply
Saud - March 15, 2017

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

Reply
Hamed Parada Velez - March 15, 2017

Congratulations! my best wishes to you and family!!

Reply
Reinalda - March 15, 2017

Congratulations from Sydney. Praying your little boy will be blessed with health & wisdom!

Reply
Bev - March 15, 2017

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.

Reply
Ugin - March 15, 2017

Congrats Jon, Exciting times ahead.

Reply
Akintola - March 15, 2017

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.

Reply
Roger K Ray - March 15, 2017

Congratulations on your new baby!!

Reply
Moataz - March 15, 2017

Congratulations Jon and Thank You very Much fir your tips

Reply
Super Joke - March 15, 2017

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

Reply
Zulaikha - March 15, 2017

Congratulations Jon!

Best wishes 🙂

Reply
Sabine - March 15, 2017

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

Reply
Rajashekar Aedelly - March 15, 2017

Congratulations Jon! Enjoy the beautiful moments!! God bless your family as always!!!

Reply
MF - March 15, 2017

Congratulations!

Reply
Agnes - March 15, 2017

Congratulations on your baby. So happy for you!

Reply
Carrie - March 15, 2017

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

Reply
    Jon Acampora - March 15, 2017

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

    Reply
      Carrie - March 15, 2017

      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!

      Reply
        Jon Acampora - March 16, 2017

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

        Reply
          Carrie - March 16, 2017

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

          Reply
INDZARA - March 15, 2017

Congratulations, Jon. A completely new and wonderful life awaits you. Enjoy every minute. Best wishes.

Reply
Victoria - March 15, 2017

And thank you for your wonderful Excel tips and training courses.

Reply
Victoria - March 15, 2017

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

Reply
David - March 15, 2017

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.

Reply
    Jon Acampora - March 15, 2017

    Thanks David! We are trying to enjoy every minute of it. I hear that so often too. Happy to hear you enjoyed the article. Have a good one! 🙂

    Reply
Xiao Li - March 15, 2017

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 !

Reply
Jim Oschwald - March 15, 2017

Congratulations on the birth of your baby boy!

Reply
Anjan Barua - March 15, 2017

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.

Reply
Nate O - March 15, 2017

Congrats on the little one Jon! I hope he got an EXCELlent name!

Reply
Juan - March 15, 2017

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

Reply
Adam - March 15, 2017

Congratulations on your son

Reply
Endalamaw - March 15, 2017

I am really exiting with your great announcement. God bless your son. children are spices of life.

Reply

Leave a Reply: