Md. Abdur Rahim Rasel
In Microsoft Excel, the WEEKNUM function is used to count the week number of a certain year. You can also call this function an Excel TIME and DATE function. In this article, we’ll get to learn how we can use this WEEKNUM function effectively in Excel with appropriate illustrations.
The above screenshot is an overview of the article, representing a few applications of the WEEKNUM function in Excel.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
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 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 |
2 Suitable Ways to Use YEARFRAC Function in Excel
1. Use the WEEKNUM Function to Calculate the Week Number
Let’s say, we have a dataset where some persons Names and their Dates of Birth are given in Column B and Column C respectively. Now, we will calculate the week number of the Date of Birth of the corresponding year.
Let’s say, first we calculate the week number of Dalton’s Birthdate. For this, you have to follow the instructions that are given below:
Step 1:
- First, select cell D5 and type the WEEKNUM Function in the Formula Bar. The formula is
=WEEKNUM(C5)
- Where C5 is the Date of Birth value
Step 2:
- Now press Enter on your keyboard and you will get 8 in cell D5 as the Week of Dalton’s Birthday.
Step 3:
- After that, place your Cursor on the Bottom-Left on Cell D5 and press the Left-Click on your Mouse and drag it downward.
- Finally, you will be able to count the other person’s Birthdate’s Week of the corresponding year. The week number of all persons is given in the screenshot.
Read More: How to Use the DATEDIF Function in Excel
Similar Readings
- How to Use DAY Function in Excel (With 3 Examples)
- Use MINUTE Function in Excel (6 Examples)
- How to Use SECOND Function in Excel (3 Examples)
- Excel Current Time Formula (7 Suitable Examples)
2. Apply the WEEKNUM Function and DATE Function in Excel
After counting the week number of dates of the corresponding year, we will now count the first date and last date of an arbitrary week by using the combination of WEEKNUM and Date function. To count the first date and last date of an arbitrary week, you have to follow the directions. Let’s say, we will count the first Day and last Day of the week number 5 and year number 2005 from our dataset.
Step 1:
- First, select the cell D5 and then type the formula in the Formula Bar. the formula will be:
=DATE(B5,1,-2)-WEEKDAY(DATE(B5,1,3))+C5*7
- After that press Enter on your Keyboard
- After pressing Enter, you will get Monday, January 31, 2005, as the first date of week number
- Now, you will get the other date of the corresponding week
Step 2:
- Similarly, we will find the last date of week number 5 and year number 2005. For the end date, the formula will be:
=DATE(B5,1,-2)-WEEKDAY(DATE(B5,1,3))+C5*7+6
- After that, press Enter and you will get Sunday, February 6, 2005, as the last date of the week. In the same way, you will get the date of another week that has been given in the screenshot.
Read More: How to Use Excel EDATE Function
Things to Remember
👉 WEEKNUM Function is used to calculate the week number of a Date of that year.
👉 #VALUE error – happens when
- Non-numeric.
- Data is not a valid date.
👉 #NUM error – happens when
- Function does not permit the value.
- Data is out of range though numeric.
Conclusion
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.
Related Articles
- How to Use the Excel DAYS Function with a Practical Example
- Use NETWORKDAYS Function in Excel (3 Suitable Examples)
- How to use HOUR Function in Excel (5 Easy Examples)
- Use TIMEVALUE Function in Excel (4 Examples)
- How to Use WORKDAY Function in Excel (5 Examples)
- Use HOUR Function in Excel (5 Easy Examples)
- How to Use NETWORKDAYS.INTL Function in Excel (2 Examples)
