Find date given day and week
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
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.
