Finding a specific date from a week no.

[Guest]

Is there a formula to find a specific date from a week no?

For example Week 42 day 2 (Monday) would result 16/10/06

Regards

 

[moon]

Is there a formula to find a specific date from a week no?

For example Week 42 day 2 (Monday) would result 16/10/06

Regards

Put weeknumber 42 in Cel A2 and daynumber 2 in Cel B2, then try this one.

=DATE(2006;12;31)-(365-(((365/52)*A2)-(IF((7-B2)>0;7-B2;B2-7))))

Not sure, I only tested 10/16/06, 01/01/01 and 12/31/06 with a positive
result.

 

[moon]

correction, pasted the wrong one

=DATE(2006;12;31)-(365-(((365/52)*A2)-(IF((7-B2)>0;7-B2;31-B2))))

 

[Guest]

A5: year
B5: week No
C5: day of week

=DATE(A5,1,1)+(B5-1)*7+C5

Regards,
Stefi

„moon†ezt írta:

 

[Roger Govier]

Hi

With A1 holding the first Monday of the Year required e.g 02 Jan 2006
and A2 holding the week number then
=A1+(A2-1)*7

 

[Niek Otten]

<the first Monday of the Year required >



Which is:



=DATE(Yr,1,1+(Nth-(Dow>=WEEKDAY(DATE(Yr,1,1))))*7)+ Dow-WEEKDAY(DATE(Yr,1,1))



Where Dow = 2.



From Chip Pearson: http://www.cpearson.com/excel/DateTimeWS.htm#LastWeekday




--
Kind regards,

Niek Otten
Microsoft MVP - Excel


| Hi
|
| With A1 holding the first Monday of the Year required e.g 02 Jan 2006
| and A2 holding the week number then
| =A1+(A2-1)*7
|
| --
| Regards
|
| Roger Govier
|
|
| | > Is there a formula to find a specific date from a week no?
| >
| > For example Week 42 day 2 (Monday) would result 16/10/06
| >
| > Regards
|
|