Convert a weeknumber into a date

[equaliser]

Hi,

I am sent reglarly some data which includes a year field and a week number field, no date. Within a query I'd like to calculate the monday start date of this combination, Year/weeknumber. I've spent hours looking for a solution so hopefully one of you bright sparks might be able to help me.:banghead:

Thanks in advance

John

 

[jdraw]

There is more hidden in your question than you may think.
Here is a link to a standard (ISO) that defines week dates and related info to help with data exchange.

http://en.wikipedia.org/wiki/ISO_8601

You may find more info using Google and look for ISO Working Days or similar.

 

[equaliser]

Yeh I'm aware of that JDraw. In excel I can do what I'm suggesting with the following code.

=DATE($K2,1,1)+(($L2-1)*7)+(2-WEEKDAY(DATE($K2,1,1)))

$k2 is the Year
$L2 is the week Number
By altering the -1 I can adjust when the week starts.

Really I'm just looking for a pointer to the possible calculation I need to put into the query. I want the query idealy to do the work as I'm then feeding the data into excel.

cheers

EQ

 

[recyan]

Till jdraw's back, just check out, if below is what you are looking for :

http://www.access-programmers.co.uk/forums/showthread.php?t=184940

Thanks