Arin Islam
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.
Download Practice Workbook
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}
- 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.
- 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.
Read More: How to Convert Date to Number in Excel (4 Methods)
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}
- 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.
- 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.
Read More: How to Convert Month to Number in Excel (3 Easy Methods)
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}
- 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 satisfies.
- IF(6>5,”0″,2) —–> turns into
- Output: {“0”}
- IF(6>5,”0″,2) —–> turns into
- 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.
Read More: Convert 3 Letter Month to Number in Excel (8 Suitable Methods)
Similar Readings
- How to Convert Degrees Decimal Minutes to Decimal Degrees in Excel
- Excel VBA to Convert Textbox Value to Number (2 Ideal Examples)
- How to Fix All Number Stored as Text in Excel (6 Easy Solutions)
- How to Convert Time to Number in Excel (5 Easy Methods)
- How to Convert Percentage to Whole Number in Excel (4 Methods)
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
- How to Convert Scientific Notation to Number in Excel (7 Methods)
- Fix Convert to Number Error in Excel (6 Methods)
- How to Convert Hours and Minutes to Decimal in Excel (2 Cases)
- Convert Degrees Minutes Seconds to Decimal Degrees in Excel
- How to Convert Exponential Value to Exact Number in Excel (7 Methods)
- How to Convert Percentage to Decimal in Excel (7 Methods)
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.