WEEKNUMformula returns the week number of a specific date. It is most commonly used to group data by week or to calculate the number of weeks between two dates.
- How to use
- Examples of using
WEEKNUMformula not working?
- Similar formulas to
WEEKNUM formula with the syntax shown below, it has 1 required parameter and 1 optional parameter:
- date (required):
The date for which to calculate the week number. Must be a valid date formatted as a date, datetime or text string.
- type (optional):
Optional parameter that specifies the type of week numbering system to use. Can be either 1 or 2, with 1 being the default. Type 1 considers the week with January 1st as the first week of the year, while type 2 considers the first week to be the one containing the first Thursday of the year.
ExamplesHere are a few example use cases that explain how to use the
WEEKNUMformula in Google Sheets.
Grouping data by week
By using the
WEEKNUM formula in combination with a pivot table, you can easily group data by week. Simply add the date column to the rows section of the pivot table, then add the
WEEKNUM formula to the columns section. This will create a table with the week numbers as columns and the data grouped accordingly.
Calculating weeks between two dates
To calculate the number of weeks between two dates, subtract the
WEEKNUM value of the earlier date from the
WEEKNUM value of the later date. This will give you the number of weeks between the two dates, even if they are in different years.
Identifying week of the year
By default, the
WEEKNUM formula considers the week with January 1st as the first week of the year. This can be useful for identifying the week of the year for a given date, for example in a calendar view.
WEEKNUMnot working? Here are some common mistakes people make when using the
WEEKNUMGoogle Sheets Formula:
Incorrect date format
The date provided to the WEEKNUM formula must be in a valid date format. Check that the date is correctly formatted and is not just text.
Incorrect type argument
The [type] argument in the WEEKNUM formula must be either 1 or 2. Check that the argument is correctly entered and is not a text value.
Not accounting for different week numbering systems
The [type] argument in the WEEKNUM formula determines the week numbering system used. Type 1 uses the US system, where the week that contains January 1st is week 1. Type 2 uses the ISO week numbering system, where the week that contains the first Thursday of the year is week 1. Make sure to use the correct type for your needs.
The following functions are similar to
WEEKNUM or are often used with it in a formula:
WEEKDAYformula takes a given date and returns the day of the week it represents, as a number ranging from 1 (Sunday) to 7 (Saturday). This function is most commonly used to calculate the day of the week for a given date and to perform calculations or comparisons based on the day of the week.
DATEDIFfunction in Google Sheets calculates the difference between two dates using a specified unit of measurement. This function is commonly used to calculate the length of time between two dates, such as the number of days, months, or years. The function is particularly useful when working with financial or project planning data.
The YEAR formula in Google Sheets returns the year of a specified date. This function is useful when you need to extract the year from a date for analysis or calculations. The YEAR formula takes one argument, which is the date from which to extract the year. The output is a four-digit number representing the year.
MONTHformula is used to extract the month from a given date, returning it as a number between 1 and 12. This function is commonly used to summarize and analyze data that includes dates.
DAYfunction is used to extract the day of the month from a given date. It takes in a single argument, a date in a valid format, and returns the day of the month as a number between 1 and 31. This function can be useful for tasks such as sorting or filtering data based on the day of the month.
You can learn more about the
WEEKNUM Google Sheets function on Google Support.