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?
What's on this page
1. Calculate date given weekday and week number (week starts on Sunday)
Formula in cell D6:
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.
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.
Tip! You can shrink the formula if you can convert the weekday to a number yourself.
Weekly Blog 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 […]
Identify overlapping date ranges
The formula in cell F6 returns TRUE if the date range on the same row overlaps another date range in […]
The DATEDIF function in cell E3 allows you to calculate days between two dates. Weekly Blog EMAIL Email Welcome! I […]
Question: I am trying to create an excel spreadsheet that has a date range. Example: Cell A1 1/4/2009-1/10/2009 Cell B1 […]
Find latest date based on a condition
Table of contents Lookup a value and find max date How to enter an array formula Explaining array formula Download […]
Formula for matching a date within a date range
Table of contents Match a date when a date range is entered in a single cell Match a date when […]
Tesh asks: How easy is it to modify this for recurring tasks (weekdays, weekly, monthly, quarterly and yearly) and maybe […]
Geoff asks: Hi Oscar, I have a cross reference table we use for shift scheduling. The x-axis is comprised of […]
This workbook contains two worksheets, one worksheet shows a calendar and the other worksheet is used to store events. The […]
Populate cells dynamically in a weekly schedule
In this post I am going to add one more function to the weekly schedule I built in a previous […]
The image above shows a calendar that is dynamic meaning you choose year and month and the calendar instantly updates […]
I have built a sheet to track time at work. It is very simple, there are 13 sheets, one for each […]
The YEAR function converts a date to a number representing the year in the date. The number is between 1900 […]
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
