Get date from week number 2

[Sjoker]

I want to transform a week number to the date of the friday of that week.
Example: week 37 should become 15 sept 2000.
Is this possible ? [sig][/sig]

 

[kathryn]

Have you looked at the DateAdd and Weekday functions? You can add any number of weeks to a date. January, 2000 began on a Saturday, so adding a week will always return a saturday date (you can use Weekday function to confirm this), so you need to subtract one to get the friday date.

for example:

DateAdd("ww", 1, #1/1/00#) = 1/8/00
Then

dateadd("d",-1,#1/8/00#) = 1/7/00, which is the friday of the first week of the year.

Combining the two:

Dateadd("d",-1, DateAdd("ww", 1, #1/1/00#))

Your function could look like this:

Function GetFriday(intWeek as integer) as date

GetFriday = dateadd('d',-1,dateadd("ww",intweek,#1/1/00#))

end function

This will only work for 2000.
[sig]<p>Kathryn<br><a href=mailto: > </a><br><a href= > </a><br> [/sig]

 

[MichaelRed]

dennie,

just a quick change to kathryn's post will keep it running well past Y2K. Replace &quot;#1/1/00&quot; with (DateValue(&quot;1/1/&quot; & Year(Now()), so the ammended function would be:

Function GetFriday(intWeek as integer) as date

[tab]GetFriday = DateAdd(&quot;d&quot;, -1, DateAdd(&quot;ww&quot;, intWeek, DateValue(&quot;1/1/&quot; & Year(Now()))))

end function

Also, not that the single quotes around the d are repalced w/ double quotes.


[sig]<p>MichaelRed<br><a href=mailto:mred@duvallgroup.com>mred@duvallgroup.com</a><br>There is never time to do it right but there is always time to do it over[/sig]

 

[Sjoker]

Thank you both,it works perfectly. [sig][/sig]