What Is WEEKNUM In Google Sheets?
WEEKNUM in Google Sheets is a function that gives us the week number of a given date within a year. We know that we have an average of 52 weeks in a year for 12 months. So, for a particular date, the Google Sheets Weeknum helps us find the specific Weeknum.
For example, for the dates given below in cells A2:A6, when we apply the WEEKNUM Google Sheets Formula for cell B2 and then drag the formula using the fill handle to the rest of the cells, i.e., to cells B2:B6. We will get the output shown below.
You are free to use this image on your website, templates, etc. Please provide us with an attribution link.
Table of contents
Key Takeaways
- The Weeknum in Google Sheets helps users retrieve the precise serial number of the week of a specific date out of the approximate 52 weeks per year.
- We use the WEEKNUM function that has two arguments:
- The first mandatory argument accepts a valid date.
- The second argument is optional, which represents the day on which the week begins.
- The start of the year, Jan 01, will always be the first week of the year, and Dec 31 will always be the last week of the year, the 52nd week.
- We have different options to supply at the start of the week. Since this is an optional argument, we need not mandatorily supply the argument. However, if we leave this argument blank, by default, the value will be 1, i.e., the start of the day of the week is Sunday.
WEEKNUM Google Sheets Formula
The syntax of the WEEKNUM Google Sheets Formula is,
The arguments of the WEEKNUM formula are,
- date: It is the date for which we find the Weeknum. It is a mandatory argument.
- [type]: We need to mention the start of the weekday. It is an optional argument.
How To Use Weeknum In Google Sheets?
We can use the WEEKNUM in Google Sheets in 2 ways, namely,
- Method 1: Enter manually in the worksheet as follows:
The steps to enter the WEEKNUM formula directly in the worksheet are,
- Step 1: Choose an empty cell for the output.
- Step 2: Type =WEEKNUM( in the cell. [Alternatively, type =W or =WEEK and select the WEEKNUM function from the Google Sheets suggestions.]
- Step 3: Enter the arguments as cell values or cell references and close the brackets.
- Step 4: Press the “Enter” key to get the output.
- Method 2: Insert the formula using the “Insert” tab as follows:
First, choose an empty cell → select the “Insert” tab → click the “Function” option right arrow → click the “Date” option right arrow → select the “WEEKNUM” function, as shown below.
Examples
We will consider some examples to use the WEEKNUM in Google Sheets.
You can download this WEEKNUM Google Sheets Template from the link given in the bottom of the article.
Example #1 – Finding Weeknum in Google Sheets
We have the below dates to find the Google Sheets Weeknum for each date in that particular year.
The steps to find the Weeknum are as follows:
- Open the Google Sheets WEEKNUM formula in the B2 cell.
2. The date is nothing but the date cell value. So, select A2 as the cell reference.
3. Let’s ignore the last argument. By default, it will take Sunday as the starting day of the week. Close the brackets and press the “Enter” key. We will get the output shown below.
4. Drag the formula to other cells using the fill handle to get the following output.
Example #2 – Add the Word WEEK to the Weeknum in Google Sheets
We have seen how to return the Weeknum of any supplied date. To understand better, we will add the word “Week” before the week’s number for the previous example to get the following output.
The steps to apply the above formatting are as follows:
- Step 1: Select all the result cells first.
Step 2: Select the “Format” tab à click the “Number” option right arrow à select the “Custom number format” option, as shown below.
Step 3: The “Custom number formats” window opens. Type ““Week” #” and click the “Apply” button, as shown below.
The output is shown below.
Example #3 – Calculate the Number of Weeks in Google Sheets between Two Dates
Let us find the difference between the two dates, in terms of weeks, if we started the project on 15th Jan 2024 and completed it on 30th April 2024, as shown below.
The steps to calculate the weeks taken to complete the project are,
- Step 1: Firstly, find the difference between the two dates.
Step 2: Now, divide the difference by 7. We have 7 days a week.
Step 3: Press the “Enter” key to complete the formula. We have a result like this.
• Step 4: Let us change the format to get accurate results.
o First, select cell C2 → select the “Format” tab → click the “Number” option right arrow → scroll down and click the “Custom number format” option.
Next, the “Custom number format” window opens. Here, choose the required format, here “0”, and click the “Apply” button, as shown below.
The output is shown below in cell C2, 15 weeks, as the difference between the given dates.
Important Things To Note
- By default, the start of the week in Google Sheets is SUNDAY.
- We must supply the return type correctly to start with a different week.
- If the date is not in the correct format or is text or alpha-numeric data, we will get #VALUE! error.
Frequently Asked Questions (FAQs)
To find the week’s number between two dates in Google Sheets, we will consider an example of a planned business trip from 20th July 2024 to 30th Dec 2024, as shown below.
Let us apply the formula we used in example 3 to find the difference between the two dates and divide the difference by 7. So, we have 7 days a week.
We get the output shown below.
Let us change the format of the resulting cell to fractions.
First, select cell C2 à select the “Format” tab à click the “Number” option right arrow à scroll down and click the “Custom number format” option, as shown below.
Next, the “Custom number format” window opens. Here, select the required fraction format and click the “Apply” button, as shown below.
Finally, we have the number of weeks in fractions now.
We will consider the FAQ 1 example itself to apply the Conditional Formatting for the WEEKNUM formula’s result as follows:
First, choose cell C2, i.e., the result cell à select the “Format” tab à click the “Conditional formatting” option as shown below.
The “Conditional format rules” window appears on the right side of the window. Here, click the “Add another rule” option.
1. The “Apply to range” field is already filled with the result cell address, C2.
2. Now, select “Is not empty” from the “Format cells if…” drop-down under the “Format rules” field.
3. Next, select yellow color in the “Fill color” from the “Formatting style” options under the “Format rules” field.
4. Finally, click “Done” to close the “Conditional format rules” window.
We will get the final output as shown below.
A few reasons why the WEEKNUM formula may not work are,
• The cell reference given for the argument is not a valid date.
• We have given the date value directly as an argument, and have not enclosed it in double-quotes. In such scenarios, we get an incorrect result.
• We get the “#NUM!” error if the date values are out of range or not in the right format.
• The result becomes 52 when we delete the dataset or there are empty or blank cells.
Download Template
This article must help understand Weeknum in Google Sheets with its formulas and examples. You can download the template here to use it instantly.
Recommended Articles
Guide to What is Weeknum In Google Sheets. We learn to use formula to find week number of a year, between two dates, examples, work template. You can learn more from the following articles. –
Leave a Reply