Excel dates seem to confuse me ...

[Sean Howard]

OK, here goes,

I have to create a function in Excel that shows a date string of the
format "TLyyyymmww" based on the following parameters :
a) "ww" is the week number of the week of that date, all weeks begin on
a Sunday
b) "mm" is the month number of the week described in a)
c) "yyy" is the year number of the week described in a)

What I do not understand is why must I trap the occurance of
"30-Dec-2007". If I do not then then answer will be "TL2007.12.53"
instead of "TL2008.01.01". I know this date does not work correctly but
wondered if anyone knew WHY it must be trapped. and if there anr any
other possible dated that will yield the same error

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Function TILPeriod(xDate As Date) As String
Dim d As Date, w As Integer, y As Integer, m As Integer

d = Int((xDate - vbSunday) / 7) * 7 + vbSunday
w = Format(xDate, "ww", vbSunday, vbFirstFourDays)
y = Year(d)
m = Month(d)

' in case this December week should be
'moved to January next year
If (w = 1) And (m = 12) Then
m = 1
y = y + 1
'WHY !!!!!!!!!!!!!!!!!
ElseIf (d = #12/30/2007#) Then
w = 1
m = 1
y = y + 1
End If

TILPeriod = "TL" & y & "." & Format(m, "00") & "." & Format(w, "00")
End Function
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''


Clear as mud I suppose, but I do not know if this is a coding or
mathematical problem, and any help yould be great


Sean
"Just press the off switch, and go to sleep!"

 

[Myrna Larson]

This looks like a bug to me. I wrote the following little function, then put a
date series starting with 12/23/2007 in A1:A16. In B1, I put the formula

=xWeekNum(A1)

and copied it down.

It gave me 53 for Sunday, 12/30/2007; this 53rd week of 2007 has only 1 day,
and the 1st week of 2008 has only 6 days!

Function xWeekNum(aDate As Date)
xWeekNum = DatePart("ww", aDate, vbSunday, vbFirstFourDays)
End Function

I wrote the following routine to list all weeks from the end of 1900 through
the beginning of 2201 that don't have 7 days. There were 68 weeks that had
only 1 or 6 days.

I have a VBA routine that calculates ISO week numbers. I will try it an see if
it has the same problem. I will post back with the results. If it's OK, I'll
give you that routine.

Option Explicit

Sub FindProblemDates()
Dim d As Double
Dim ErrCount As Long
Dim ErrList() As Double
Dim n As Long
Dim w As Long
Dim PrevWeek As Long

Const Year1 As Long = 1900
Const YearN As Long = 2200

'maximum of 2 errors per year
ReDim ErrList(1 To (YearN - Year1 + 2) * 2, 1 To 3)

'loop until we get to the first week of 2000
d = DateSerial(Year1, 12, 29)
Do Until DatePart("ww", d, vbSunday, vbFirstFourDays) = 1
d = d + 1
Loop

n = 1
PrevWeek = 1
ErrCount = 0

For d = d + 1 To DateSerial(YearN + 1, 1, 10)
w = DatePart("ww", d, vbSunday, vbFirstFourDays)
If w <> PrevWeek Then
'all weeks should have 7 days
If n <> 7 Then
ErrCount = ErrCount + 1
ErrList(ErrCount, 1) = d
ErrList(ErrCount, 2) = PrevWeek
ErrList(ErrCount, 3) = n
End If
n = 1
PrevWeek = w
Else
n = n + 1
End If
Next d

ActiveSheet.Columns(1).Clear
With ActiveSheet.Cells(1, 1).Resize(ErrCount, 3)
.Value2 = ErrList()
.NumberFormat = "0"
.Columns(1).NumberFormat = "mm/dd/yyyy"
End With
End Sub

 

[Myrna Larson]

I've attached code for the ISO weeknumber below. For the date range
end-of-1900 through 1st week in 2201, all weeks have 7 days. But there's a
hitch.... the ISO standard says that weeks begin on *Monday*, not Sunday.

Here's an excerpt from the ISO definition: "Week 01 of a year is per
definition the first week that has the Thursday in this year, which is
equivalent to the week that contains the fourth day of January. In other
words, the first week of a new year is the week that has the majority of its
days in the new year. Week 01 might also contain days from the previous year
and the week before week 01 of a year is the last week (52 or 53) of the
previous year even if it contains days from the new year. A week starts with
Monday (day 1) and ends with Sunday (day 7)."

Here's my code for the ISO week number. As far as starting the week on Sunday,
I *think* it requires only that you change vbMonday to vbSunday in the
ISOYearStart routine, but I haven't tested this.

As you can see from this code, the problem resolves to determining the day on
which Week1 begins. Once you have that, you get the week number by N =
(TheDate - YearStart) \ 7 + 1

Function ISOWeekNum(TheDate As Date, Optional NumFormat As Long = 1) As Long
Dim y As Long
Dim YearStart As Date
Dim NextYearStart As Date
Dim N As Long

y = Year(TheDate)
YearStart = ISOYearStart(y)

NextYearStart = ISOYearStart(y + 1)
If TheDate >= NextYearStart Then
y = y + 1
YearStart = NextYearStart

ElseIf TheDate < YearStart Then
y = y - 1
YearStart = ISOYearStart(y)
End If

N = (TheDate - YearStart) \ 7 + 1

Select Case NumFormat
Case 1: ISOWeekNum = N
Case 2: ISOWeekNum = (y Mod 100) * 100 + N
Case 3: ISOWeekNum = y * 100 + N
End Select
End Function

Function ISOYearStart(TheYear As Long) As Date
Dim Jan4 As Date

Jan4 = DateSerial(TheYear, 1, 4)
ISOYearStart = Jan4 - Weekday(Jan4, vbMonday) + 1

End Function

 

[Myrna Larson]

Here's another test routine that compares the week number returned by VBA's
DatePart with my ISO number routine. I adjusted the latter to start the week
on Sunday rather than Monday.

I found 34 discrepancies in the period from Jan 4, 1900 through Jan 11, 2201.

For Sat Jan 1, 2101, VBA gave 53 and ISO 52; for Sat Jan 3, 2201, results were
54 [sic!] and 53. The other 32 instances all involved the last Sunday of the
calendar year; VBA's result was 53 and the ISO routine gave 1. One of them was
the Dec 30, 2007 date that you've already discovered.

Here's my lastest ISO routine and test code:

Option Explicit

Sub WEEKNOProblems()
Dim d As Date
Dim ErrCount As Long
Dim ErrList() As Double
Dim w1 As Long
Dim w2 As Long

Const Year1 As Long = 1900
Const YearN As Long = 2200

'maximum of 2 errors per year
ReDim ErrList(1 To (YearN - Year1 + 1) * 2 + 2, 1 To 3)

ErrCount = 0

For d = DateSerial(Year1, 1, 4) To DateSerial(YearN + 1, 1, 11)
w1 = DatePart("ww", d, vbSunday, vbFirstFourDays)
w2 = ISOWeekNum(d, vbSunday)
If w1 <> w2 Then
ErrCount = ErrCount + 1
ErrList(ErrCount, 1) = CDbl(d)
ErrList(ErrCount, 2) = w1
ErrList(ErrCount, 3) = w2
End If
Next d

ActiveSheet.Columns(1).Resize(, 3).Clear
If ErrCount = 0 Then
MsgBox "No errors!", vbOKOnly
Else
With ActiveSheet
.Cells(1, 1).Resize(1, 3).Value = Array("Date", "VBA", "ISO")
With .Cells(2, 1).Resize(ErrCount, 3)
.Value2 = ErrList()
.NumberFormat = "0"
.Columns(1).NumberFormat = "mm/dd/yyyy"
End With
End With
End If
End Sub

Function ISOWeekNum(TheDate As Date, _
Optional StartDay As Long = vbMonday, _
Optional NumFormat As Long = 1) As Long

Dim y As Long
Dim YearStart As Date
Dim NextYearStart As Date
Dim n As Long

y = Year(TheDate)
YearStart = ISOYearStart(y, StartDay)
NextYearStart = ISOYearStart(y + 1, StartDay)
If TheDate >= NextYearStart Then 'end of December
y = y + 1
YearStart = NextYearStart
ElseIf TheDate < YearStart Then 'early January
y = y - 1
YearStart = ISOYearStart(y, StartDay)
End If
n = (TheDate - YearStart) \ 7 + 1

Select Case NumFormat
Case 1: ISOWeekNum = n
Case 2: ISOWeekNum = (y Mod 100) * 100 + n
Case 3: ISOWeekNum = y * 100 + n
End Select
End Function

Private Function ISOYearStart(TheYear As Long, _
Optional StartDay As Long = vbMonday) As Date
Dim Jan4 As Date

Jan4 = DateSerial(TheYear, 1, 4)
ISOYearStart = Jan4 - Weekday(Jan4, StartDay) + 1

End Function

 

[Sean Howard]

Hello Myrna

Thanks for the code, I will go through it thoroughly soon. However I
still do not understand why such extensive code, is needed to yield such
a simple result. Is this a bug(!) in Excel, or should all calendars be
changed to "the weeks according to Uncle Bill".



Sean
"Just press the off switch, and go to sleep!"