Hi Fletcher,
Many thanks for the reply and additional WeekNumber information.
I've re-read my original post and can now see how it is unclear -
apologies for that.
The original code I posted returns week 39 for the date 28/09/2003
(28th September 2003 - UK date format) - this is indeed correct, as it
works to the ISO standard.
What I wanted to do was take the code, and change the variables of
StartDay and StopDay, so that Sunday would be classed as the first day
of the week, not a Monday. Doing this in my code didn't give me what I
wanted, understandbly because firstly, I didn't cotton on to the ISO
standard bit and I'm not really the sharpesty tool in the box when it
comes to writing functions!
With hindsight, I should've looked up ISO8601! If this function works
to a particular standard then it will return the value to that
standard - doing exactly as it says on the tin!
What I am ultimately trying to achieve is a function (or set of
functions) that on taking a date, will return my Company financial
week number and year.
Our financial year runs from October to September, our trading week
runs from Sunday to Saturday.
Therefore, Saturday 27th 2003 is the 52nd week of Financial Year 2003,
28th September 2003 is the 1st week of Financial Year 2004.
I thought that if I could modify the original code I posted, I could
be on the first steps to doing this! I should of looked a bit deeper
than I actually did!
Anyhow, thanks again for the response - I now know I need to look
again at this!
Cheers,
Phil
"Fletcher Arnold" <fletch@home.com> wrote in message news:<bmv8vn$kai$1@hercules.btinternet.com>...[color=blue]
> "Shuffs" <phil03@globalnet.co.uk> wrote in message
> news:54947ba2.0310160813.20297a42@posting.google.c om...[color=green]
> > Could someone, anyone please tell me what I need to amend, to get this
> > function to take Sunday as the first day of the week?
> >
> > I amended the Weekday parts to vbSunday (in my code, not the code
> > attached), yet when I ran it for 28/09/2003 (UK date format) it
> > returned Week 39. I would have expected it to return Week 40.
> > However, I'm really stuck and my head is busting over this, so any
> > pointers would be gratefully appreciated.
> >
> > Many thanks in advance
> >
> > Cheers,
> >
> > Phil
> >
> >
> >
> > '================================================
> > 'Function obtained from Microsoft Knowledge Base
> > 'Q200299. It returns correct Weeknumbers as per ISO 8601
> > 'and works around known week numbering bug in Access
> > '================================================
> > Function WeekNumber(InDate As Date) As Integer
> > Dim DayNo As Integer
> > Dim StartDays As Integer
> > Dim StopDays As Integer
> > Dim StartDay As Integer
> > Dim StopDay As Integer
> > Dim VNumber As Integer
> > Dim ThurFlag As Boolean
> >
> > On Error GoTo HandleErr
> > DayNo = Days(InDate)
> > StartDay = WeekDay(DateSerial(Year(InDate), 1, 1)) - 1
> > StopDay = WeekDay(DateSerial(Year(InDate), 12, 31)) - 1
> > ' Number of days belonging to first calendar week
> > StartDays = 7 - (StartDay - 1)
> > ' Number of days belonging to last calendar week
> > StopDays = 7 - (StopDay - 1)
> > ' Test to see if the year will have 53 weeks or not
> > If StartDay = 4 Or StopDay = 4 Then ThurFlag = True Else ThurFlag =
> > False
> > VNumber = (DayNo - StartDays - 4) / 7
> > ' If first week has 4 or more days, it will be calendar week 1
> > ' If first week has less than 4 days, it will belong to last year's
> > ' last calendar week
> > If StartDays >= 4 Then
> > WeekNumber = Fix(VNumber) + 2
> > Else
> > WeekNumber = Fix(VNumber) + 1
> > End If
> > ' Handle years whose last days will belong to coming year's first
> > ' calendar week
> > If WeekNumber > 52 And ThurFlag = False Then WeekNumber = 1
> > ' Handle years whose first days will belong to the last year's
> > ' last calendar week
> > If WeekNumber = 0 Then
> > WeekNumber = WeekNumber(DateSerial(Year(InDate) - 1, 12, 31))
> > End If
> > ExitHere:
> > Exit Function
> >
> > HandleErr:
> > Select Case Err.Number
> > Case Else
> > MsgBox "Error " & Err.Number & ": " & Err.Description,
> > vbCritical, "Form_frmBfPickADate.WeekNumber"
> > End Select
> >
> > End Function
> >
> > '================================================
> >
> > 'Function obtained from Microsoft Knowledge Base
> > 'Q200299
> > 'Called by Weeknumber()
> > '================================================
> >
> > Function Days(DayNo As Date) As Integer
> > On Error GoTo HandleErr
> > Days = DayNo - DateSerial(Year(DayNo), 1, 0)
> > ExitHere:
> > Exit Function
> >
> > HandleErr:
> > Select Case Err.Number
> > Case Else
> > MsgBox "Error " & Err.Number & ": " & Err.Description,
> > vbCritical, "Form_frmBfPickADate.Days"
> > End Select
> >
> > End Function[/color]
>
>
>
> Hi Phil
>
> No-one else seems to have answered - and I am a bit puzzled too. You
> changed the code for the function and now it no longer works as expected?
> So why did you change the code?
> The code says in its description that it is working to the ISO 8601
> standard - an internationally accepted standard which can only return one
> possible week number for the date of September 28, 2003. That is week
> number 39. The standard says that Week No 1 contains the fourth day of
> January and you can count from there. I have my own version of this
> function, but I have no reason to suspect this one is flawed. If it helps
> at all, I have included a printout for 2003, but perhaps you can let us know
> what value you wanted the function to return for the above-mentioned date
> and which standard you are working to.
>
> HTH
>
> Fletcher
>
> 30 31 01 02 03 04 05 2003W01 Jan-03
> 06 07 08 09 10 11 12 2003W02
> 13 14 15 16 17 18 19 2003W03
> 20 21 22 23 24 25 26 2003W04
> 27 28 29 30 31 01 02 2003W05 Feb-03
> 03 04 05 06 07 08 09 2003W06
> 10 11 12 13 14 15 16 2003W07
> 17 18 19 20 21 22 23 2003W08
> 24 25 26 27 28 01 02 2003W09 Mar-03
> 03 04 05 06 07 08 09 2003W10
> 10 11 12 13 14 15 16 2003W11
> 17 18 19 20 21 22 23 2003W12
> 24 25 26 27 28 29 30 2003W13
> 31 01 02 03 04 05 06 2003W14 Apr-03
> 07 08 09 10 11 12 13 2003W15
> 14 15 16 17 18 19 20 2003W16
> 21 22 23 24 25 26 27 2003W17
> 28 29 30 01 02 03 04 2003W18 May-03
> 05 06 07 08 09 10 11 2003W19
> 12 13 14 15 16 17 18 2003W20
> 19 20 21 22 23 24 25 2003W21
> 26 27 28 29 30 31 01 2003W22 Jun-03
> 02 03 04 05 06 07 08 2003W23
> 09 10 11 12 13 14 15 2003W24
> 16 17 18 19 20 21 22 2003W25
> 23 24 25 26 27 28 29 2003W26
> 30 01 02 03 04 05 06 2003W27 Jul-03
> 07 08 09 10 11 12 13 2003W28
> 14 15 16 17 18 19 20 2003W29
> 21 22 23 24 25 26 27 2003W30
> 28 29 30 31 01 02 03 2003W31 Aug-03
> 04 05 06 07 08 09 10 2003W32
> 11 12 13 14 15 16 17 2003W33
> 18 19 20 21 22 23 24 2003W34
> 25 26 27 28 29 30 31 2003W35
> 01 02 03 04 05 06 07 2003W36 Sep-03
> 08 09 10 11 12 13 14 2003W37
> 15 16 17 18 19 20 21 2003W38
> 22 23 24 25 26 27 28 2003W39
> 29 30 01 02 03 04 05 2003W40 Oct-03
> 06 07 08 09 10 11 12 2003W41
> 13 14 15 16 17 18 19 2003W42
> 20 21 22 23 24 25 26 2003W43
> 27 28 29 30 31 01 02 2003W44 Nov-03
> 03 04 05 06 07 08 09 2003W45
> 10 11 12 13 14 15 16 2003W46
> 17 18 19 20 21 22 23 2003W47
> 24 25 26 27 28 29 30 2003W48
> 01 02 03 04 05 06 07 2003W49 Dec-03
> 08 09 10 11 12 13 14 2003W50
> 15 16 17 18 19 20 21 2003W51
> 22 23 24 25 26 27 28 2003W52
> 29 30 31 01 02 03 04 2004W01 Jan-04[/color]
