Re: Excel dates seem to confuse me ...

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Myrna Larson (anonymous_at_discussions.microsoft.com)
Date: 08/12/04 

Date: Thu, 12 Aug 2004 17:45:31 -0500

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
    
    Active***.Columns(1).Clear
    With Active***.Cells(1, 1).Resize(ErrCount, 3)
      .Value2 = ErrList()
      .NumberFormat = "0"
      .Columns(1).NumberFormat = "mm/dd/yyyy"
    End With
  End Sub

On Thu, 12 Aug 2004 08:36:28 -0700, Sean Howard <sean.howard@axelero.hu>
wrote:

>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!"
>
>*** Sent via Developersdex http://www.developersdex.com ***
>Don't just participate in USENET...get rewarded for it!

Quantcast