Results 1 to 3 of 3
  1. #1
    4 Star Lounger
    Join Date
    May 2004
    Toronto, Ontario, Canada
    Thanked 0 Times in 0 Posts

    date conversion (Excel 2002)

    Hi all,

    I am tryinig to figure out how to convert a string date with the format of mm/dd/yy into a date serial, but each time I try, I am getting month day year, when it's actually day month year. I am using the Weekday function in code, how would I write it to show day/month/year so that when testing with the weekday function I am getting a return of 1 for January 4 2009, as opposed to 6 April 4 2009?

    Option explicit
    Public Sub list_Report(x As Integer, xy As Integer, ax As Integer)
    Dim MyComment As Comment, filename As String
    Dim mySht As Worksheet
    Dim Ocells As Range
    Dim j As Integer, a As String
    Dim z As Integer
    Dim i As Integer
    Dim lcol As Long
    Dim lrow As Long
    Dim arrparts() As String
    Dim testarray(1) As String
    Dim b As String
    Dim c As Date
    Dim d As Integer
    Dim months As String
    Dim days As String
    Dim years As String
    lcol = x + 5
    lrow = ax
    Do Until i = xy
    Set Ocells = Sheets(2).Cells(lrow, lcol)
    If HasComment(Ocells) Then
    a = VBA.Mid(Ocells.Comment.Text, 1, 100)
    arrparts = Split(a, "+")
    For j = LBound(arrparts) To UBound(arrparts) - 1
    b = VBA.Right(a, 11)
    b = VBA.leftLeft(b, 10)
    days = VBA.Left(b, 2)
    months = Mid(b, 4, 2)
    years = Right(b, 4)
    c = days & "/" & months & "/" & years
    d = VBA.Weekday(c, vbSunday)
    Select Case d
    Case 1
    Userform2.lbSUN.AddItem arrparts(j)
    Debug.Print arrparts(j)
    Case 2
    Userform2.lbMON.AddItem arrparts(j)
    Debug.Print arrparts(j)
    Case 3
    Userform2.lbTUE.AddItem arrparts(j)
    Debug.Print arrparts(j)
    Case 4
    Userform2.lbWED.AddItem arrparts(j)
    Debug.Print arrparts(j)
    Case 5
    Userform2.lbTHU.AddItem arrparts(j)
    Debug.Print arrparts(j)
    Case 6
    Userform2.lbFRI.AddItem arrparts(j)
    Debug.Print arrparts(j)
    Case 7
    Userform2.lbSAT.AddItem arrparts(j)
    Debug.Print arrparts(j)
    End Select
    Next j
    End If
    i = i + 1
    lrow = lrow + 1
    End Sub

    Thank you in advance for your help.

  2. #2
    Uranium Lounger
    Join Date
    Dec 2000
    Salt Lake City, Utah, USA
    Thanked 6 Times in 6 Posts

    Re: date conversion (Excel 2002)

    Darryl, have you tried setting c like this:

    c = DateSerial(years, months, days)

    You have a lot of irrelevent userform code posted, so I'm a bit unclear, but my guess is that your existing line is building a string which then goes though an implicit date conversion using US mm/dd/yyyy format - using DateSerial should correct that. Maybe. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    -John ... I float in liquid gardens
    UTC -7ąDS

  3. #3
    4 Star Lounger
    Join Date
    May 2004
    Toronto, Ontario, Canada
    Thanked 0 Times in 0 Posts

    Re: date conversion (Excel 2002)

    Hey thanks John,

    worked great!


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts