Bug in DatePart-Function -> Format

[poltergeist]

If you test the sub it will give you back the Weeknumber, First and last day of this week. But there is a bug in the DatePart-function and also in format. In the year in which 29 diciember is a monday like 2003 it give Weeknumber 53 and it must be Weeknumber 1.

Private Sub Command1_Click()
Dim WeekNum
Dim FirstDayOfWeek
Dim LastDayOfWeek

If IsDate(Text1.Text) Then
WeekNum = Format(Text1.Text, "ww", vbMonday)
FirstDayOfWeek = Format(((CDate(Text1.Text) - (Weekday(Text1.Text, vbMonday) - 1))), "c&quot
LastDayOfWeek = Format(CDate(FirstDayOfWeek) + 6, "c&quot
MsgBox "Week No" & Chr(9) & WeekNum & Chr(13) & _
"First day" & Chr(9) & FirstDayOfWeek & Chr(13) & _
"Last day" & Chr(9) & LastDayOfWeek
End If
End Sub

So there is e little solution:


Public Function WeeksInYear(Optional ByVal TestDate As Variant, _
Optional ByVal TestYear As Variant, _
Optional ByVal FirstDayOfWeek As VbDayOfWeek _
= vbUseSystemDayOfWeek, _
Optional ByVal FirstWeekOfYear As VbFirstWeekOfYear _
= vbUseSystem) As Integer

Dim nYear As Integer
Dim nDate As Date
Dim nWeek As Integer
Dim nWeekday As Integer

If IsMissing(TestDate) Then
If IsMissing(TestYear) Then
'Error
Else
nYear = TestYear
End If
Else
nYear = Year(TestDate)
End If
nDate = DateSerial(nYear, 12, 31)
Do
nWeek = DatePart("ww", nDate, FirstDayOfWeek, FirstWeekOfYear)
Select Case nWeek
Case 1
nDate = nDate - 1
Case 53
If DatePart("ww", nDate + 7, _
FirstDayOfWeek, FirstWeekOfYear) = 2 Then
WeeksInYear = 52
Else
WeeksInYear = 53
End If
Exit Function
Case 52
WeeksInYear = 52
Exit Function
End Select
Loop
End Function

peterguhl@yahoo.de

 

[DrJavaJoe]

You're saying
WeekNum = Format("12/29/03", "ww", vbMonday) or
WeekNum = Format("12/30/03", "ww", vbMonday) or
WeekNum = Format("12/31/03", "ww", vbMonday)
should not return 53? I don't see why it should return 1.

 

[Golom]

I'm with DrJavaJoe.

Since DatePart or Format operate by counting the number of "firstdayofweek" occurences, ("Monday" in his example), the 53rd Monday (or Tuesday or Wednesday, etc.) should be week 53 and not week 1. Certainly 12/31/2003 is not in the first week of 2003 and its not in 2004 so it can't be the first week there either.

 

[strongm]

It's not really a bug. It is due to the method by which VB calculates date/time intervals, and is documented in the help files; basically, for weeks, by default VB counts the number of Sundays between two dates (although you can modify this through the FirstDayOfWeek and FirstWeekOfYear parameters); in DatePart, VB counts the number of intervals between the 1st Jan of the year in question and the date you have selected.

 

[poltergeist]

I say that

WeekNum = Format("12/29/03", "ww", vbMonday)

must be return 1 and not 53 because there are only 3 days of the week in 2003 and 4 days in 2004. So it must be monday of the first week of 2004 and not monday of the last week of 2003.

peterguhl@yahoo.de

 

[strongm]

Firstly, by that logic you'd also expect:

WeekNum=Format("1/1/03", "ww", vbMonday)

to return 52

Secondly, how do you arrive at the idea that there are only 3 days of December of that final week in 2003? This is an important question, because for your assertion to be correct you have to have decided that Monday is the first day of the week. This is not any sort of hard and fast rule; for example, plenty of people consider Sunday is the first day of a new week (and hence is the default used by VB in it's clculations).

The point is that the system has to be flexible enough to deal with different requirements.

 

[poltergeist]

It's not a rule I've invented. It is a normative of ISO 8601. And this I've taken for basic. And when you see this base the first day of a week is monday and the first week of a year is the week which have 4 or more days since monday.


peterguhl@yahoo.de

 

[strongm]

Firstly, all I was trying to point out was that, just because some people use Monday as the start of the week (whether through personal choice or because of a requirement to adhere to a standard such as ISO 8601 or financial calendars) doesn't mean everyone else has to adhere to that requirement.

Secondly, if you want to try to get VB to adhere more closely with ISO 8601 you probably want to change

Format(Text1.Text, "ww", vbMonday)

to

Format(Text1.Text, "ww", vbMonday, vbFirstFourDays)

Thirdly, the above won't completely fix the problem you have noted since the VB date routines are not compliant with ISO 8601

Fourthly, this isn't a bug (although it might be irritating) since MS have never claimed that their date routines are compliant with that standard

 

[poltergeist]

OK Strongm,

Exactly this is the error:

MsgBox Format("12/29/03", "ww", vbMonday, vbFirstFourDays)

This must give you back 1 and not 53 because there are 4 days of this week in 2004 so 12/29/03 is member of the First Week of 2004.

peterguhl@yahoo.de

 

[strongm]

Um...did you actually read my comments above?

 

[bjd4jc]

poltergeist,

I think that you should take a step back and look at strongm's track record in this forum... You, being a newbie here, are not respecting the what strong is telling you. I encourage you to take a look at strong's profile and read some of his posts. He is very respectible and usually right in what he says 9.9 times out of 10.

 

[poltergeist]

Dear bjd4jc,

I always respect the other opinion and I apreciate Strongm as a great VB Expert. I'm disturbed that you'll terminate a discussion not with a comment about this thread but with the profile of on of the panelist.
Can be possible that the spanish Handbook and VB-Version is different so I'll accept strongm statement.


peterguhl@yahoo.de

 

[DrJavaJoe]

9.9 times out of 10? Show me the .1 time that he's wrong

 

[Golom]

Hate to prolong this agonizing discussion but I just can't resist.

Apparently beauty and weeks in the year are in the eye of the beholder ... and the application that you're trying to write. I don't think that I'll ever write an application where a date in this year is regarded as in the next year ... but that's just me (can't get my mind around that making any real world sense.)

As to the DatePart, Format, etc. discussion. They both work the way the documentation says they work. Given the number of things out there of which that can't be said ... what else can you ask for?

 

[poltergeist]

Ok I know, I should not do this but I still not understand why do you think that it is correct that one week have 1 day and another 6 days when we all know that a week have 7 days.

Private Sub Form_Load()
Dim i As Date
For i = #12/25/2003# To #1/7/2004#
Debug.Print Format(i, "ww", vbMonday, vbFirstFourDays)
Next i
End Sub

This will show you that the week 53 have only one day and the first day 6 days (2 in deciember in 4 in January).

For a comment, we need the weeknumber to programate the empolyers contracts because they are all with week-base and every first week of the year we have to adjust it to an parameter.

peterguhl@yahoo.de

 

[strongm]

I'm beginning to lose the will to live...

Let's ask some fundamental questions:

Fundamental Question 1

1) Would you agree that 31 December 2003 is a day that exists in the year 2003?

If so, would you agree that it must exist in a week that belongs to the year 2003?

If so, would you agree that it might possibly exist in week 53 of that year even if an arbitary standard says it does not?

ISO 8601 is a standard that is designed to make dates unambiguous, not to define what a year actually is. The standard, if you read it, makes this absolutely clear.

Fundamental question 2

If a a week contains a few days from 2003 and a few days from 2004, do you really believe that one year or the other actually has more claim on that week than the other?

Even if you argue that a week has seven days (Doh!), and therefore a year that only has claim to one day of that week is wrong then surely you also have to argue that a year that only has claim on 6 days is equally invalid. Which leads to the situation that there are loads of weeks that are invalid, since no year has a genuine claim on them. Clearly this is ridiculous.

Again, the mere fact that an ISO standard resolves this for the purposes of removing unambiguity doesn't invalidate the argument.

In summary, I'm more than happy for you to say "I need to use a particular date definition standard in my work" but less happy when you say "I use a particular standard in my work, and all the rest of you are doing it wrong, and I can't understand why you can't see it. I'm also rather upset that the computer language I'm using get's it wrong according to my standard, because my standard is right and anything that works differently must, by definition, be wrong. Therefore this must be a bug"