Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    Oct 2002
    Location
    Lakewood, New Jersey, USA
    Posts
    187
    Thanks
    0
    Thanked 0 Times in 0 Posts

    format dates (2000)

    I have a worksheet containing a column of dates. Some are in the format 102103 - i.e. month, day, year without any separation between them, and some are 10/21/2003 - i.e. separated by forward slashes. I want to format all of the cells in this column to contain the forward slashes as in the second example. Is there any simple way for me to do this? If not, and I have to write some VBA code, how do I loop through the cells of a range that has been selected by the user and format each one?

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

    Re: format dates (2000)

    It would help to know the underlying cell formats but this quick and dirty macro should work:

    Sub datefix()
    Dim rngCell As Range
    For Each rngCell In Selection ' this is one way to loop through a user selection
    If CBool(InStr(rngCell.Value, "/")) Then
    rngCell.Value = rngCell.Value ' in case cell format is text
    Else
    rngCell.Value = DateValue(Left(rngCell.Value, 2) & "/" & _
    Mid(rngCell.Value, 2, 3) & "/" & Right(rngCell.Value, 2))
    End If
    Next rngCell
    Selection.NumberFormat = "mm/dd/yy"
    End Sub

    Please test it for all conditions you are encountering. This could also be done with formulas such as =FIND() and =DATEVALUE().
    -John ... I float in liquid gardens
    UTC -7ąDS

  3. #3
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: format dates (2000)

    Hi John,

    I think you'd need to change the Else processing to something like:
    rngCell.Value = DateValue(Left(rngCell.Value, Len(rngCell.Value) - 4) & "/" & _
    Mid(rngCell.Value, 2, Len(rngCell.Value) - 3) & "/" & Right(rngCell.Value, 2))
    so as to cater for Excel's habit of dropping leading 0s.

    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  4. #4
    2 Star Lounger
    Join Date
    Oct 2002
    Location
    Lakewood, New Jersey, USA
    Posts
    187
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: format dates (2000)

    Thanks guys. I appreciate your help. I got the task done by creating a macro similar to John's. I guess there isn't any built-in Excel function that would have done the work for me, huh?

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: format dates (2000)

    If all dates had been in one format, you might have used Data | Text to Columns..., but the mixture of formats makes that impracticable.

  6. #6
    2 Star Lounger
    Join Date
    Oct 2002
    Location
    Lakewood, New Jersey, USA
    Posts
    187
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: format dates (2000)

    Thank you, Hans!! Despite the fact that the column contained dates formatted in 2 different ways (i.e. a) 100103 b)10/1/03), the Text-to-Columns feature worked to convert the entire range to dates. Kudos.

  7. #7
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: format dates (2000)

    Hi Taibe,

    Depending on how your data is produced, you may need to be able to work with any or all of the following:
    01/02/03
    1/02/03
    01/02/2003
    1/02/2003
    010203
    10203
    01022003
    1022003
    In any event, Text-To-Columns would still give the day, month & year in three different columns.

    The following formula in, say, B1:
    =DATEVALUE(LEFT(SUBSTITUTE(A1,"/",""),LEN(SUBSTITUTE(A1,"/",""))-4-(LEN(SUBSTITUTE(A1,"/",""))>6)*2)&"/"&MID(SUBSTITUTE(A1,"/",""),LEN(SUBSTITUTE(A1,"/",""))-3-(LEN(SUBSTITUTE(A1,"/",""))>6)*2,2)&"/"&RIGHT(SUBSTITUTE(A1,"/",""),2))
    will convert the string in A1, which can be in any of the above formats, into a 'proper' Excel datevalue, which you can then format as a date using cell formatting. This gives your whole date in result in a single column instead of spreading it over three.

    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  8. #8
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: format dates (2000)

    Be aware that Text to columns will not properly convert 100103 to the date 10/1/03. It will convert it to 1/25/2174 (displayed as 1/25/74 if you format to two digit years).
    Legare Coleman

Posting Permissions

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