How to Convert Date to the Week Number of the Month in Excel (5 Methods)

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 date-related 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:

• 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.

• • • 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.

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(B5-WEEKDAY(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(B5-WEEKDAY(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(B5-WEEKDAY(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(B5-WEEKDAY(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)

• 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.

• 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:

• 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:

• • 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