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

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.

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("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.

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.

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

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.

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

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.

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

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.

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.

- How Dates Work in Excel: The Calendar System Explained + Video
- Filter a Pivot Table or Slicer for the Most Recent Date or Period
- Grouping Dates in a Pivot Table VERSUS Grouping Dates in the Source Data
- How to Create Month to Date (MTD) Comparisons with a Pivot Table + Video
- 3 Ways to Get the Day Name for a Date

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! ðŸ™‚

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:

=1+(WEEKNUM(J2))-(WEEKNUM(DATE(YEAR(J2),LOOKUP(MONTH(J2),{2,5,8,11}),1)))

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.

how to find the week code of a day which is in DD/MM/YYYY format in excel.

E.g : 25/01/2018

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:

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

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

Congrats…

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

Thanks Peter! ðŸ™‚

Congratulations and best wishes to the entire family !

Gerard

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.

Thank you Acampora Sir….

very simple command yet very useful…….!!

Congratulations Mr. Jon..

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.

Thanks for the clear explanation Seamonk! I think that will make it easier for others to understand it. ðŸ™‚

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. ðŸ™‚

LOL! Too funny Paul! ðŸ™‚

Lovely Baby

Nice Article

Keep it up!

Thank you Crispo! ðŸ™‚

Congratulations Jon!

Congratulations Jon!

Congratulations on the baby, he’s supercute. ðŸ™‚

Thank you Greg! ðŸ™‚

Congratulations Jon!

Best wishes

May your son fills your life with joy and love!

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

Thank you Saud! ðŸ™‚

Congratulations! my best wishes to you and family!!

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

Thank you Reinalda! ðŸ™‚

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.

Thank you Bev! We are doing our best to enjoy our time with him. ðŸ™‚

Congrats Jon, Exciting times ahead.

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.

Thank you Akintola! ðŸ™‚

Congratulations on your new baby!!

Congratulations Jon and Thank You very Much fir your tips

Congratulations an my best wishes!..))))

â˜… â˜† â˜¼ â˜º âœ¾((((((((((((â™¥( Í¡Â° ÍœÊ– Í¡Â° )â™¥))))))))))))âœ¾ â˜º â˜¼ â˜† â˜…

Thank you! And cool symbol art! ðŸ™‚

Congratulations Jon!

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

Thank you Sabine! ðŸ™‚

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

Thank you Rajashekar! ðŸ™‚

Congratulations!

Thanks MF! ðŸ™‚

Congratulations on your baby. So happy for you!

Thank you Agnes! ðŸ™‚

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, Jon. A completely new and wonderful life awaits you. Enjoy every minute. Best wishes.

Thank you Indzara! ðŸ™‚

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

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

And thank you for your wonderful Excel tips and training.

Thank you Victoria! ðŸ™‚

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.

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! ðŸ™‚

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 !

Thank you Xiao! ðŸ™‚

Congratulations on the birth of your baby boy!

Thank you Jim! ðŸ™‚

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.

Thanks for your support Anjan! ðŸ™‚

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

Thanks Nate! ðŸ™‚

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

Thank you Juan! ðŸ™‚

Congratulations on your son

Thank you Adam! ðŸ™‚

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

Thanks Endalamaw!