How to calculate the full WC date from YY-WW?

[shockeroo]

I have weeks in format YY-WW (this week is 09-18) and wish to calculate the full date for the week commencing day for that week.

'09-18 is in cell S8. To work out the date I currently have:

=DATE(LEFT(S8,2),1,RIGHT(S8,2)*7-8)-WEEKDAY(DATE(LEFT(S8,2),1,3))

Which I thought was working fine, but turns out to be the WC date for 1909. I'll probably have to change the -8, that's not a problem, but I can't work out how to get it to 2009.

[JBeaucaire]

Doesn't this do what you want:

=DATE("20"&LEFT(S8,2),1,RIGHT(S8,2)*7-8)-WEEKDAY(DATE(LEFT(S8,2),1,3))

[shockeroo]

Oh-so simple... thank you!

[squiggler47]

you need to add 2000 to your year, date function expects full years!

Please Login or Register  to view this content.

Will return the correct date based on january 3rd, change the 3 to day you want!

more robust as it will work for any year

Please Login or Register  to view this content.

This will return the correct date based on the first weekday in january, so replace Both DayOfWeek with :-

0-Sat,1-Sun....6-Fri

if you want the last weekday in december subtract 7!