Comments on: How to convert text to date and number to date in Excel

The tutorial explains how to use Excel functions to convert text to date and how to turn text strings into dates in a non-formula way. You will also learn how to quickly change a number to date format. Continue reading →

Comments page 19. Total comments: 731

  1. i have a thousand of date, all in the date format, but some people key in dd/mm/yy, some people key in mm/dd/yy, both also recognize as correct format in the excel, however, i know some of the date is wrongly key in, so i use "text to column function change it, but unfortunately, the correct date will automatic change to incorrect result.

    Eg,
    1/3/2015(read as 1 march 2015-original correct) after change become 3/1/2015)
    3/7/2015(should read as 7 march 2015-original wrong)after change bcm 3/7/2015)

    Any solution to change all one shot into same date format as dd/mm/yy?

  2. how can i convert this date 10111989 to this 10/11/1989
    i tried but i could not ,so could you help me

    1. Hi Ahmed,

      You can do it using the following formula:

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

      Where A1 is a cell containing the original date.

  3. Apologies Svetlana. I tried it on a blank worksheet by inserting 2015-01, 2015-02 and 2015-03 and the formula you gave me to separate the month works correctly. I can therefore only assume that my spreadsheet is somehow corrupted, although I cannot visibly see it. I will have to figure out how to overcome this. Thank you so much for your help.

  4. Thank you for your prompt response Svetlana. The separation of the year works correctly, but for some reason the month part always shows December. I tried it on 2015-01, 2015-02 and 2015-03 and they all show the month as December instead of January, February and March. Is it because it is treating the hyphen as part of the month? How can I get around this problem?

  5. I have dates in the format yyyy-mm (e.g. 2015-01, 2015-02, 2015-03, etc). These dates are currently in cells with text format. I would like to split the dates and store the years as 2015, 2016, etc) in separate cells and I would like to store the months as: "January" instead of 01, "February" instead of 02, "March" instead of 03, etc in separate cells. Could you please show me how this can be done automatically. Thank you for any help you can give.

    1. Hi Avnish,

      You can use the following formulas, where A1 is a cell with a date:

      To extract a year: =LEFT(A1, 4)

      To extract a month: =TEXT(DATE(LEFT(A1, 4), RIGHT(A1, 2), 1), "mmmm")

  6. Thanks a ton. !! this saved 4 hrs of work of mine. Got it done in 5 mins instead !

  7. Very well illustrated. Great... Found the answer here for a date format that was in text format in google sheets. Tried all tricks including text to columns and then didn't know how to proceed. Date function helped it. Liked the different problems associated with this crazy dates and the solutions you have for them.

  8. please help me in converting this 2016-01-06T10:18:36.983+05:30 to values (ex:1440684229) Unix time

    thanks & regards
    Santosha DS

  9. Hi
    I have column of duration in time formate 00:00:00
    now i want to concate a text to it result should like this time="00:00:00"
    for this i am using CONCATENATE
    but result is coming like this time"0.001335343545"
    I want the time should be converted as it in string formate

    1. Got the solution
      Text(A1, "h:mm:ss")

  10. Very useful article. I used the method where I first change from text to number, and then change format of the number to date. However, then I wanted to delete the column which has the "text" dates. On doing so, the date column (one in real "date" format) loses its data too, because it is dependent on the data from the "text dates". How can I delete the "text date" column and still retain the newly created date column? Thanks.

    1. Hi PM,

      You simply need to replace formulas with their calculated values. To do this, select the entire date column, press Ctrl+C to copy it, then right-click the selection and click Paste Special > Values. Done!

  11. sir

    excel date format corrupted

    ex: 20141027

    how to recover it please help me sir

  12. 1/2/2015 12:13:48 PM
    31/03/2015 08:53:45

    How i will convernt into month-year
    E.G. Jan-2015

    1. Hello Sachin,

      To keep the source data and change only the display format, you can select the cells, press Ctrl+1 to open the Format Cells dialog, select custom under Category on the left pane, and type mmm-yyyy in the Type box.

      To convert the dates to text, use the following formula:
      =TEXT(A1, "mmm-yyyy")

      Where A1 is the source date you want to convert.

  13. eg between dates;
    01/01/2016 and 31/01/2016 = Jan 16
    01/02/2016 and 28/02/2016 = Feb 16
    01/03/2016 and 31/03/2016 = Mar 16
    ... till Dec.

    How can I perform this using excel?

    Many thanks.
    Jose

    1. Hi Jose,

      You can use the following formula to convert your dates to text in the specified format:
      =TEXT(A1, "mmm yy")

      Or, to change only the display format, select the cells, right-click, then click Format Cells... and set custom format to mmm yy.

      The difference is that the formula converts a date to a text value, while setting a custom format to a cell keeps the original value and changes only the visual representation of a date.

  14. Very good site with lot of information.

  15. Hi,
    I have copied data across into the spreadsheet. Eg:
    7-01
    6-04

    and its copying across as 7-Jan & 6-Apr. How can I stop this?
    Thank you

    1. Hello Mona,

      Set the format of destination cells to "Text" and then paste the data.

  16. I need help. I already have a date say - 01 Apr 2016. Now I need to add 18 moths to this date. How do i do it in Excel. The result should be 01 Sep 2017.

    1. Hi Raman,

      You can use the following formula (where A2 is the source date):

      =DATE(YEAR(A2), MONTH(A2) + 18, DAY(A2))

  17. Hi there,

    I am using Text to Column function to change the dates from 20160101 to look like this 01/01/16. The only problem is, its picking the formula and not the date itself. Just to describe it a bit better, all my dates are coming from another source. Is there any way I can change 20160101 to 01/01/16.

    Thanks

      1. That didn't work for me. Any other ideas?

        1. Hi Hafiz,

          From your example, it's not quite clear which of the two 01 is month and which is day.

          Assuming your source dates (20160101) are in the format "yyyymmdd", you can use the following formula:
          =DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2))

          If it is the "yyyyddmm" format, then change the order of the last 2 arguments:
          =DATE(LEFT(A1,4),RIGHT(A1,2),MID(A1,5,2))

          Both formulas return serial numbers representing the dates in the internal Excel system. To have the output formatted as date, select a cell with the formula, right click, click "Format Cells", and choose the date format you want.

          1. i got great problem to covert the format of date entered in Gregorian to date can you help me please 10Q

  18. how i can convert 6 to 06:00:00 format though excel

    please let me know any one..

    1. Hi Deepak,

      First, divide 6 by 24 (there are 24 hrs in a day). The formula can be as simple as =A1/24. This will yield the decimal value (for 6 the result will be 0.25).

      Now, select the cell with the formula result, right-click and select Format Cells from the context menu. The Format Cells dialog will appear, you choose Time on the left pane, and the format you want on the right pane under Type. Please see the following example for full details: How to convert numbers to time format in Excel.

  19. Date 07/04/2014
    Date 19/07/2014
    Accounting days Excel #value!

  20. 20-02-1191-20-02-2015=???? What is the formula?

    Plz Urgent

    Regards,
    Naiyar

  21. hi,
    i have problem on date function,
    A1 hv month name, and cel B1 have year(2015),how i can get display as first of particular month date and date. for example.
    APRIL 2015. i want to display this as 01/04/2015,
    pls help anyone

  22. I am having some problems with Excel dates.
    The dates are stored in a large Excel spreadsheet with 537 rows. I want to to import this Microsoft Access but the dates are incorrectly converted. The data in the column is stored in the following formats in the same column.

    Custom format: d/mmm/yy and mmm/yy

    Result of import: These transfer in Microsoft Access correctly.

    General format: no format any numeric or text value can be entered.

    Forexample dates stored as general format:
    2011 2011 give 03/07/1905 03/07/1905.

    I have tried using the maths option (1*A2), DATEVALUE(), VALUE() options and these did not work.

    Could you tell me the best technique in coverting these dates?

  23. Hello!
    So I have this annoying problem, i need to change my "text" date to some kind of date format with QUARTERS so the data mining add in could recognize it as time stamp.
    My date is in format like "2007_09", I managed to get in format "2007 Q3",but still it does the trick only for selective analysis not data mining.

    I was wondering that i could format at least the year part as date, but the DATE function does not work because i need month and day as well :(.

    Do you have any ideas?

  24. I want to convert a series of 6 numbers to a mm/dd/yyyy format automatically when I type the series of numbers into the cell. Example
    120457 I want the cell to display the result as 12/04/1957. Please help.

  25. I Have various columns with different interpretations of the date and time.. working with 10,000 lines is there a formula to make them all the same.. original file is a CSV download.

    10/02/2015 19:30 13/10/2015 06:00:00
    10/04/2015 2:30 10/11/2015 18:30
    10/01/2015 19:00 10/10/2015 19:00
    10/05/2015 1:30 13/10/2015 19:05:00
    10/06/2015 2:00 13/10/2015 02:00:00
    10/09/2015 1:30 17/10/2015 14:10:00
    10/03/2015 3:30 14/10/2015 06:43:00
    10/08/2015 15:30 10/11/2015 15:30
    10/09/2015 0:00 10/10/2015 00:00
    10/07/2015 23:00 10/11/2015 16:00
    10/03/2015 5:00 10/10/2015 16:00
    10/07/2015 23:30 15/10/2015 08:00:00
    10/08/2015 23:00 17/10/2015 05:20:00

  26. how to deal with this data. i want uniform data.in date format.
    ACTIVE_DATE
    3/28/2006
    3/28/2006
    3/28/2006
    3/28/2006
    3/28/2006
    3/28/2006
    3/28/2006
    3/28/2006
    3/28/2006
    37382
    38934
    39114
    39114
    39114
    39114
    39114
    39114
    39114
    39114
    39114
    39114
    39114
    39114
    39114
    39114
    39114
    39114
    39063
    7/22/2000
    7/22/2000
    7/22/2000
    7/22/2000
    7/22/2000
    7/22/2000
    3/26/1998
    3/26/1998
    3/26/1998
    36561
    36561
    36561
    36561
    36561
    36561
    36561
    36561
    36561
    36561
    36561
    36561
    36561
    36561
    36561
    36561

  27. Hallo.

    I have problem, with automatic conversion of typed vallues in Excell 2003. When i type 31-8 excell not change vallue in to date, its still text. When i set cell to date format its still text. When i type 10 its converted to 10 1 1900. This appear after i installed SP.
    How to activate automatic conversion func?

  28. 20150805
    20150806
    I want to showing is
    05/08/2015
    06/08/2015

  29. Hi How do I group different dates in a month to a single format.

    Eg
    01/03/2015
    02/03/2015
    03/03/2015

    I want all to show Mar-15, not showing the day in the pivot table.

  30. how to convert date to date value format?

    1. use this formula.

  31. I want to convert a timestamp of 81215182533 to 8/12/15 18:25:33. How do I do that?
    Thanks

    1. Reed, this is probably way too late to help, but I solved a similar issue today. My date column was written as plain text and did not have leading zeroes.

      The date, March 19, 2015 was written as 31915.

      DateText in cell A2
      31915

      Based on this page and some other searches, I used this formula:
      =DATE( YEAR , MONTH , DAY )
      =DATE((20&RIGHT(A2,2)),LEFT(A2, LEN(A2)-4),LEFT(RIGHT(A2,4),2))

      HTH someone,
      Wayne

    2. Exactly the problem I am facing with. Is there an answer?

      1. Hi Reed,

        It's next to impossible to write a formula for this particular example, because the first item (supposedly a month number) contains just one digit (8). In other dates, there can be 2 digits, and no algorithm is smart enough to distinguish between these cases.

        If your source data had a leading zero, i.e. 081215182533 (mmddyyhhmmss format), then you could use the following formula:

        =DATE(MID(A1,5,2), LEFT(A1,2), MID(A1,2,2)) + TIME(MID(A1,7,2), MID(A1,9,2),RIGHT(A1, 2))

        1. You can insert an "if" statement, because all numbers smaller than 99999999999 will need a leading zero, and all numbers greater ar equal to 100000000000 will not.

  32. Very helpful, very clearly laid out, this saved me a lot of time. Thank you Svetlana!

  33. Hi. I am trying to covert a date time value that looks like this, 13/01/90 00:00, is left aligned but does not have an apostrophe in the formula anywhere, into a standard datetime format like this, 12/12/1990 15:00:00, so that the data works with the rest of my calculations. Any suggestions?

    1. Hi!

      You can use the following formula where A1 is a cell with the date:
      =DATE(MID(A1,7,2), MID(A1,4,2), MID(A1,1,2)) + TIME(MID(A1,10,2), MID(A1,13,2),0)

  34. I want to convert date which is in text format, number of period of date in one cell (e.g. 03/31/2014-09/30/2014,10/01/2014-04/01/2015 and so on).How am I convert it into number & change format date.

  35. Do you know how to convert the text value of a date to the date format within a formula result?

    For example, if I were to have a formula that results with "Today is 7/14/15" but I want to be able to link the date to an outside link, I keep getting "Today is 42195." How can I change the format within the formula?

    1. Hi Ryan,

      When you are concatenating a text string and a formula-driven date, you have to use the TEXT function to display the date in the desired format. For example:
      =CONCATENATE("Today is ", TEXT(TODAY(), "mm/dd/yy"))
      or
      ="Today is " & TEXT(TODAY(), "mm/dd/yy")

      1. Thanks for your very much helpful answer, Svetlana Cheusheva :)

      2. This is the perfect answer to a problem I have had for years!

      3. This was what I was looking for - great! Thank you!

  36. Im looking for the exact opposite: how to prevent excel from formatting my data to date and I found this useless shite !

    1. OMG John,

      you are very inconsiderate and self absorbed. You have no idea nor the patience to learn anything. Please go somewhere else....

    2. John, you are a useless shite. This article was the best I've seen with clear directions laid out with great visual examples. If you were looking for the opposite of this article, learn how to use Google and don't waste our time by posting your stupidity. PS: If you want the opposite, put an apostrophe before your entry in the cell, and Excel will only interpret the contents as plain text.

    3. Dear John,

      The tips above are very well laid out and helpful.

      If they are not what you were looking for, should you just keep looking rather than passing such rude remarks?

    4. John,

      This article explains how to convert text to date in Excel.

      To prevent Excel from converting your data to a date, type an apostrophe (') before it, e.g. '1/1/2015. In this case, the input data will be a text string.

      To convert existing dates to text strings, you can use the TEXT function or Text to Columns wizard, as demonstrated in How to convert date to text in Excel.

  37. I found the DATEVALUE worked but then I realized that the Day and Month were transposed. i.e. April 12th came out as Dec. 4th and March 20 produced an error.
    Is there any fix for that?

    1. Hi Kevin,

      Can you give an example of your "text date" please? How exactly is it written in a cell?

  38. Thanks

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)