467,175 Members | 1,322 Online

# Calendar Week Number

 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
• viewed: 14577
Share:
7 Replies
 "Shuffs" 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
 "Shuffs" 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
 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!)? 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" wrote in message news:... 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
 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 offunctions) that on taking a date, will return my Company financialweek 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
 "Shuffs" 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!)? 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 Fletcher Nov 12 '05 #7