[AccessD] Week of the Month - SOLVED

Guys,

Sorry I should have explained myself better I was assuming that January
1st was a Monday etc etc. Here is a cool solution I found at
http://www.cpearson.com/excel/DateTimeVBA.htm

Does what I need:

Public Function YearStart(WhichYear As Integer) As Date

Dim WeekDay As Integer
Dim NewYear As Date

NewYear = DateSerial(WhichYear, 1, 1)
WeekDay = (NewYear - 2) Mod 7 'Generate weekday index where Monday = 0

If WeekDay < 4 Then
    YearStart = NewYear - WeekDay
Else
    YearStart = NewYear - WeekDay + 7
End If

End Function

Public Function ISOWeekNum(AnyDate As Date, Optional WhichFormat As
Variant) As Integer
' WhichFormat: missing or <> 2 then returns week number,
'                                = 2 then YYWW
'
Dim ThisYear As Integer
Dim PreviousYearStart As Date
Dim ThisYearStart As Date
Dim NextYearStart As Date
Dim YearNum As Integer

ThisYear = Year(AnyDate)
ThisYearStart = YearStart(ThisYear)
PreviousYearStart = YearStart(ThisYear - 1)
NextYearStart = YearStart(ThisYear + 1)
Select Case AnyDate
    Case Is >= NextYearStart
        ISOWeekNum = (AnyDate - NextYearStart) \ 7 + 1
        YearNum = Year(AnyDate) + 1
    Case Is < ThisYearStart
        ISOWeekNum = (AnyDate - PreviousYearStart) \ 7 + 1
        YearNum = Year(AnyDate) - 1
    Case Else
        ISOWeekNum = (AnyDate - ThisYearStart) \ 7 + 1
        YearNum = Year(AnyDate)
End Select

If IsMissing(WhichFormat) Then Exit Function
If WhichFormat = 2 Then
    ISOWeekNum = CInt(Format(Right(YearNum, 2), "00") & _
    Format(ISOWeekNum, "00"))
End If

End Function

Thanks for everyones suggestions...

Regards
Vlad

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Gustav Brock
Sent: Friday, 20 June 2003 1:27 AM
To: accessd at databaseadvisors.com
Subject: Re: [AccessD] Week of the Month


Hi Vlad

Are you sure about this? Normally, an ISO standard, a week runs from
Monday to Sunday (except in the US where they start the week in the
middle of the weekend - go figure).

The DatePart() function and Weekday() will give the week and weekdays if
that is what you need (so you say). DatePart() is a little buggy around
week 52 but that won't probably be of importance to you.

/gustav


> Does anyone know of a method to work out what week it is of the month.

> For example, you have a range of  dates 1st January to 31st March. The

> 1st to the 7th is the 1st week of January and the 8th to the 14th is 
> the second etc etc...
 
> I have 2.5 years of data (about 1 Million rows) from an old main frame

> system and we are trying to determine the frequency of delivery to 
> each customer on a per monthly basis. So effectively to say customer 
> Jim Spanner the client delivers every second Thursday for February. Is

> there an elegant method of working this out? I have the date the 
> delivery was carried out for the past 2.5 years, but am having 
> difficulty working out how I could manufacture a frequency pattern.
 
> Is there any method anyone could suggest, that I could explore...

_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com