Reverse engineer date

[jak82]

Hi,

I know the year, weekof year and day,

How would I go about reverse engineering it to show the date.

[TABLE="width: 349"]
<colgroup><col span="2"><col><col span="2"></colgroup><tbody>[TR]
[TD]Year[/TD]
[TD]Week[/TD]
[TD]Day[/TD]
[TD]Date[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2017[/TD]
[TD]26[/TD]
[TD]Monday[/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2017[/TD]
[TD]26[/TD]
[TD]Tuesday[/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2017[/TD]
[TD]26[/TD]
[TD]Wednesday[/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2017[/TD]
[TD]26[/TD]
[TD]Thursday[/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Any help appreciated.

Thanks

 

[sheetspread]

Barry has a solution here:

https://stackoverflow.com/questions/10995917/how-do-i-convert-a-calendar-week-into-a-date-in-excel

 

[jak82]

Barry has a solution here:

https://stackoverflow.com/questions/10995917/how-do-i-convert-a-calendar-week-into-a-date-in-excel

Thanks Sheetspread,

My Table does not run concurrently and ive noticed when I create a date table in Excel it seems to be a day behind so for example if I was to run the dayname function against todays date excel would say Tuesday?

 

[sheetspread]

That formula actually returns each monday of the week, so a quick adjustment (might not be the most elegant):

Excel 2010
	A	B	C	D
1	Year	Week	Day	Date
2	2017	26	Monday	6/26/17
3	2017	26	Tuesday	6/27/17
4	2017	26	Wednesday	6/28/17
5	2017	26	Thursday	6/29/17
Sheet6

Cell Formulas
Range	Formula
D2	=(DATE(A2,1,-2)-WEEKDAY(DATE(A2,1,3))+B2*7)-2+MATCH(C2,{"Sunday";"Monday";"Tuesday";"Wednesday";"Thursday";"Friday";"Saturday"},0)

 

[Rick Rothstein]

That formula actually returns each monday of the week, so a quick adjustment (might not be the most elegant):

Excel 2010

	A	B	C	D
Year	Week	Day	Date
Monday
Tuesday
Wednesday
Thursday

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]2017[/TD]
[TD="align: right"]26[/TD]

[TD="align: right"]6/26/17[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]2017[/TD]
[TD="align: right"]26[/TD]

[TD="align: right"]6/27/17[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]2017[/TD]
[TD="align: right"]26[/TD]

[TD="align: right"]6/28/17[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]2017[/TD]
[TD="align: right"]26[/TD]

[TD="align: right"]6/29/17[/TD]

</tbody>

Sheet6​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D2[/TH]
[TD="align: left"]=(DATE(A2,1,-2)-WEEKDAY(DATE(A2,1,3))+B2*7)-2+MATCH(C2,{"Sunday";"Monday";"Tuesday";"Wednesday";"Thursday";"Friday";"Saturday"},0)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

You can reduce your formula to two-thirds of its size by replacing the part of your formula that I highlighted in red above with this...

MATCH(LEFT(C2,2),{"Su";"Mo";"Tu";"We";"Th";"Fr";"Sa"},0)

 

[sheetspread]

You can reduce your formula to two-thirds of its size by replacing the part of your formula that I highlighted in red above with this...

MATCH(LEFT(C2,2),{"Su";"Mo";"Tu";"We";"Th";"Fr";"Sa"},0)

I thought about that but was too lazy--typed Monday in a cell, dragged it down to get the remaining days, then got the array of constants in another cell with F9. If you type Su, Mo, the autofill doesn't recognize a pattern apparently. What I really wanted was something like Month(1&April) = 4 but for weekdays, not sure Excel has it (at least in 2010).

 

[Rick Rothstein]

What I really wanted was something like Month(1&April) = 4 but for weekdays, not sure Excel has it (at least in 2010).

Yeah, I am pretty sure no version of Excel would do that. By the way, an even shorter replacement (more than half) of what I highlighted in Message #5 would be this...

=(FIND(LEFT(C2,2),"SuMoTuWeThFrSa")+1)/2

 

[jak82]

Thanks Guys, Will have a look and see If I can get it to work from that.

 

[sheetspread]

=(FIND(LEFT(C2,2),"SuMoTuWeThFrSa")+1)/2

That's the best one I've seen so far