Week numbers are a useful way to identify specific periods of the year. In this tutorial, you will learn how to calculate the week number of a given date within that month in Google Sheets. To do that, we will use a few of the DATE functions in Google Sheets.
They are WEEKNUM, TODAY, DATE, and EOMONTH. These are some basic date functions and you can find them in my tutorial titled How to Utilise Google Sheets Date Functions [Complete Guide].
Let me explain the topic with two examples:
For example, if today is 31/10/2013 (MM/DD/YYYY), the week number will be 40 for the year and the fifth for the (current) month.
If you have a past or future date, such as 15/03/2018, the week number will be 11 in that year and the third in that (given) month.
To find the week number, we can use the WEEKNUM function in Google Sheets. For the other two (current month or given month), we can use a custom formula.
The WEEKNUM function in Google Sheets returns a number representing the week of the year in which the given date falls.
Syntax:
WEEKNUM(date, [type])Arguments:
date: The date for which to determine the week number.type(optional): A number representing the day that the week starts on, as well as the system used for determining the first week of the year (1 = Sunday, 2 = Monday).
Example:
=WEEKNUM("15/03/2018") // will return 11
=WEEKNUM(DATE(2018,3,15)) // will return 11
=WEEKNUM(A1) // will return 11 if the date in A1 is "15/03/2018"Note: The WEEKNUM function, by default, considers Sunday to Saturday as one week. Also, the week that contains January 1 is the first week of that year.
Calculate the Week’s Number Within the Month in Google Sheets
For example, assume we have the date 15/03/2018 in cell A2. It is a past date, not the current date.
Here is the formula to find the week number of this given date within the month of March:
=WEEKNUM(A2)-WEEKNUM(EOMONTH(A2,-1)+1)+1
This will return 3 because 15/03/2018 falls within the third week of March 2018.
The dates from Thu, 1 Mar 2018 to Sat, 3 Mar 2018 fall in week #1, Sun, 4 Mar 2018 to Sat, 10 Mar 2018 fall in week #2, and Sun, 11 Mar 2018 to Sat, 17 Mar 2018 fall in week #3.
You can also use the same formula with future dates, but not with the current date. This is because you may need to use the TODAY() function with the current date. We will see how to do this later.
Formula Explanation
There are two parts in the formula where part #1 returns the week number of the date and part #2 returns the week number of the date converted to the month start date.
So the formula that returns the week number for the given month is part #1 - part #2.
The part #1 is:
=WEEKNUM(A2)The above formula will return the week number of the date in cell A2, which is 11. Please scroll up to see the image (Figure 1) where I have marked the month numbers.
The part #2 is:
=WEEKNUM(EOMONTH(A2,-1)+1)Let me explain it.
We can use the EOMONTH function to find the end of the month date of any given date.
Syntax:
EOMONTH(start_date, months)If the months argument is 0, you will get the last day of a month of the given date. Here you can put -1 to get the last day of the previous month of the given date. Simply put +1 to get the month start date of the given date.
=EOMONTH(A2,-1)+1That means the above week number part #2 formula returns the week number of the first day of the month in A2, and it will be 9.
So the formula calculates 11 – 9 + 1 = 3, i.e.;
Week # Within Month (1-5) = Week # of the Date – Week # of the Month Start Date + 1
How to Count the Current Week Number for the Month in Google Sheets
To count the current week number for the month, we can follow the below generic formula:
Current Week # for the Month (1-5) = Week # of Today’s Date – Week # of the Current Month’s Start Date + 1
=WEEKNUM(TODAY())-WEEKNUM(EOMONTH(TODAY(),-1)+1)+1The logic of the formula is the same as earlier. We just need to replace the date with TODAY(). In our last formula, we used cell reference A2 in the formula to point to a date. Just replace that with TODAY(). the rest of the formula is the same.
Here is a breakdown of the formula:
TODAY(): Returns the current date.WEEKNUM(TODAY()): Returns the week number of the current date.WEEKNUM(EOMONTH(TODAY(),-1)+1): Returns the week number of the first day of the current month.+1: Adds one because we want the current month’s week number to start from 1 not from 0.
You can hardcode today’s date in the formula instead of specifying the TODAY() function. But that won’t be dynamic.
Thank you for the stay!
Amazing job as usual, P. Thanks so much
Hi Prashanth,
Thanks for the correction. That works perfectly!
I have to admit, I’m not very familiar with the
LET()andLAMBDA()functions and only minorly familiar withMAP().Hi Prashanth,
I found this article when searching for help figuring out how many weeks in a month, contain a specific day (i.e. paychecks on Thursday). Your base formula was key to my solution so I thought I would post my solution here for anyone who might be looking to find the same thing.
While the
typeparameter ofWEEKDAY()is a straight-forward day offset from Sunday, thetypeparameter toWEEKNUM()is coded and is only available in the extended documentation. This threw me off initially.14is the correct value for `type` to use Thursday.The odd thing I found was that the
WEEKNUM()formula returned the incorrect number of weeks in a month, when the first of the month was on a Thursday. I thought for a while and couldn’t figure out how to alter the formula to correct that anomaly. Eventually I just decided to account for the edge-case.Here is my solution for finding the number of weeks in the month of a given date, contain a Thursday.
=WEEKNUM(EOMONTH(A1, 0), 14) -WEEKNUM(EOMONTH(A1, -1) + 1, 14) +
IF(WEEKDAY(DATE(YEAR(A1), MONTH(A1), 1)) = 5, 1, 0)
Hi Todd,
Thanks for sharing. Here is another way to handle this.
=COUNTIF(INDEX(WEEKDAY(SEQUENCE(DAY(EOMONTH(A1,0)),1,
EOMONTH(A1,-1)+1))),5
)
But you may require a Lambda to expand it, if necessary.
Hi Prashanth,
Interesting… I wasn’t able to get that to work inside an
ArrayFormula(), but I applied it manually against dates from 1/1/2020 -to- 12/1/2031 and it occasionally returned an extra week.I looked for a pattern that would expose the calculation error, but I couldn’t identify one. The following dates calculated 5 Thursdays instead of the 4 that exist in the month…
9/1/2020 (Tue) – 8 months later
6/1/2021 (Tue) – 9 months later
2/1/2022 (Tue) – 8 months later
11/1/2022 (Tue) – 9 months later
2/1/2023 (Wed) – 3 months later
4/1/2025 (Tue) – 26 months later
9/1/2026 (Tue) – 17 months later
6/1/2027 (Tue) – 9 months later
2/1/2028 (Tue) – 8 months later
2/1/2029 (Thu) – 12 months later
4/1/2031 (Tue) – 26 months later
Hi Todd,
That would work now! I made a mistake earlier, corrected that.
The logic is to generate a sequence for the whole month and count the weekdays equal to 5 in that sequence.
It’s flexible as you can replace 5 with any other weekday number.
You can follow this formula to make it an array formula:
=ARRAYFORMULA(LET(range, A1:A,
test,MAP(range,LAMBDA(row,COUNTIF(
WEEKDAY(SEQUENCE(DAY(EOMONTH(row,0)),1,EOMONTH(row,-1)+1)),5))),
IF(test=0,,test))
)
I think this breaks when the end of a week is also the end of the month.
So, assuming Sunday – Saturday weeks in March 2023.
The final day of the month is Friday. It is week 13 of 2023.
If I want the week number of April for April 7, 2023, the formula returns a two instead of a 1, although April 7 is in the first week (week 14).
Any ideas on how to work around that?
Hi, Parcoast,
Please follow the below steps to understand the formula.
We require three blank columns for the test.
1. Insert the following Sequence in cell A2 and then select A2:A > Format > Number > Custom Number Format >
dd"-"mmm"-"yyyy ddd=sequence(365,1,date(2023,1,1))2. In cell B2, insert the following Weeknum formula.
=ArrayFormula(weeknum(A2:A366,1))3. Finally, insert the following current month’s week number array formula in cell C2.
=ArrayFormula(WEEKNUM(A2:A366,1)-WEEKNUM((EOMONTH(A2:A366,-1)+1),1)+1)Go through every row and you will find the logic.
Also, check How to Sum by Week of the Month in Google Sheets.
Thanks a lot !
Hi Prashanth,
This still doesn’t quite work for me. For the month of May 2021, the results are still a week ahead. If I remove the last ‘+1’ and only have:
=WEEKNUM(A2,2)-WEEKNUM(EOMONTH(A2,-1)+1,2), then May is perfect, however, June then starts with Week 0.
Hi, Jamie,
The best way to understand my formula is as follows.
In a blank (new) Sheet, enter the following array formulas.
In Cell A2:
=sequence(35,1,date(2021,5,1))The formula would return a sequence of dates/date values from 1-May-2021 to 4-Jun-2021. Please select A2:A36 and apply Format (menu) > Number > Date.
In Cell B2:
=ArrayFormula(WEEKNUM(A2:A36)-WEEKNUM(EOMONTH(A2:A36,-1)+1)+1)The above is my array formula that returns the current month’s week number based on Sunday-Saturday weeks.
In Cell C2:
=ArrayFormula(WEEKNUM(A2:A36,2)-WEEKNUM(EOMONTH(A2:A36,-1)+1,2)+1)Your formula in Array Form (Monday-Sunday).
Now check the outputs.
Please don’t expect 7 full days in the first and the last week of the month as the formula considers Sunday-Saturday or Monday-Sunday to return the week numbers.
If you want 0-7 (1st week), 8-14 (2nd week), and so on as the week count basis, you can consider the below formula.
=roundup((days(A2,eomonth(A2,-1)+1)+1)/7)Thanks for this. However, it starts to fail in March/April of 2021. 29 March 2021 returns week#5 of March. However, the following Monday week (5 April 2021) returns as week#2 of April. Week#1 of April doesn’t exist!
Hi, Tedinoz,
The formula counts the week number of the entered date in that month NOT based on 1-7 (first week), 8-14 (second week), and so on. It is based on Sunday-Saturday.
Example:
1-May-2021 is Saturday which is week 1 in that month. Week 2 will be from 2-May-2021.
If you want the week to start from Monday-Sunday, modify the formula as below.
=WEEKNUM(A2,2)-WEEKNUM(EOMONTH(A2,-1)+1,2)+1Hope this clarifies.
Hello, I’m using a lot Google Sheets in my daily life and just want to say thank you for all the info, good practices, tricks you are publishing. It helps a lot!
Hi, Fabrice,
Thanks for taking time to leave your feedback!