By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,659 Members | 1,939 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,659 IT Pros & Developers. It's quick & easy.

Calendar Week Number

P: n/a
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
Nov 12 '05 #1
Share this Question
Share on Google+
7 Replies


P: n/a
"Shuffs" <ph****@globalnet.co.uk> wrote in message
news:54**************************@posting.google.c om...
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


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
Nov 12 '05 #2

P: n/a
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" <fl****@home.com> wrote in message news:<bm**********@hercules.btinternet.com>...
"Shuffs" <ph****@globalnet.co.uk> wrote in message
news:54**************************@posting.google.c om...
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


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

Nov 12 '05 #3

P: n/a
"Shuffs" <ph****@globalnet.co.uk> wrote in message
news:54**************************@posting.google.c om...
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

Hi Phil
If I understand you correctly, the first week of a financial year always
contains October 1st in which case your function could be as shown below. I
think it's correct, but before you get into trouble with your finance
department, you might like to try it for yourself.

' ************************************************** ********
Public Function FinancialWeek(dte As Date) As String

Dim dteSat As Date ' The last day of the week
Dim intFinYear As Integer ' The financial year
Dim dteFinStart As Date ' The first date of the financial year

If Month(dte) > 9 Then
intFinYear = Year(dte) + 1
Else
dteSat = dte + 7 - WeekDay(dte, vbSunday)
If dteSat > DateSerial(Year(dte), 9, 30) Then
intFinYear = Year(dte) + 1
Else
intFinYear = Year(dte)
End If
End If

dteFinStart = 1 + DateSerial(intFinYear - 1, 10, 1) - _
WeekDay(DateSerial(intFinYear - 1, 10, 1), vbSunday)

FinancialWeek = intFinYear & "W" & Format((1 + (dte - dteFinStart) \ 7),
"00")

End Function

' ************************************************** ********
Nov 12 '05 #4

P: n/a
Hi Fletcher,

Don't you realise what you've done?
I have been picking this up and putting it down for over two years
with the aim of coming up with a solution!! It was a pet project that
I could take or leave, which fills those moments when I've some time
to tinker!
What am I going to do now, (once I've picked each line apart, to
understand how it works!)? <g>

Joking aside, a sincere thanks for the response and a function to
boot!

Your function is coming up trumps each time I test it, with various
scenarios.

All I can say is a very sincere thank you, and it is very very much
appreciated.

A heartfelt cheers,

Phil
"Fletcher Arnold" <fl****@home.com> wrote in message news:<bn**********@sparta.btinternet.com>...
Hi Phil
If I understand you correctly, the first week of a financial year always
contains October 1st in which case your function could be as shown below. I
think it's correct, but before you get into trouble with your finance
department, you might like to try it for yourself.

' ************************************************** ********
Public Function FinancialWeek(dte As Date) As String

Dim dteSat As Date ' The last day of the week
Dim intFinYear As Integer ' The financial year
Dim dteFinStart As Date ' The first date of the financial year

If Month(dte) > 9 Then
intFinYear = Year(dte) + 1
Else
dteSat = dte + 7 - WeekDay(dte, vbSunday)
If dteSat > DateSerial(Year(dte), 9, 30) Then
intFinYear = Year(dte) + 1
Else
intFinYear = Year(dte)
End If
End If

dteFinStart = 1 + DateSerial(intFinYear - 1, 10, 1) - _
WeekDay(DateSerial(intFinYear - 1, 10, 1), vbSunday)

FinancialWeek = intFinYear & "W" & Format((1 + (dte - dteFinStart) \ 7),
"00")

End Function

' ************************************************** ********

Nov 12 '05 #5

P: n/a
On 20 Oct 2003 02:36:51 -0700, ph****@globalnet.co.uk (Shuffs) wrote:
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.


Did you consider storing this important business data in a table?
That's where data belongs, right?

--
Mike Sherrill
Information Management Systems
Nov 12 '05 #6

P: n/a

"Shuffs" <ph****@globalnet.co.uk> wrote in message
news:54**************************@posting.google.c om...
Hi Fletcher,

Don't you realise what you've done?
I have been picking this up and putting it down for over two years
with the aim of coming up with a solution!! It was a pet project that
I could take or leave, which fills those moments when I've some time
to tinker!
What am I going to do now, (once I've picked each line apart, to
understand how it works!)? <g>

Joking aside, a sincere thanks for the response and a function to
boot!

Your function is coming up trumps each time I test it, with various
scenarios.

All I can say is a very sincere thank you, and it is very very much
appreciated.

A heartfelt cheers,

Phil

Very pleased to hear it's doing what you wanted. It's always nice to get
some feedback - it sure beats a 'thanx in advance'. If it helps, this is
how the function works:

* Find the financial year the date belongs to:
If the month is bigger than 9, add a year.
If not, then if the coming Saturday is in October, add a year - otherwise
the financial year and the calendar year are the same.

* Find date the start of the financial year:
This happens on the Monday of the week containing the 1st October of the
previous year.

* Find the number of days elapsed:

* Divide by 7 (removing any remainder) and add 1 to get the week number

PS
I hope you will be able to find a replacement pet project to tinker with <g>

Fletcher
Nov 12 '05 #7

P: n/a
Thanks for the pointers to what the code does, I've added them as
comments to the function itself!

I've decided that I really do need to improve my VBA skills, and I
need another pet project to accomplish this.

I think I'll have a go at building an Add-In - I'm thinking of a
'Wizard' add-in, one with a spell-checker! Should keep me busy!! <G>

Thanks again!

Phil
"Fletcher Arnold" <fl****@home.com> wrote in message news:<bn**********@titan.btinternet.com>...
"Shuffs" <ph****@globalnet.co.uk> wrote in message
news:54**************************@posting.google.c om...
Hi Fletcher,

Don't you realise what you've done?
I have been picking this up and putting it down for over two years
with the aim of coming up with a solution!! It was a pet project that
I could take or leave, which fills those moments when I've some time
to tinker!
What am I going to do now, (once I've picked each line apart, to
understand how it works!)? <g>

Joking aside, a sincere thanks for the response and a function to
boot!

Your function is coming up trumps each time I test it, with various
scenarios.

All I can say is a very sincere thank you, and it is very very much
appreciated.

A heartfelt cheers,

Phil

Very pleased to hear it's doing what you wanted. It's always nice to get
some feedback - it sure beats a 'thanx in advance'. If it helps, this is
how the function works:

* Find the financial year the date belongs to:
If the month is bigger than 9, add a year.
If not, then if the coming Saturday is in October, add a year - otherwise
the financial year and the calendar year are the same.

* Find date the start of the financial year:
This happens on the Monday of the week containing the 1st October of the
previous year.

* Find the number of days elapsed:

* Divide by 7 (removing any remainder) and add 1 to get the week number

PS
I hope you will be able to find a replacement pet project to tinker with <g>

Fletcher

Nov 12 '05 #8

This discussion thread is closed

Replies have been disabled for this discussion.