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.
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 DATE function returns a number that acts as a date in the Excel environment. The image above shows you [โฆ]
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.
English
Spanish