Excel: Convert week no. to date

[norty303]

Hi, we've been supplied some data that has week number by financial year within it. We need to create a graph by month and so could do with a formula/solution to converting this data. I realise that week number does not have a year reference which is where i'm getting stuck.


Anyone got any pointers/tips please?

 

[TonyJollans]

Hi norty303,

Can you give a bit more detail please? There is no generic function but I'm sure what you want can be done given details of the format and the year start date.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[norty303]

Thanks for the reply, I've just found out that there are only 137 dates covering June 2002 to Feb 2004 so I told him to do it manually!!! I could understand if there were 1000s but in this case.....

If you could maybe post some pseudo code or the theory/thinking to doing this so I know for next time....

Many thanks

 

[SkipVought]

norty,

This formula might need to be tweeked to yeild the exact week-related date
[tt]
=DATE(B2,1,(A2-1)*7+1)
[/tt]
where column A is the week number and column B is the year.



Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[norty303]

....because the week numbers are by financial year our date column is actually text as e.g.'2002/03'

How should I handle this?

Thanks skip

 

[SkipVought]

Assuming that '2002/03' is in A2
[tt]
=DATE(left(A2,4),1,(right(A2,2)-1)*7+1)
[/tt]


Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[norty303]

Thanks very much for all that Skip. Mucho appreciated