Looking for ways to know how to convert date to week number of month in Excel? Then, this is the right place for you. Here, you will find 5 different step-by-step explained ways to convert date to week number of month in Excel.
How to Convert Date to Week Number of Month in Excel: 5 Ways
Here, we have a dataset containing the Date and Sales records of a shop. Now, we will convert these dates to the week number of their consecutive months to show you how to convert date to week of month in Excel.
1. Using WEEKNUM Function to Convert Date to Week Number of Month in Excel
In the first method, we will use the WEEKNUM, DATE, YEAR, and MONTH functions to convert the date to the week number of the month in Excel.
Here, we will extract the day from the given date and the first day of the month the date falls within. Then, we will subtract the values and add 1 to avoid getting the week number of months as 0. Go through the steps to do it on your own.
Steps:
- Select cell D5.
- Insert the following formula.
=WEEKNUM(B5,1)-WEEKNUM(DATE(YEAR(B5),MONTH(B5),1),1)+1
Formula Breakdown
- MONTH(B5) —–> The MONTH function returns the value of the month of the given date.
- Output:Â {6}
- YEAR(B5) —–> The YEAR function returns the value of the year of the given date.
- Output:Â {2020}
- DATE(YEAR(B5),MONTH(B5),1 —–> The DATE function returns a certain date with a sequential serial number.
- DATE(2020,6,1) —–> turns into
- Output: {43983}
- DATE(2020,6,1) —–> turns into
- WEEKNUM(B5,1) —–> The WEEKNUM function returns a week number of months of a certain date.
- Output: {24}
- WEEKNUM(DATE(YEAR(B5),MONTH(B5),1),1) —–> turns into
- WEEKNUM(43983,1) —–> turns into
- Output: {23}
- WEEKNUM(43983,1) —–> turns into
Here, we extracted the day from the given date as 24 and the first day of the month the date falls within as 23. Then, we will subtract the values and add 1.
- Press ENTER.
- Drag down the Fill Handle tool to copy the formula for the rest of the cells.
- You will get the dates converted to the week number of the month using the WEEKNUM function.
2. Use of ISOWEEKNUM Function to Convert Date to Week Number of Month
We can also convert the date to the week number of the month in Excel using the ISOWEEKNUM function. Here, we will show you how to use the ISOWEEKNUM, DATE, YEAR, and MONTH functions to convert the date to the week number of the month.
Here, we will extract the day from the given date and the first day of the month the date falls within. Then, we will subtract the values and add 1 to avoid getting the week number of the month as 0.
Steps:
- Select cell D5 and insert the following formula.
=ISOWEEKNUM(B5)-ISOWEEKNUM(DATE(YEAR(B5),MONTH(B5),1))+1
Formula Breakdown
- MONTH(B5) —–> The MONTH function returns the value of the month of the given date.
- Output:Â {6}
- YEAR(B5) —–> The YEAR function returns the value of the year of the given date.
- Output:Â {2020}
- DATE(YEAR(B5),MONTH(B5),1 —–> The DATE function returns a certain date with a sequential serial number.
- DATE(2020,6,1) —–> turns into
- Output: {43983}
- DATE(2020,6,1) —–> turns into
- ISOWEEKNUM(B5) —–> The ISOWEEKNUM function returns a week number of months of a certain date that follows ISO standards.
- Output: {24}
- ISOWEEKNUM(DATE(YEAR(B5),MONTH(B5),1)) —–> turns into
- ISOWEEKNUM(43983) —–> turns into
- Output: {23}
- ISOWEEKNUM(43983) —–> turns into
Here, we extracted the day from the given date as 24 and the first day of the month the date falls within as 23. Then, we will subtract the values and add 1.
- Press ENTER.
- Drag down the Fill Handle tool to AutoFill the formula for the rest of the cells.
- You will get the week numbers of months of the dates using the ISOWEEKNUMBER function.
3. Applying WEEKNUM and WEEKDAY Functions in Excel
Here, we will use both WEEKNUM and WEEKDAY functions to convert the date to the week number of months in Excel.
Steps:
- Select cell D5 and insert the following formula.
=IF(WEEKDAY(B5,1)>5,"0",WEEKNUM(DATE(YEAR(B5),1,DAY(B5-WEEKDAY(B5,1)))))
Formula Breakdown
- YEAR(B5) —–> The YEAR function returns the value of the year of the given date.
- Output:Â {2020}
- WEEKDAY(B5,1) —–> The WEEKDAY function returns the number of the day of the week between 1-7.
- Output:Â {6}
- DAY(B5-WEEKDAY(B5,1)) —–> The DAY function returns the day of a given date.
- DAY(B5-6) —–> turns into
- Output: {6}
- DAY(B5-6) —–> turns into
- DATE(YEAR(B5),1,DAY(B5-WEEKDAY(B5,1)))) —–> The DATE function returns a certain date with a sequential serial number.
- DATE(2020,1,6) —–> turns into
- Output: {43836}
- DATE(2020,1,6) —–> turns into
- WEEKNUM(DATE(YEAR(B5),1,DAY(B5-WEEKDAY(B5,1)))) —–> The WEEKNUM function returns a week number of months of a certain date.
- WEEKNUM(43836) —–> turns into
- Output: {2}
- WEEKNUM(43836) —–> turns into
- IF(WEEKDAY(B5,1)>5,”0″,WEEKNUM(DATE(YEAR(B5),1,DAY(B5-WEEKDAY(B5,1))))) —–> The IF function returns a value if it satisfies the given condition and returns a different value if it does not satisfy.
- IF(6>5,”0″,2) —–> turns into
- Output: {“0”}
- IF(6>5,”0″,2) —–> turns into
- Press ENTER.
- Drag down the Fill Handle tool to AutoFill the formula for the rest of the cells.
- You will get the week numbers of months of the dates using the WEEKNUM and WEEKDAY functions.
4. Applying DAY and ROUNDUP Functions in Excel
Now, we will show you how to convert the date to the week number of the month by applying the DAY and ROUNDUP functions. We can do it by going through some simple steps.
Step-01: Using DAY Function
Here, we will use the DAY function to find the Day from the given Date. Follow the steps given below to do it on your own.
- Select cell C5 and insert the following formula.
=DAY(B5)
Here, in the DAY function, we selected cell B5 as serial_number. It will find the value of the day from the date.
- Now, press ENTER.
- Drag down the Fill Handle tool to AutoFill the formula for the rest of the cells.
- You will get the values of the day for all the given data.
Step-02: Dividing Days by Week
Now, we will convert the days to week numbers of the month in Excel by dividing the values of days by 7.
- First, select cell D5.
- Then, insert the following formula.
=C5/7
- Next, press ENTER.
- Drag down the Fill Handle tool to AutoFill the formula for the rest of the cells.
- Now, you will get the values of the week number of the months in decimal numbers.
Step-03: Rounding Up Week Number of Month
Here, we will round up the values of the week numbers of the month by using the ROUNDUP function.
- Select cell E5 and insert the following formula.
=ROUND(D5,0)
Here, in the ROUNDUP function, we selected cell D5 as the number and 0 as the num_digits. This function will round up the decimal value into a value of 0 decimals.
- Now, press ENTER.
- Drag down the Fill Handle tool to AutoFill the formula for the rest of the cells.
- You will get the week numbers of months of the dates using the DAY and ROUNDUP functions.
5. Using INT and DAY Functions to Convert Date to Week Number of Month in Excel
In this method, we will use the INT and DAY functions to convert the date to the week number of the month in Excel.
Steps:
- Select cell D5 and insert the following formula.
=INT((DAY(B5)-1)/7)+1
Here, first using the DAY function we get the value of a day of the date in cell B5. Then, we subtracted the value by 1 and divided it by 7 to get the day into the week number of the month. After that, we converted the day into an integer value by using the INT function. Finally, we added 1 with the value to avoid the week number as 0.
- Next, press ENTER.
- Drag down the Fill Handle tool to AutoFill the formula for the rest of the cells.
- Finally, you will get the week numbers of months of the dates using the DAY and INT functions.
Read More: Excel VBA to Find Week Number
Practice Section
In this section, we are giving you the dataset to practice on your own and learn to apply these methods.
Download Practice Workbook
Conclusion
So, in this article, you will find 4 ways to convert date to week number of month in Excel. Use any of these ways to accomplish the result in this regard. Hope you find this article helpful and informative. Feel free to comment if something seems difficult to understand. Let us know any other approaches which we might have missed here.
<< Go Back to Excel WEEKNUM Function | Excel Functions | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
Thank you for the method. However this does not take into account the calendar. For example, September 2021 has its first week finishing on 4th of September, however the code you posted goes with week 1 until 7th of September and not by calendar.
Do you have a method do go by calendar? So if the week starts with 1 on Sunday for example, it’s just that day in week 1?
Thanks in advance!
Hello MRRRR,
Thank you for your comment. If you follow the first method, we have shown you will get the week number from a date according to the calendar.
• Here, to find the week number of 4th September 2021 we used the following formula and got 1 as the week number.
=WEEKNUM(B3,1)-WEEKNUM(DATE(YEAR(B3),MONTH(B3),1),1)+1• On the other hand, using the same formula we got 2 as the week number for 5th September 2021.
Hope you have found your solution. If you face any further problems, please share your Excel file with us at [email protected].
Regards
Arin Islam,
Exceldemy.
Hi,
Thanks for the very useful tutorial. Using Method 1, I am receiving a 0 (zero) for the date 2024-09-01 (YYYY-MM-DD). My week starts on a Monday. Any idea why this is the case ?
=WEEKNUM(B1,2)-WEEKNUM(DATE(YEAR(B1),MONTH(B1),1),1)+1
Thank you.
Hi JAMZ,
Thank you for your comment. There is an error in the second portion of your formula. As your week starts on Monday, you must apply the WEEKNUM function’s return type as 2. Here is the corrected formula:
=WEEKNUM(B1,2)-WEEKNUM(DATE(YEAR(B1),MONTH(B1),1),2)+1Regards
Mahfuza Anika Era
Exceldemy