Results 1 to 4 of 4
  1. #1
    Lounger
    Join Date
    Feb 2002
    Location
    US
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts

    convert date to number (excel 2000)

    how to convert date to number or number to date in such format below using same function or vba
    2-23-02 to 20223
    3-24-02 to 20324
    or
    20223 to 2-23-02
    20324 to 2-24-02
    any one please help me on this
    than you!!!
    20324 to 3-24-02

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: convert date to number (excel 2000)

    If your date is in A1 then =VALUE(TEXT(A1,"mmddyy")), would return a number comprised of the date elements. Leave out the VALUE fumction and the date is returned as string,

    The revers is a little trickier, as the length of teh number can be 5 or 6. For a value in A1, try

    =IF(LEN(A1)=5,DATE(RIGHT(A1,2),LEFT(A1,1),MID(A1,2 ,2)),DATE(RIGHT(A1,2),LEFT(A1,2),MID(A1,3,2)))

    Andrew C

  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: convert date to number (excel 2000)

    Joe8888, some of what you are asking is unpredictable (and dangerous) because of the leading single digit year (is it 1992 or 2002?), and is also uncertain because I need to tell if "2-23-02 " is really text or a true date, in which latter case the underlying number is treated by Excel as 02-23-2002. (Which reveals my US-centric perspective of date separation slashes, i.e. mm/dd/yy)

    Assuming that the leading year will always be one or two digits, and the second format is a real date, here are conversion macros (with some unnecessary bells and whistles, since I converted them from another one of my own):

    Option Explicit

    Sub XDatetoN()
    '2-23-02 to 20223
    Dim rngCell As Range
    Dim strCVal As String
    Dim intValLen As Integer
    If vbYes = MsgBox("Are you certain your want to convert these dates" & vbLf _
    & " from mm-dd-yy to yymmdd numbers?", vbYesNo, "XL Date to Number") Then
    Application.ScreenUpdating = False
    For Each rngCell In Selection
    strCVal = rngCell.Value
    intValLen = Len(strCVal)
    If intValLen > 10 And strCVal <> "0" Then
    Beep
    MsgBox "Invalid date!", vbExclamation, "XL Date to Number"
    Application.ScreenUpdating = True
    rngCell.Select
    Exit Sub
    End If
    If strCVal <> "0" Then
    rngCell.Value = Right(strCVal, 2) & _
    Left(strCVal, 2) & _
    Mid(strCVal, 4, 2)
    End If
    Next rngCell
    Selection.NumberFormat = "General"
    End If
    Application.ScreenUpdating = True
    End Sub

    Sub NDatetoX()
    '20223 to 2-23-02
    Dim rngCell As Range
    Dim strCVal As String
    Dim intValLen As Integer
    If vbYes = MsgBox("Are you certain your want to convert these numbers" & vbLf _
    & " from yymmdd to mm-dd-yy date?", vbYesNo, "Number to XL Date") Then
    Application.ScreenUpdating = False
    For Each rngCell In Selection
    strCVal = rngCell.Value
    intValLen = Len(strCVal)
    If intValLen > 6 And strCVal <> "0" Then
    Beep
    MsgBox "Invalid date!", vbExclamation, "Number to XL Date"
    Application.ScreenUpdating = True
    rngCell.Select
    Exit Sub
    End If
    If strCVal <> "0" Then
    rngCell.Value = Mid(strCVal, IIf(intValLen = 5, 2, 3), 2) & _
    "-" & Right(strCVal, 2) & "-" & _
    Left(strCVal, intValLen - 4)
    End If
    Next rngCell
    Selection.NumberFormat = "mm-dd-yy"
    End If
    Application.ScreenUpdating = True
    End Sub

    Because of the risks I noted, please test this against a more extensive selection of your own data and see if it bombs on any it shouldn't.

    Usage is to highlight the desired range and run the macro. By design it allows and skips iver any cell whose value is simply zero ("0"). If it finds a value that won't convert, it stops, beeps and selects the miscreant cell. To restart you'll have to skip that cell and select the remaining range.

    You could also do this with formulas, but I get monster reports from my data people with date in yyyymmdd format, and macros are a faster more efficient way to convert.
    -John ... I float in liquid gardens
    UTC -7ąDS

  4. #4
    Lounger
    Join Date
    Feb 2002
    Location
    US
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: convert date to number (excel 2000)

    =VALUE(TEXT(A1,"mmddyy")) is the one I needed. thank you all for the help.

Posting Permissions

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