La Thăng asks:

I want to find day if given date and week, for example : if given Tuesday, 32th week, 2015 >>> how to use function to point out 4/8/2015 ?


Calculate date given day and weeknumber

Formula in cell D6:

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

Formula in cell D13:

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

Explaining formula in cell D6

Step 1 - Calculate the first date of this year

DATE(YEAR(TODAY()), 1, 1) returns 42005

Step 2 - Subtract with the corresponding weekday number

The weekday function converts a date to a number ranging from 1 to 7, depending on when the week starts.

WEEKDAY(serialnumber, return_type)

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


becomes WEEKDAY(42005, 1)

and returns 5. 42005 is 2015-01-01 and that day is a thursday.

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


Example, 7*(32-1)


7*31 and returns 217.

Step 4 - Calculate and add weekday number

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.

Step 5 - Add and subtract

42005-5+217+3 = 42220

42220 is 2015-08-04

Download excel *.xlsx file

Find date given day and week.xlsx

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