WW/MM/YYYY - into Date

[Magee]

Hi All,

Does anyone know how I would be able to convert a cell with the reference WW/MM/YYYY into a date format (ie. the Wednesday of that week.

Example

14/04/2011 is actually week 14 (4-10th April 2011) I would like this to display 06/04/11

?

 

[barry houdini]

Assuming that 14/04/2011 is in A1 and is text formatted try this formula

=DATE(RIGHT(A1,4),1,0)-WEEKDAY(DATE(RIGHT(A1,4),1,3))+LEFT(A1,FIND("/",A1)-1)*7

I'm assuming that your weeks are defined like ISO week numbers

 

[Magee]

You are a genius, thank you