How to convert week number to date or vice versa in Excel?

Working with dates and week numbers in Excel is a common need in business analytics, project planning, and reporting. For example, you might want to know which week a particular date falls in, or determine the date range for a specific week number in a given year. However, Excel does not provide direct built-in options to convert week numbers to full date ranges or quickly reverse the process. To address these needs, you can use a variety of formulas, VBA solutions, and other Excel features depending on your specific requirements and the volume of data you want to process. Below are several practical methods to handle this task in Excel.

Convert week number to date with formulas

Convert date to week number with formulas

Convert between week number and date with VBA codes

Convert week number to date with formulas

Suppose you have a specific year and a week number entered in your worksheet (for example, 2015 in cell B1 and 15 in cell B2). You may wish to calculate the actual start date (Monday) and end date (Sunday) of this week. This can be especially helpful in schedule planning, preparing weekly summaries, or referencing weekly reporting periods.

To calculate the date range for the specified week number, you can use the following Excel formulas:

1. Select a blank cell to display the start date (here, cell B5). Enter the folling formula, and press the Enter key. The formula will return a serial number representing the date.

=MAX(DATE(B1,1,1),DATE(B1,1,1)-WEEKDAY(DATE(B1,1,1),2)+(B2-1)*7+1)

2. For the end date of the same week (for example, in cell B6), enter the following formula, then press Enter. The formula will return the serial number for the last day of the specified week.

=MIN(DATE(B1+1,1,0),DATE(B1,1,1)-WEEKDAY(DATE(B1,1,1),2)+B2*7)

Note: In the formulas above, B1 is the cell containing the year (for example,2015), and B2 contains the week number you want to convert. Adjust these cell references for your actual worksheet as needed.

3. The formulas initially return numbers rather than formatted dates. To display the correct date format, select both formula cells, then go to Home > Number Format drop-down > Short Date. This will convert the values to recognizable dates.

Convert date to week number with formulas

Conversely, you may want to determine the week number in which a given date falls. Excel provides the WEEKNUM function for this purpose. This is particularly handy when analyzing timesheet data, generating weekly reports, or tracking deliveries and events by week.

1. Choose a blank cell for the output of the week number. Enter the following formula (assuming your date is in B1):

=WEEKNUM(B1,1)

2. Then, press Enter. This formula returns the week number considering Sunday as the first day of the week.

Notes:

(1) In this formula, B1 is the cell containing the date you want to convert.

(2) If you prefer to count weeks starting from Monday (common in the ISO week system), use this version of the formula:

=WEEKNUM(B1,2)

Convert between week number and date with VBA codes

In this article, we’ll explore two VBA routines: one that converts a week number (and year) into its corresponding date range, and another that determines the ISO week number for any given date.

Convert week number to date range:

1. Open the VBA Editor by clicking Developer > Visual Basic. In the window that opens, click Insert > Module and paste the below code into the module:

Sub WeekNumberToDateRange()
    Dim YearNum As Long
    Dim WeekNum As Long
    Dim FirstDay As Date, LastDay As Date
    Dim Jan4 As Date
    YearNum = Application.InputBox("Enter the year:", "KutoolsforExcel", Year(Date), Type:=1)
    If YearNum < 1 Then Exit Sub
    WeekNum = Application.InputBox("Enter the week number:", "KutoolsforExcel", 1, Type:=1)
    If WeekNum < 1 Then Exit Sub
    Jan4 = DateSerial(YearNum, 1, 4)
    FirstDay = Jan4 - Weekday(Jan4, vbMonday) + 1
    FirstDay = FirstDay + (WeekNum - 1) * 7
    LastDay = FirstDay + 6
    MsgBox "Start date: " & Format(FirstDay, "yyyy-mm-dd") & vbCrLf & _
           "End date:   " & Format(LastDay, "yyyy-mm-dd"), _
           vbInformation, "KutoolsforExcel"
End Sub 

2. Run the macro using the button. It will prompt you for the year and week number, then display the respective date range in a dialog box.

Convert date to week number:

1. Copy and paste the following VBa code into the module:

Sub DateToWeekNumber()
 Dim InputDate As Date
 Dim WeekNum As Integer
 InputDate = Application.InputBox("Enter the date (yyyy-mm-dd):", "KutoolsforExcel", Date, Type:=2)
 WeekNum = WorksheetFunction.WeekNum(InputDate, 2)
 MsgBox "The week number is: " & WeekNum, vbInformation, "KutoolsforExcel"
End Sub  

2. After inserting and running this code, enter your target date when prompted and the macro will show the week number, counting Monday as the start of the week. You can modify the code by changing the second argument in WeekNum to 1 for weeks starting on Sunday.

Related articles:

How to count the number of specific weekdays between two dates in Excel?

How to add / subtract days / months / years to date in Excel?