Convert week number into calendar month?

[Guest]

I'm attempting to calculate a manufacture date from a serial number. The
serial number has the year, week number, and day of the week. For example,
3447 would be decoded like this: 3=year(2003), 44=week number, and
7=weekday(Saturday). Basically, the year and weekday are easy to place within
a formula. It's calculating the month from the week number that I'm having
the problem with. Anyone have any idea as to how I might handle this
situation?

Thanks for your help!

 

[Daniel.M]

Hi,

Depends on your definition of a week (http://www.cpearson.com/excel/weeknum.htm)

Also, assuming you always have YWWD format, so that 10011 would return the first
Sunday of first week of 2009 : Sun 2010-01-03

Something like:

=DATE(INT(A1/1000)+2000,1,1)-WEEKDAY(DATE(INT(A1/1000)+2000,1,))+7*MOD(INT(A1/10
),100)+MOD(A1,10)-1

If it doesn't return the proper results, please provide more data and expected
results.

Regards,

Daniel M.

 

[Daniel.M]

Hi,

Sunday of first week of 2009 : Sun 2010-01-03

I meant: Sunday of first week of 2010

If you don't care about weeks and only want the WW occurence of D (d=1 for Sun
and 7 for Sat) within the year YY (10056) : fifth Friday of Year 2010 : Fri
2010-01-29

=DATE(INT(A1/1000)+2000,1,1)-WEEKDAY(DATE(INT(A1/1000)+2000,1,
1-MOD(A1,10)))+7*MOD(INT(A1/10),100)

Regards,

Daniel M.