Week commencing from the week number

[paulmcdonnell]

Hi guys...

I am using the week number to find which week entries fall into...

eg. 12 13 14 15 etc

if the week number of the date is = to the week number in the query then the data is selected.

I'm projecting 4 weeks ahead by simply adding 1,2,3 to the current week number.

i.e. if the first week is 12 then the next week is week 13 and the following week is week 14 etc.

For each week number I want to show the date the week commences

Can you do this from the number

So if the week number is 12 then w/c is 14/03/2005

what if the week is 13? How can I find the week commencing date from a week number ?

Hope you can help

Cheers
Paul

 

[The_Doc_Man]

Look up "DateAdd" and use the concept of a known base date plus a known increment in weeks.

I.e. If you know that week 1 started on 3-Jan-05 and you want the start of week 13, add 12 weeks (13 minus 1, since the number is one-based) via the DateAdd function. The result is itself a date.

 

[ColinEssex]

So if the week number is 12 then w/c is 14/03/2005

according to my FiloFax, the monday of week 12 is 21/03/05

Col

 

[paulmcdonnell]

Are you a drummer too or just a nice icon

yes thats not too important.

There is a whole US / Europe thing about when a week actually begins.

you can use which ever.... I think i'm on the US version.


PAUL

 

[ColinEssex]

so have you sorted it yet?

Col

 

[paulmcdonnell]

Sorted?

Not yet but I have some idea of how to that I just haven't tried yet..

 

[thrusk]

Solution

Just came across this & if anyone else is searching for the solution, here is mine.

Function weekCommencing(y As Integer, w As Integer) As Date
Dim addedWeeks As Date
addedWeeks = DateAdd("ww", w, DateSerial(y, 1, 1))
weekCommencing = addedWeeks - Weekday(addedWeeks, vbMonday) + 1
End Function