Author: Oscar Cronquist Article last updated on May 10, 2021

Calculate date given weekday and week number

This article demonstrates a formula that returns a date based on a week number and a weekday like Sun to Sat. It uses the current year to calculate the date.

La Thăng asks:

I want to find the day if given date and week, for example: if given Tuesday, 32nd week, 2015 >>> how to create a formula to point out 4/8/2015?

1. Calculate date given weekday and week number (week starts on Sunday)

Calculate date given weekday and week number week starts on sunday

Formula in cell D6:

=DATE(YEAR(TODAY()), 1, 1)-WEEKDAY(DATE(YEAR(TODAY()), 1, 1), 1)+(7*(D4-1))+MATCH($D$4, {"Sun", "Mon", "Tue", "Wed", "Thu", "Fri", "Sat"}, 0)

Back to top

Explaining formula in cell D6

Step 1 - Calculate the first date of this year

The TODAY function returns today's date, the function is volatile meaning it recalculates every time you enter a value in a cell and press enter or press F9.

TODAY() returns 5/8/21.

Step 2 - Calculate the first date of this year

The YEAR function returns the year based on an Excel date.

YEAR(TODAY())

becomes

YEAR(44324)

and returns 2021.

Step 3  - Calculate the first date of this year

The DATE function returns an Excel date based on a year number, month number and day number.

DATE(year, month, day)

Year is a number from 1900 to 9999, month is a number from 1 to 12. 1 = January, ... , 12 = December. Day is usually a number between 1 and 31 but can be larger.

DATE(YEAR(TODAY()), 1, 1)

becomes

DATE(2021, 1, 1)

and returns 44197.

Step 4 - Subtract with the corresponding weekday number

The WEEKDAY function converts an Excel date to a number ranging from 1 to 7, based on when the week begins.

WEEKDAY(serial_number, [return_type])

Calculate date given weekday and week number WEEKDAY function 2

If you live in the US the week in starts on Sundays, return_type is 1.

WEEKDAY(DATE(YEAR(TODAY()), 1, 1), 1)

becomes WEEKDAY(44197, 1)

and returns 6. 44197 is 1/1/2021 and that day is a Friday.

1 - Sunday, ... , 7 - Saturday.

Step 5 - Subtract date with weekday number

DATE(YEAR(TODAY()), 1, 1)-WEEKDAY(DATE(YEAR(TODAY()), 1, 1), 1)

becomes

44197 - 6 equals 44191.

Step 6 - Calculate and add the number of days from Monday/week 1 to Monday/(nth week -1)

+7*(D4-1)

Example, 7*(32-1)

becomes

7*31 and returns 217.

Step 7 - Calculate and add weekday number

The MATCH function returns the relative position of the given item in a cell range or array.

MATCH($D$4, {"Sun", "Mon", "Tue", "Wed", "Thu", "Fri", "Sat"}, 0)

If you week begins on sunday, this is the array you want to be using:

{"Sun", "Mon", "Tue", "Wed", "Thu", "Fri", "Sat"}.

Example, "Tue" returns 3. "Tue" is in position three in the array.

Step 8 - Add and subtract

44192+217+2 = 44411

44411 is 8/3/2021.

Back to top

2. Calculate date given weekday and week number (week starts on Monday)

Calculate date given weekday and week number week starts on monday

This example demonstrates a formula that works with weeks that begin with Mondays.

Formula in cell 5:

=DATE(YEAR(TODAY()), 1, 1)-WEEKDAY(DATE(YEAR(TODAY()), 1, 1), 2)+(7*(D4-1))+MATCH($D$3, {"Mon", "Tue", "Wed", "Thu", "Fri", "Sat", "Sun"}, 0)

Note that the WEEKDAY function uses 2 in the second argument meaning the week begins with a Monday and the array in the second argument in the MATCH function begins with Monday.

Back to top

Back to top

Tip! You can shrink the formula if you can convert the weekday to a number yourself.

=DATE(YEAR(TODAY()), 1, 1)-WEEKDAY(DATE(YEAR(TODAY()), 1, 1), 1)+(7*(D5-1))+$D$4