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 ?

Answer:

Formula in cell D6:

Formula in cell D13:

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.

-WEEKDAY(DATE(YEAR(TODAY()), 1, 1), 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) 

+(7*(D5-1))

Example, 7*(32-1)

becomes

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.

---

Weekly Blog EMAIL

Email

Welcome! I am Oscar and here to help you out.

Feel free to comment and ask Excel questions.

Make sure you subscribe to my newsletter so you don't miss new blog articles.

Related articles

---

Count a specific weekday in a date range

NETWORKDAYS function returns the number of whole workdays between two dates, however the array formula I am going to demonstrate […]

Find date range based on a date

This article demonstrates a formula that returns a date range that a date falls under, cell C3 above contains the […]