# How to Convert Date to Week Number of Month in Excel (5 Ways)

Get FREE Advanced Excel Exercises with Solutions!

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.

## 5 Ways to Convert Date to Week Number of Month in Excel

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 date to week number of 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:

• In the beginning, select cell D5.
• Then, 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}
• 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}

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.

• Now, press ENTER.
• Then, drag down the Fill Handle tool to AutoFill the formula for the rest of the cells. • Finally, you will get the Dates converted to week number of month using the WEEKNUM function. ### 2. Use of ISOWEEKNUM Function to Convert Date to Week Number of Month

We can also convert date to week number of month in Excel using the ISOWEEKNUM Function. Here, we will show you how to use ISOWEEKNUM, DATE, YEAR, and MONTH functions to convert date to 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 month as 0.

Follow the steps given below to do it on your own dataset.

Steps:

• First, select Cell D5.
• After that, 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}
• 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}

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.

• After that, press ENTER.
• Then, 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 ISOWEEKNUMBER function. ### 3. Applying WEEKNUM and WEEKDAY Functions in Excel

Here, we will use both WEEKNUM and WEEKDAY functions to convert date to week number of months in Excel.

Go through the steps given below to do it on your own.

Steps:

• In the beginning, select cell D5.
• Then, 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}
• 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}
• 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}
• 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 satisfies.
• IF(6>5,”0″,2) —–> turns into
• Output: {“0”}
• After that, press ENTER.
• Then, 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 WEEKNUMBER and WEEKDAY functions. ### 4. Applying DAY and ROUNDUP Functions in Excel

Now, we will show you how to convert date to week number of month by applying the DAY and ROUNDUP function. 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.

• First, select Cell C5.
• After that, insert the following formula
`=DAY(B5)` Here, in the DAY function, we selected Cell B5 as serial_number. It will find the value of Day from the Date.

• Now, press ENTER.
• Then, drag down the Fill Handle tool to AutoFill the formula for the rest of the cells. • After that, 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.

• In the beginning, select Cell D5.
• Then, insert the following formula
`=C5/7` • Next, press ENTER.
• After that, 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. Go through the steps given below to do it on your own.

• First, select Cell E5.
• Then, 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.
• Then, 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 ROUNDUP functions. ### 5. Using INT and DAY Functions to Convert Date to Week Number of Month in Excel

In the final method, we will use the INT and DAY functions to convert date to week number of month in Excel.

Now, follow the steps given below to do it on your own dataset.

Steps:

• In the beginning, select cell D5.
• Then, 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.

• After that, press ENTER.
• Then, 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. ## Practice Section

In this section, we are giving you the dataset to practice on your own and learn to use these methods. ## 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. And, visit ExcelDemy for many more articles like this. Thank you!

## Related Articles #### Arin Islam

Hello, I'm Arin. I graduated from Khulna University of Engineering and Technology (KUET) from the Department of Civil Engineering. I am passionate about learning new things and increasing my data analysis knowledge as well as critical thinking. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to it.

1. Reply 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?

• Reply 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. Advanced Excel Exercises with Solutions PDF  