Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Jan 2002
    Posts
    54
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Date Format (97 SR1)

    Is there a way to change a column that has the date entered like this: 20000802, into the date like so: 8/2/2000? Can I do it with a macro or something? When I enter hyphens after the 4th and 6th numbers it will change to the date format. How could I program it to enter these hyphens? I'm a beginner so I would need detailed instructions. Thanks.

  2. #2
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Date Format (97 SR1)

    kanders1

    Happy New year

    Yes you can do it via a macro, or via a formula, faster.

    I am sory my Excel is calculating a huge model at the moment, but look at:

    1) Concatenate function
    2) Mid Function.

    You can do something like Mid(A1,1,4) and this will get you the year them Mid(A1,5,1) will get you the Month, and then Mid(A1,6,2) to get the Day. Then

    Use the concatenate function to assemble them back with / in between.

    HTH

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  3. #3
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Date Format (97 SR1)

    If the date is always in the quoted 8 digit format, this formula (assuming the source cell is A1) will convert it:

    =DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2))

    If you make these conversions frequently a macro would be better.
    -John ... I float in liquid gardens
    UTC -7ąDS

  4. #4
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Date Format (97 SR1)

    Here we go with macro. With Excel open, Alt-F11 opens the Visual Basic Editor. On the left side, the Project Explorer, locate your Personal .xls, create a Module if there isn't one, and copy this text into the code window on the right hand side, save Personal.xls:

    Sub T8Date2DateVal()
    Application.ScreenUpdating = False
    Dim intCalcSetting As Integer
    Dim rngCell As Range
    Dim strCVal As String
    If vbYes = MsgBox("Are you certain you want to convert these dates" & _
    vbLf & "from Text yyyymmdd to Date mm/dd/yyyy?", vbYesNoCancel) Then
    intCalcSetting = Application.Calculation
    Application.Calculation = xlCalculationManual
    For Each rngCell In Selection.SpecialCells(xlCellTypeConstants)
    strCVal = rngCell.Value
    If Len(strCVal) = 8 And Not InStr(strCVal, "/") Then
    rngCell.Value = Left(strCVal, 4) & "/" & Mid(strCVal, 5, 2) & _
    "/" & Right(strCVal, 2)
    rngCell.NumberFormat = "mm/dd/yyyy"
    End If
    Next rngCell
    Application.Calculation = intCalcSetting
    End If
    Application.ScreenUpdating = True
    End Sub
    -John ... I float in liquid gardens
    UTC -7ąDS

  5. #5
    Star Lounger
    Join Date
    Jan 2002
    Posts
    54
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date Format (97 SR1)

    Thank you very much! The macro worked perfectly.

Posting Permissions

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