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 5. Total comments: 511

  1. how do i convert: "september 10, 2017" and "march 12, 2016" to dd/mm/yyyy?

    1. Hi!

      By using the TEXT function (the result will be a text string:): =TEXT(A1, "dd/mm/yyyy")

      Or, by applying the custom format (the result is a date): dd/mm/yyyy

  2. hey i want to convert date into next date..
    Example:- 1/4/2015= 2/4/2015
    15/4/2015=16/4/2015

  3. 8.6.2017
    8.6.2017
    dd.mm.year

    calculate the days with current today date

    1. Hello,

      if I understand your task correctly, you will need to use DATE function. You will find the instructions on how to work with the function on this blog post.

      If you need an instant solution, you can try our Date & Time Wizard from our collection of add-ins for Excel - Ultimate Suite. You can download its trial version from this web page.

  4. Hi,

    I have a bunch of date/time stamps that look like this:

    1497541289
    1497541291
    1497541294
    1497541297
    1497541299
    1497541300

    How do I convert them to real dates & times?

    Thanks,
    Pete

    1. Hi, Pete,

      could you please specify what are these values stand for?

      It's just the very last date in Excel (December 31, 9999) is stored under the 2958465 number. The stamps you provided simply can't represent the integers of the dates for Excel.
      So, for us to help you better, please specify what these values should transform into exactly.

      Thanks!

      1. Hi,
        I have also the DateStamp like : 1551363970 and I need to transform into : 2/28/2019 2:26:10 PM.
        Is any formula to conver this?
        Thank you,

        1. Hi Emil,

          Assuming your date stamp is in A2, please try this formula:
          =A2/(24*60*60)+DATE(1970,1,1)

          Don't forget to apply the needed date format to a cell with a formula.

  5. Good morning!

    my date data is in custom data type '00000000'
    changing it to string loses the leading zero and using the formula

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

    also does not work with the leading zero. any thoughts?

    example below from my worksheet

    10021980 10/02/80 (great)
    03301980 09/01/82 (not good)

    1. I had a similar problem to Joshua's with leading zeroes. I was able to solve it using an "if" argument with the "len" argument to adjust for where the date formula picked up the month and day numbers from the string.
      My data were, for example:
      14.09.74 (in cell e2, format mm.dd.yy)
      02.11.78
      my formula was: =DATE(RIGHT(E2,2),MID(E2,IF(LEN(E2)=8,4,3),2),LEFT(E2,IF(LEN(E2)=8,2,1)))
      I hope this helps anyone searching for help on this particular problem with leading zeroes.

    2. one way around is
      =DATE(RIGHT(E3,4), LEFT(E3,len(e3)-6), MID(E3,3,len(e3-5)))

      this way it can change the spot it starts reading the number from depending on the length of the number string since it doesn't see the lead 0

      1. I am not sure if this website is still checked, but can I get help writing a formula for the following number format. 00102998, the first number would be the year, so 2000 in this case. It actually goes 1 – 0 for 1990 – 2000. Then the second two numbers are the month. The day is not important and can be any 2 digit number.

  6. Hi,

    I have a column with 'Apr 7, 2016 10:39 AM' type data in it. How do I convert this text into a date/time format so I can use it to do further calculations? I.e Find the difference in time elapsed between 'Apr 7, 2016 10:39 AM and Apr 14, 2016 09:21 AM'?

    I've tried text to column (which I don' want to do in order to protect the data source). Is there a formula for this?

    1. Hi, Izzy,

      activate the cell with a text you want to transform, then under the Home tab find Number format box, and choose Long Date or Short Date to your liking. If it already says Date – your data is ready for calculations.

      1. Hi,

        I know this is an old, thread, but I've tried everything so far, so I'm gonna give this a shot as well.
        I have the same issue as Izzy and I've tried what you advised in your reply as well as the options in the article but as a I result I only get either the date "2/1/2020" or only the time "00:15:00". Regardless of formatting, I can't get them both as Date+ time or "2/1/2020 00:15:00". Any ideas?

  7. Converting a number to date for me isn't working.
    For example (when I follow your directions), I have a "number-date" of 1926. It "converts" to a date of 4/10/1909. 1927 converts to 4/11/1909, etc.

    1. Hello Benny,

      Please give an example of your full "number-date" and expected result. We will try to work out a proper method.

  8. Hi ,

    i'm trying to sort the below list of data in excel,

    03/26 0:00
    03/26 1:00
    03/26 2:00
    03/26 9:00
    03/26 10:00

    but the results are not as per my requirement

    03/26 0:00
    03/26 1:00
    03/26 10:00
    03/26 2:00
    03/26 9:00

    expected :

    03/26 0:00
    03/26 1:00
    03/26 2:00
    03/26 9:00
    03/26 10:00

    So i believe the issue will be resolved by converting the time to 00:00 format. i dont want to do it manually . could you help me out ?

    1. Hi, Chitti,

      it is most likely that Excel recognizes the data as a plain text, and you need to convert it. Please, follow the instructions from the article to check that in your document and convert the data.

  9. how can i convert this date "25/3/2017" to "3/25/2017"

  10. Dear, I am very depressed to calculate the age in Excel so please help me to solve my problem mostly my work to calculate is for example

    10.04.1987
    04.08.1999
    10.04.1987
    04.08.1998
    10.04.1986
    04.06.1992
    30.04.1981
    02.08.1996
    11.04.1989
    02.07.1993

    more than 4000 or 5500 date of birth have been calculated please share with me any formula to solve the matter in seconds please

  11. Dear Svetlana Cheusheva,

    I would like to ask you how to convert mm/dd/yy to dd-mmm-yy. I definitely can't find the solution. Could you help me to solve this problem??

    Thank you, XD

    Example:
    mm/dd/yy to dd-mmm-yy
    04/02/2016 to 02-Apr-16

    1. Dear Socheata,

      You can easily do this by changing the cell format:

      Select the cell(s) with dates, press Ctrl+1 to open the Format Cells dialog, select Custom under Category, and type the format code dd-mmm-yy in the Type box. That's it!

      For the detailed instructions, please see How to change the date format in Excel.

  12. Hello, is it possible to change date format from "Nov 05, 2015" to "11/5/2015"

    1. Hi Tim,

      If your dates are entered as dates and not as text strings, select all the dates you want to re-format, press Ctrl+1 to open the Format Cell dialog, select Custom under Category, and type the following format in the Type box: mm/d/yyyy

  13. Hello,
    For some reason my company's database exports the date as "Jun 17th, 2016" in text format. I can't get any of these functions to work and my suspicion is the "th"s and "nd"s, any ideas?

    1. I'd use a nested SUBSTITUTE to replace the "st,", "th," and "rd," with "," then apply the formula.

  14. I have a date 25/03/2019 which i'd like to convert to 2019/03/25. How do I do that?

    1. Hello Stella,

      Press Ctrl+1 to open the Format Cells dialog, on the left-side pane under 'Category' choose Custom, and type the following format in the Type box: yyyy/mm/dd

  15. i want to convert month year e.g. Mar 15 to 3/1/2015. The output or result should always be the first day of the month and should be in MM/DD/YYYY format. Thank you

    1. Hello!

      You can use the following formula to return the first day of the month of the date in A1:
      =DATE(YEAR(A1), MONTH(A1), 1)

      After that, press Ctrl+1 to open the Format Cells dialog, on the left-side pane under 'Category' choose Custom, and type the following format in the Type box: mm/dd/yyyy

  16. how to convert text 14,06,2017 to date 14-june-17

    1. Hi Michael,

      You can use either Text to Column Wizard (on step 2, check the Comma box under Delimiters) or the following formula:

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

  17. im trying to convert a date like 01/14/14 to a number like 20140114

    1. Hi William,

      Try the following formula, where A1 is the cell with a date.

      =TEXT(A1, "yyyymmdd")

      Please keep in mind that the result of the TEXT function is always a text string, and therefore if you want to output a number, wrap it in the NUMBERVALUE function:

      =NUMBERVALUE(TEXT(A1, "yyyymmdd"))

  18. 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.

  19. 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")

  20. 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")

  21. 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!

  22. 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.

  23. 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.

  24. 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.

  25. 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))

  26. 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?

  27. 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.

  28. 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.

  29. how to convert date to date value format?

    1. use this formula.

  30. 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.

  31. 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)

  32. 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!

  33. 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.

  34. 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?

Post a comment



Thanks for your comment! Please note that all comments are pre-moderated, and off-topic ones may be deleted.
For faster help, please keep your question clear and concise. While we can't guarantee a reply to every question, we'll do our best to respond :)