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.

Year	Week	Day	Date
2017	26	Monday
2017	26	Tuesday
2017	26	Wednesday
2017	26	Thursday

<colgroup><col span="2"><col><col span="2"></colgroup><tbody>
</tbody>

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

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet6​

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

<tbody>
</tbody>

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