The Excel **WEEKNUM function** helps users to determine the week number of a provided date efficiently. It enables users to extract and manipulate week numbers, managing data analysis and organization based on weeks. This function simplifies the process of tracking and reporting, ensuring effective utilization of data.

You can also call this function an Excel TIME and DATE function. In this article, we’ll get to learn various examples of how we can use this WEEKNUM function effectively in Excel with appropriate illustrations.

**Table of Contents**Expand

**Introduction to the WEEKNUM Function in Excel**

**Function Objective**

*The WEEKNUM function is used to calculate the week number of a date*.

**Syntax**

*=WEEKNUM(serial_number, [returns_type])*

**Arguments Explanation**

Arguments | Required/Optional | Explanation |
---|---|---|

serial_number |
Required |
Value from which to calculate the week number |

returns_type |
Optional |
From which day the week begins |

The **WEEKNUM **function can be used in two different [**returns_type**] ways:

**Way 1:** Week 1 specifies the week which contains January 1st;

**Way 2:** Week 1 is the week that includes the first Thursday of the year.

Returns_type |
Week begins on |
System |
---|---|---|

1 or omitted |
Sunday |
1 |

2 |
Monday |
1 |

11 |
Monday |
1 |

12 |
Tuesday |
1 |

13 |
Wednesday |
1 |

14 |
Thursday |
1 |

15 |
Friday |
1 |

16 |
Saturday |
1 |

17 |
Sunday |
1 |

21 |
Monday |
2 |

## How to Use Excel WEEKNUM Function: 5 Examples

Let’s assume, we have a report on *Project: Community Gardening* in our hands. This dataset includes *Flat*, its corresponding holder *Name*, *Start Date*, *End Date*, and *Points* under columns **B**, **C**, **D**, **E**, and **F** respectively. Actually, it’s a housing society’s garden maintenance routine. Who will take care from when to when and their points/scores are also shown.

*Note:**This is a basic dataset to keep things simple. In a practical scenario, you may encounter a much larger and more complex dataset*.

Now, we’ll utilize this dataset to show how to use the **WEEKNUM** function in Excel through multiple examples. So, let’s explore them one by one.

Not to mention, here, we have used the ** Microsoft Excel 365** version; you may use any other version according to your convenience. Please leave a comment if any part of this article does not work in your version.

### 1. Use Text as Argument of WEEKNUM Function to Calculate Week Number

The **WEEKNUM** function can take valid text as an argument. Here, by valid we mean that it has to be a date. Then, it’ll return the corresponding week number for this date in the output cell.

- First, in cell
**G5**, enter the following formula and press**ENTER**.

`=WEEKNUM("5-Jan-2023")`

Instantly, it returns 1 as it’s the first week of this year. We can easily understand that 5 of the first month of a year has the most possibility to be in the first week of a year.

- Now, bring the cursor to the right-bottom corner of cell
**G5**and it’ll look like a plus (**+**) sign. It’s the**Fill Handle**tool. - Then, double-click on it to
**AutoFill**in the remaining cells in**Column G**.

The following image provides the entire result.

### 2. Use Cell Reference as Argument to Find Week Number in Excel

Here, we’ll achieve the same output but use the cell reference as an argument in the **WEEKNUM** function.

- First, write the following formula in cell
**G5**and press**ENTER**.

`=WEEKNUM(D5)`

*Note:**Here, we omitted the*.

**return_type**argument so that the week starts on**Sunday**. Giving**1**or**17**will give the same resultIt confirms that this start date is in the first week of the year 2023.

### 3. Achieve Week Number in a Month Using Excel WEEKNUM Function

You can utilize the **WEEKNUM**, **DATE**, and **MONTH** functions if you want to convert a specific date to its corresponding week number within the month. You can use them in the following combination:

- The formula in cell
**G5**is the following.

`=WEEKNUM(D5)-WEEKNUM(DATE(YEAR(D5),MONTH(D5),1))+1`

**Formula Breakdown**

**WEEKNUM(D5):**This part calculates the week number for the date in cell**D5**. It determines the week number based on the default week numbering system in Excel.**DATE(YEAR(D5),MONTH(D5),1):**This constructs a new date using the year and month from the date in cell**D5**and a day of 1. It represents the first day of the same month as the date in cell**D5**.

