Extract week number from a date

[TerryEA]

Hi

Can anyone suggest a simple way of determining a week number in the year (between 1-52) from an inputted date? This is for an accounts application so I would like the year start date, and thus week 1 start date, to be variable.

So for example, if the year start date was 1 August 2006, any date between 08 August 2006 and 14 August 2006 would return the number "2" (ie week 2 in the accounting year).

Hope this makes sense.

Tel

 

[SgtJarrow]

This should work....I didn't try every possibility not does it ensure the yearstart is before the date to check. Let me know if something doesn't seem right.

Public Function GetWeek(myDate As Date, YearStart As Date) As Integer

    GetWeek = Format(myDate, "ww") + IIf(Year(myDate) > Year(YearStart), 52, 1) - Format(YearStart, "ww")

End Function

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB/Access Programmer

 

[TerryEA]

Robert

Thanks for the speedy reply. That looks OK to me. I'll give it a good testing in the application and get back to you tomorrow.

Many thanks again

Tel

 

[Remou]

TerryEA
If you intend that weeks are to be numbered from 1st August regardless of the day, I think you need to use DateDiff, for example:

Function FiscalWeek(dteStart)
If Month(dteDate) < 8 Then
    dteStart = DateSerial(Year(dteDate) - 1, 8, 1)
    FiscalWeek = DateDiff("w", dteStart, dteDate)
Else
    dteStart = DateSerial(Year(dteDate), 8, 1)
    FiscalWeek = DateDiff("w", dteStart, dteDate) + 1
End If
End Function

mstrmage1768
I find that your code returns 31st of July 2006 as week 1, which may or may not be what TerryEA wants.

 

[TerryEA]

Hmmm, interesting

As it happens, in this particular application, any transaction before 1st August 2006 is to be coded as "week 0", and any after are to store the correct week number in the year, according to the date. I know it sounds strange but there it is. Since all the transactions before 1 August 2006 have already beeen entered into the software (and there won't be any more) it really doesn't matter, but of course it would normally.

I'll take a close look at the results of both codings and get back to you. Many thanks for your replies. I'm very grateful.

Tel

 

[TerryEA]

Ok Guys - I've had a good look at the options and it seems that the second, by mstrmage1768, is the preferred on. It seems to return the correct week number for every date, regardless of the financial year and, on reflection, this is going to be the most useful option in my accounting packages generally.

Many thanks to you both for your contributions. What a great place this is!

Tel