Not able to convert text dates to system dates
Results 1 to 5 of 5

Thread: Not able to convert text dates to system dates
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Feb 2008
    Posts
    302
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Not able to convert text dates to system dates

    Hello Excel Friends,

    I am in strange situation where I have a data with Invoice Dates. These dates are in Text. However, when I convert it into reall excel dates. For some reason the dates do not come right. I am looking for help to convert my text dates into excel real dates. I did a lot of google search and apply these three methods but all of these gave me strange result I was not expecting. These are the methods I used and the result of each method.

    Method 1 : Using Text to Column Wizard (Excel 2007)
    I highlighted invoice column and went into text to column wizard. I clikced on Date button, and selected MDY format. Strangely enough, my result was day, month and year i.e. DMY which does not seem right.
    Then I press Control ~ on this data as shown in second view.

    Inv DateMDY
    09/22/1122/09/2011
    08/31/1131/08/2011
    08/31/1131/08/2011
    08/31/1131/08/2011
    08/31/1131/08/2011


    control ~ (overview)
    Inv DateMDY
    09/22/1140808
    08/31/1140786
    08/31/1140786
    08/31/1140786
    08/31/1140786


    2nd Method - Using formula to convert text date to real dates
    When I used formula, I get the following result. As you can see, formula is converting text dates into different system dates than the first method.
    Further strange things is when I take these system dates i.e. 4283 and in 2007 excel format use "Short Date" the same system date gets converted into wrong year as shown in the second view.


    Inv DateFormulaFormula Result
    09/22/11=DATE(MID(B2,7,2),MID(B2,1,2),MID(B2,4,2))4283
    08/31/11=DATE(MID(B3,7,2),MID(B3,1,2),MID(B3,4,2))4261
    08/31/11=DATE(MID(B4,7,2),MID(B4,1,2),MID(B4,4,2))4261
    08/31/11=DATE(MID(B5,7,2),MID(B5,1,2),MID(B5,4,2))4261
    08/31/11=DATE(MID(B6,7,2),MID(B6,1,2),MID(B6,4,2))4261
    08/31/11=DATE(MID(B7,7,2),MID(B7,1,2),MID(B7,4,2))4261


    2nd view - system dates are getting converted into 1911
    Inv DateFormulaFormula Result
    09/22/11428322/09/1911
    08/31/11426131/08/1911
    08/31/11426131/08/1911
    08/31/11426131/08/1911
    08/31/11426131/08/1911
    08/31/11426131/08/1911


    Similarly, I tried other methods or copying blanks etch but none seems to convert text dates into real (system dates).
    Does anyone know what my problem is? Thanks.

  2. #2
    MrExcel MVP shg's Avatar
    Join Date
    May 2008
    Location
    The Great State of Texas
    Posts
    21,588
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    13 Thread(s)

    Default Re: Not able to convert text dates to system dates

    The first instance is doing the conversion correctly; just change the format to mm/dd/yyyy.

    I didn't look at the others.

  3. #3
    Board Regular
    Join Date
    Mar 2005
    Location
    Washington state
    Posts
    177
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Not able to convert text dates to system dates

    The method I use is the DateValue formula. like...

    =DATEVALUE($P$9)

    where P9 contains a text date.
    n0gel
    All things curious

  4. #4
    Board Regular T. Valko's Avatar
    Join Date
    May 2009
    Location
    Pittsburgh
    Posts
    16,623
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Not able to convert text dates to system dates

    Quote Originally Posted by irfananeeza View Post
    Hello Excel Friends,

    I am in strange situation where I have a data with Invoice Dates. These dates are in Text. However, when I convert it into reall excel dates. For some reason the dates do not come right. I am looking for help to convert my text dates into excel real dates. I did a lot of google search and apply these three methods but all of these gave me strange result I was not expecting. These are the methods I used and the result of each method.

    Method 1 : Using Text to Column Wizard (Excel 2007)
    I highlighted invoice column and went into text to column wizard. I clikced on Date button, and selected MDY format. Strangely enough, my result was day, month and year i.e. DMY which does not seem right.
    Then I press Control ~ on this data as shown in second view.

    Inv DateMDY
    09/22/1122/09/2011
    08/31/1131/08/2011
    08/31/1131/08/2011
    08/31/1131/08/2011
    08/31/1131/08/2011


    control ~ (overview)
    Inv DateMDY
    09/22/1140808
    08/31/1140786
    08/31/1140786
    08/31/1140786
    08/31/1140786


    2nd Method - Using formula to convert text date to real dates
    When I used formula, I get the following result. As you can see, formula is converting text dates into different system dates than the first method.
    Further strange things is when I take these system dates i.e. 4283 and in 2007 excel format use "Short Date" the same system date gets converted into wrong year as shown in the second view.


    Inv DateFormulaFormula Result
    09/22/11=DATE(MID(B2,7,2),MID(B2,1,2),MID(B2,4,2))4283
    08/31/11=DATE(MID(B3,7,2),MID(B3,1,2),MID(B3,4,2))4261
    08/31/11=DATE(MID(B4,7,2),MID(B4,1,2),MID(B4,4,2))4261
    08/31/11=DATE(MID(B5,7,2),MID(B5,1,2),MID(B5,4,2))4261
    08/31/11=DATE(MID(B6,7,2),MID(B6,1,2),MID(B6,4,2))4261
    08/31/11=DATE(MID(B7,7,2),MID(B7,1,2),MID(B7,4,2))4261


    2nd view - system dates are getting converted into 1911
    Inv DateFormulaFormula Result
    09/22/11428322/09/1911
    08/31/11426131/08/1911
    08/31/11426131/08/1911
    08/31/11426131/08/1911
    08/31/11426131/08/1911
    08/31/11426131/08/1911


    Similarly, I tried other methods or copying blanks etch but none seems to convert text dates into real (system dates).
    Does anyone know what my problem is? Thanks.
    Are the date years always in the 2000 decade or later?

    If so, just add 2000 to the YEAR function like this:

    =DATE(MID(B2,7,2)+2000,MID(B2,1,2),MID(B2,4,2))
    .
    Biff
    Microsoft MVP - Excel

    Don't be afraid to use volatile functions or array formulas
    Tell us what version of Excel you're using
    KISS - Keep It Simple Stupid

  5. #5
    Board Regular
    Join Date
    Feb 2008
    Posts
    302
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Not able to convert text dates to system dates

    Quote Originally Posted by shg View Post
    The first instance is doing the conversion correctly; just change the format to mm/dd/yyyy.

    I didn't look at the others.
    Thanks for looking into it. What is the way to manually change the date fromat to mm/dd/yyyy?

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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