**WEEKNUM(DATE(YEAR(D5),MONTH(D5),1)):**This calculates the week number for the constructed date from step 2. It determines the week number based on the default week numbering system in Excel.**WEEKNUM(D5)-WEEKNUM(DATE(YEAR(D5),MONTH(D5),1)):**This part subtracts the week number obtained in step 3 from the week number obtained in step 1. The purpose is to determine the difference in week numbers between the specific date in cell**D5**and the first day of the same month.**WEEKNUM(D5)-WEEKNUM(DATE(YEAR(D5),MONTH(D5),1))+1:**This part adds 1 to the difference calculated in step 4. The purpose is to adjust the week number difference to account for the starting week.

### 4. Find Sum and Average by Week Number in Excel

We can also use week numbers for other types of calculations. Here, this dataset includes the *Evaluation Date* in extra from the previous dataset. We assumed this date when the performance of each person is being evaluated.

We opt to compute the sum of points as well as the average points in a week.

- To find the
*Week Number*of the corresponding*Evaluation Date*, the formula in cell**F5**is the following.

`=WEEKNUM(D5)`

- Now, detect the unique week numbers from
**Column F**. The formula in cell**B17**is like below.

`=UNIQUE(F5:F14)`

The **UNIQUE function** returns a list of unique values in a range or in a list.

To calculate the sum of points in week 7, insert the following formula in cell **C17**.

`=SUMIF($F$5:$F$14,B17,$E$5:$E$14)`

The **SUMIF function** adds the cells specified by a given condition or criteria.

From the above image, you can see that the sum of points for* Week 7* is **160** (cell **C17**) which is equal to the summation of points in the **E5:E7** range. Excel reduced manual labor efficiently.

- Again, input the following formula in cell
**D17**to get the*Average Points*for this month.

`=AVERAGEIF($F$5:$F$14,B17,$E$5:$E$14)`

The **AVERAGEIF function** finds the average for the cells specified by a given condition or criteria.

### 5. Highlight Cells Based on Week Number Using WEEKNUM Function

We can also highlight cells based on week numbers. In this case, we can use the **WEEKNUM** and **MOD** functions.

Let’s say, we want to highlight rows in which *Evaluation Date* appears in an even week number.

Firstly, we calculated the *Week Number* using the **WEEKNUM** function in **Column F**.

`=WEEKNUM(D5)`

- Now, select cells in the
**B5:F14**range. - Then, go to the
**Home**tab >>**Conditional Formatting**on the**Styles**group >>**New Rule**.

It’ll open the **New Formatting Rule** dialog box.

- Click on
**Use a formula to determine which cells to format**under the**Select a Rule Type**section. - In the
**Format values where this formula is true:**box, write the following formula.

`=MOD(WEEKNUM($D5),2)=0`

- Click
**Format**.

- In the
**Format Cells**dialog box, go to the**Fill**tab. - Select your desired color and click
**OK**.

You can see a preview of the formatting here.

- Click
**OK**.

Excel highlighted rows that have *Evaluation Date* in an even week number. For authentication, you can match with the value in **Column F**.

## [Fixed!] WEEKNUM Is Not Working in Excel: Reason with Solution

Sometimes, we face errors while working with the **WEEKNUM** function. In the following image, we get **#NUM!** and **#VALUE!** errors. But why?

- If you look clearly, you can see that we used
**18**as theargument of this function which is not valid. That’s why Excel is returning this error.*return_type*

- Change the formula in cell
**G5**to the following.

`=WEEKNUM(D5,1)`

Now, it returns the correct output.

- Also, it gives us errors when we use invalid dates which is not possible in reality. Like, we used 29 Feb in the below image which cannot be possible in 2023. For this reason, it returned an error.

- Correct the formula in cell
**G7**and it becomes the following.

`=WEEKNUM(“28-Feb-2023”)`

The error is gone and the function is working correctly.

## How to Find Week Range Using WEEKDAY & DATE Functions in Excel

From this information, we take another dataset. Here, we want to determine the *Start Date* and *End Date* of this corresponding *Week* in **Column D**.

See the dataset and inspect the differences from the previously shown dataset.

- We used the following formula in cell F5 to find the
*Start Date of the Week*.

`=DATE(E5,1,-2)-WEEKDAY(DATE(E5,1,3))+D5*7`

**Formula Breakdown**

