Calculating a Date Based on Year, Week Number and Day of the Week

Problem: How to calculate the date using the relevant numbers for day of the week, week number, and year number.

Solution & Explanation:

There is no built-in function to figure out the desired date, but there are a number of ways that you can approach the problem and figure it out. In these examples we will assume that the year is in cell C1 and the desired week number is in cell B1.

Use the following formula:

=DATE(C2,1,1)-WEEKDAY(DATE(C2,1,1),2)+(B2-1)*7+A2

The DATE function returns the date of January 1st, for the year listed in cell C2.

The WEEKDAY function returns the day of the week (1-7) corresponding to a given date. In our example [return_type]=2, it means that week starts with Monday.

The number returned by the WEEKDAY function, representing a number of days, is subtracted from the date calculated by the DATE function. To that date is added the number of days that result from the following calculation:  7*(B2-1)+A2.

The result is the date that corresponds to the year, week number, and day of the week listed in cells A2:C2.