Convert text to date

Posted on 2007-10-16
Last Modified: 2012-06-27
How can I convert Excel Date "1080303"--date displayed here "yy/mm/dd"

to date in format "mm/dd/yy" using any Excel or Access functions.  Access preferable

Cdate not working.
Question by:maximyshka
    LVL 119

    Expert Comment

    by:Rey Obrero
    are you sure you got the right data?
    LVL 3

    Expert Comment

    In a cell or in VBA?

    = date("20" & mid(a1,2,2),mid(a1,4,2),right(a1,2))

    And then just format the cell accordingly ( date or custom yy/mm/dd)

    LVL 3

    Assisted Solution

    That is assuming 1080303 really means 03/03/08 (mm/dd/yy)

    The number 1080303 is not a native excel date, well at least in excel2003 anyway.
    LVL 119

    Expert Comment

    by:Rey Obrero
    is the value for date 03/08/03 in the format of yy/mm/dd

    my guess is that, it is a long integer value. you will need a function to convert that.
    pls answer the question and i will give you the function to convert the value to date

    Author Comment

    Regarding Data.  Yes, that's stupid way how Excel gets data.  I believe they got data probably from Mainframe.
    Basically, you saying that first "1" does not mean anything?

    LVL 119

    Accepted Solution

    try this function

    Function LngInt2Date(ival As Variant) As Long
    Dim sDate As String
    Dim iYr As Integer
    Dim iMon As Integer
    Dim iDay As Integer
        If IsNull(ival) Or Val(ival) = 0 Then Exit Function
        sDate = Right("0" & Trim(Str(ival)), 6)
        If Val(Right(sDate, 2)) < 50 Then
           iYr = 2000 + Val(Right(sDate, 2))
           iYr = 1900 + Val(Right(sDate, 2))
        End If
        iDay = Val(Mid(sDate, 3, 2))
        iMon = Val(Left(sDate, 2))

        LngInt2Date = DateSerial(iYr, iMon, iDay)

    End Function

    if you want the formatting use it this way


    LVL 9

    Expert Comment


    To expand upon the 108:

    Many systems use the syntex of number of years since 1900 when creating a date serial number.  I ran into this with java script in web browsers back in 2003.  I had to either test which browser first or use long date coding.


    Author Comment

    Thank you very much capricorn & ry_ashpool.  

    Featured Post

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    Join & Write a Comment

    In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
    Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
    This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
    This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

    729 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now