**DATE(E5, 1, -2):**This part constructs a date using the year from cell**E5**, a month of**1**(January), and a day of**-2**. By using**-2**, it sets the date to the second-to-last day of the previous month (December of the specified year).**WEEKDAY(DATE(E5, 1, 3)):**The**WEEKDAY function**calculates the weekday number for the specified date, which is the 3rd day of January in the year specified in cell**E5**.**DATE(E5,1,-2)-WEEKDAY(DATE(E5,1,3)):**Then, it subtracts the weekday value (obtained in step 2) from the constructed date in step 1. The purpose is to obtain the date of the first day of the week that contains January 1st in the specified year.**D5*7:**This multiplies the value in cell**D5**by**7**, which represents the number of weeks. This calculates the total number of days to add to the date obtained in step 3.**DATE(E5,1,-2)-WEEKDAY(DATE(E5,1,3))+D5*7:**The date obtained in step 3 is added to the total number of days calculated in step 4. This results in the desired week’s date.

- Then, the formula (in
**cell G5**) we used to find the*End Date of Week*is the following:

`=DATE(E5,1,-2)-WEEKDAY(DATE(E5,1,3))+D5*7+6`

## How to Compute Week Numbers Between Two Dates in Excel

We have learned how to find the week number of a given date in Excel. However, is it possible to determine the number of weeks between the two dates?

`=(E5-D5)/7`

We divided the difference by 7 as there are 7 days in a week.

**Read More:** How to Convert Date to Week Number of Month in Excel

## How to Determine Fractional Number of Weeks in Excel

In the previous section, we achieved the number of weeks between two dates in decimal format. But how can we get it in fractional format?

- Insert the same formula in cell
**F5**as the**previous example**. - Then, select cells in the
**F5:F14**range and press**CTRL + 1**on your keyboard.

- Now, select
**Number**>>**Fraction**>>**As quarters (2/4)**. - Click
**OK**.

See the output in the fractional format in the image below.

## How to Obtain Month from Given Week Number in Excel

Previously, we learned to obtain a week number from a specific date. In this part, we’ll show you how you can obtain a month name from a given week number in Excel.

- Let’s put the formula in cell
**F5**to get the result like the image below.

`=TEXT(DATE($E5,1,1)+($D5-1)*7,"mmm")`

The **TEXT function** returns a numeric value in a specified format.

**Formula Breakdown**

**DATE($E5, 1, 1):**This part constructs a date using the year from cell**E5**, a month of 1 (January), and a day of 1. This creates the first day of the specified year.**($D5-1)*7:**Here, the value in cell**D5**(week number) is subtracted by 1 and then multiplied by 7. This calculates the number of days to add to the first day of the year to reach the desired week.- The result of step 2 is added to the date obtained in step 1. This provides the date of the desired week.
**TEXT(…, “mmm”):**The final step involves formatting the resulting date as an abbreviated month name. The**TEXT**function is used to convert the date into the desired format.

## Things to Remember

**#VALUE!**error happens when we use non-numeric data or data that is not a valid date.**#NUM!**error happens when the function does not permit the value and data is out of range though numeric.- Ensure that the dates you provide to the
**WEEKNUM**function are in a valid format recognized by Excel, such as proper date values or valid date serial numbers. - Take note of any cultural or regional differences in week numbering systems when using the
**WEEKNUM**function, as different countries may have variations in their week numbering conventions.

## Frequently Asked Questions

**1. Can I use the WEEKNUM function in combination with other functions or formulas?**

Yes, the **WEEKNUM** function can be combined with other functions and formulas in Excel to perform various calculations and analyses based on week numbers.

**2. Can I adjust the WEEKNUM function to consider Monday as the first day of the week?**

Yes, you can modify the **WEEKNUM** function to consider Monday as the first day of the week by adding an optional second argument with a value of **2**.

**3. Can the WEEKNUM function handle dates from different years?**

Yes, the **WEEKNUM** function can handle dates from different years. It accurately calculates the week number based on the specified date, regardless of the year transition.

**4. Does the WEEKNUM function consider leap years when calculating week numbers?**

Yes, the **WEEKNUM** function accounts for leap years and adjusts the week numbers accordingly based on an additional day in a leap year.

**Download Practice Workbook**

Download this practice workbook to exercise while you are reading this article.

## Conclusion

In conclusion, the Excel **WEEKNUM** function is a useful tool for working with week numbers in Excel. It allows you to determine the week number of a given date based on different methods.

This can be helpful in various scenarios, such as organizing and analyzing data based on weeks or calculating time durations in weeks. This function proves to be valuable for various data analysis and time-tracking tasks in Excel.

I hope all of the suitable methods mentioned above to use the WEEKNUM function will now provoke you to apply them in your Excel spreadsheets with more productivity. You are most welcome to comment if you have any questions or queries.

**<< Go Back to Excel Functions | Learn Excel**