Dataset Overview
Suppose we have a dataset containing sales records for a shop, including the date of each transaction. Our goal is to convert these dates into week numbers corresponding to their respective months in Excel.
We’ll achieve this using the WEEKNUM function along with other daterelated functions. Follow these steps to perform the conversion:
Method 1 – Using the WEEKNUM Function
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.
 We’ll extract the day from the given date and determine the first day of the month to which the date belongs.
 We’ll calculate the difference between these values and add 1 to avoid week numbers of 0.
 Let’s break down the formula:
=WEEKNUM(B5,1)WEEKNUM(DATE(YEAR(B5),MONTH(B5),1),1)+1
 Here’s what each part of the formula represents:
 MONTH(B5): Returns the month value (e.g., 6 for June) from the given date.
 YEAR(B5): Provides the year value (e.g., 2020) from the date.
 DATE(YEAR(B5), MONTH(B5), 1): Constructs the first day of the same month (e.g., June 1, 2020).
 WEEKNUM(B5,1): Gives the week number of the date.
 WEEKNUM(DATE(YEAR(B5), MONTH(B5), 1), 1): Calculates the week number for the first day of the month.
Steps:
 Select cell D5 in your Excel sheet.
 Enter the formula mentioned above.
 Press ENTER.
 Drag down the Fill Handle tool to copy the formula for other cells.
 Your dates are now converted to week numbers of the month using the WEEKNUM function.
Method 2 – Using the ISOWEEKNUM Function to Convert Dates to Week Numbers of the Month
In addition to the previously discussed method, we can also convert dates to week numbers of the month using the ISOWEEKNUM function in Excel. Let’s walk through how to achieve this using the ISOWEEKNUM function along with other daterelated functions.
 Objective: We want to convert a given date into the week number of the month.
 Methodology:

 Extract the day from the given date.
 Determine the first day of the month to which the date belongs.
 Calculate the difference between these values and add 1 to avoid week numbers of 0.
 Formula:
 Breaking down the formula:
=ISOWEEKNUM(B5)ISOWEEKNUM(DATE(YEAR(B5),MONTH(B5),1))+1


 MONTH(B5): Returns the month value (e.g., 6 for June) from the given date.
 YEAR(B5): Provides the year value (e.g., 2020) from the date.
 DATE(YEAR(B5), MONTH(B5), 1): Constructs the first day of the same month (e.g., June 1, 2020).
 ISOWEEKNUM(B5): Gives the week number of the date (following ISO standards).
 ISOWEEKNUM(DATE(YEAR(B5), MONTH(B5), 1)): Calculates the week number for the first day of the month.

 Implementation:

 Select cell D5 in your Excel sheet.
 Enter the provided formula.
 Press ENTER.
 Drag down the Fill Handle tool to AutoFill the formula for the rest of the cells.
Now you have the week numbers of the months corresponding to the given dates using the ISOWEEKNUM function.
Method 3 – Using WEEKNUM and WEEKDAY Functions
To calculate week numbers based on dates in Excel, we’ll utilize both the WEEKNUM and WEEKDAY functions. Follow these steps:
 Select cell D5.
 Insert the following formula:
=IF(WEEKDAY(B5,1)>5,"0",WEEKNUM(DATE(YEAR(B5),1,DAY(B5WEEKDAY(B5,1)))))
Let’s break down the Excel formulas step by step:

 YEAR(B5): The YEAR function extracts the year from the given date (in this case, 2020). Output: 2020
 WEEKDAY(B5,1): The WEEKDAY function determines the day of the week (ranging from 1 to 7) for the specified date (6 corresponds to Saturday). Output: 6
 DAY(B5WEEKDAY(B5,1)): The DAY function calculates the day of the month by subtracting the weekday (6) from the original date (January 6, 2020). Output: 6
 DATE(YEAR(B5),1,DAY(B5WEEKDAY(B5,1))): The DATE function constructs a new date using the extracted year (2020), the month (January), and the calculated day (6). Output: January 6, 2020 (represented as a serial number: 43836)
 WEEKNUM(DATE(YEAR(B5),1,DAY(B5WEEKDAY(B5,1)))): The WEEKNUM function assigns a week number to the specified date (January 6, 2020). Output: Week 2
 IF(WEEKDAY(B5,1)>5,“0”,WEEKNUM(DATE(YEAR(B5),1,DAY(B5WEEKDAY(B5,1))))): The IF function checks if the weekday (6) is greater than 5 (i.e., Saturday or Sunday). If true, it returns “0”; otherwise, it provides the week number (2). Output: “0” (since it’s a weekend)
 Press ENTER.
 To apply this formula to other cells, drag down the Fill Handle tool.
 This process will yield the week numbers corresponding to the dates, thanks to the WEEKNUM and WEEKDAY functions.
Method 4 – Using DAY and ROUNDUP Functions
In this method, we’ll demonstrate how to convert dates into week numbers of the month using the DAY and ROUNDUP functions. Follow these simple steps:
1. Using the DAY Function:
 Select cell C5.
 Enter the following formula:
=DAY(B5)
 The DAY function will extract the day value from the given date in cell B5.
 Press ENTER.
 Drag down the Fill Handle tool to apply the formula to other cells.
 This will give you the day values for all the provided dates.
2. Converting Days to Week Numbers:
Now, we will convert the days to week numbers of the month in Excel by dividing the values of days by 7.
 Select cell D5.
 Insert the following formula.
=C5/7
 This formula divides the day values by 7 to obtain week numbers (in decimal format).
 Press ENTER.
 AutoFill the formula down to calculate week numbers for the remaining dates.
3. Rounding Up Week Numbers:
 Select cell E5.
 Enter the following formula:
=ROUND(D5,0)
 The ROUNDUP function rounds the decimal week numbers to whole numbers (with 0 decimal places).
 Press ENTER.
 AutoFill the formula to compute week numbers for all dates.
 Now you have the week numbers of the months using the DAY and ROUNDUP functions in Excel!
Method 5 – Using INT and DAY Functions to Convert Date to Week Number of Month
In this method, we’ll utilize the INT and DAY functions to convert dates into week numbers of the month in Excel. Follow these steps:
 Select cell D5 and insert the following formula:
=INT((DAY(B5)1)/7)+1

 We use the DAY function to extract the day value from the date in cell B5.
 We subtract 1 from the day value and divide it by 7 to determine the week number within the month.
 The INT function then converts this result into an integer value.
 We add 1 to avoid having a week number of 0.
 Press ENTER to calculate the week number for the given date in cell B5.
 Drag down the Fill Handle tool to AutoFill the formula for the remaining cells in the column.
 By following these steps, you’ll obtain the week numbers of the months using the DAY and INT functions in Excel.
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
You can download the practice workbook from 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 20240901 (YYYYMMDD). 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)+1
Regards
Mahfuza Anika Era
Exceldemy