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.

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

Formula in cell D6:

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])

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)

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

Formula in cell 5:

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

Get the Excel file

Find-date-given-day-and-weekv2.xlsx

Back to top

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

---

Weekly Blog EMAIL

[newsletter_signup_form id=1]

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 […]