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)+1
That 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)+1
The 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!
