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 18. Total comments: 735

  1. Hi. I need to convert a date to financial year e.g. 14/07/2016 to 2016/17. There doesn't appear to be an option in excel to do so. I assume that I would need an If statement such as =IF(A1=>01/4/2016, "2016/17", "2015/16") but in order to do this I have to amend thestatement. Is there any other way to achieve this as I have to cover many years of data?

  2. I have date format like 01-01-1900 00:00:00 and i want the format to be like 1900-01-00 00:00 but it's not changing as it is text format and i have tried many solutions like clicking text to columns,changing format in format cells ,apply format painter,specifying =datevalue() function in empty cell but none of them worked.Can anybody help me in overcoming this issue?

  3. Thank you!
    "Text to Columns wizard - formula-free way to covert text to date"
    helped me!!!!

  4. HI,
    I am facing a weird issue, i generated report from QUIX, where i see few of the dates as *******, i do not know how it has come and how to convert those into dates.

    Please help in this regard,
    Suma

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

  6. Hi,

    In a excel column few cells has correct date format but other cells date format is shown in text. How to format the entire column in correct date format.

    Regards,
    Manoj

  7. 0
    down vote
    favorite
    I have been trying to use the CONCATENATE function in the excel for creating multiple values of the financial statements for different years. My main formula is as under:

    =VLOOKUP(TEXT(CONCATENATE(A1,"-",C6,"-",RIGHT(B7,2)),"dd-mm-yy"),range,column,FALSE)

    Although the (TEXT(CONCATENATE(A1,"-",C6,"-",RIGHT(B7,2)),"dd-mm-yy") gives me the date that I want (e.g. 10-Jun-10) yet when I use this in the VLOOKUP it does not treat it as a date and returns the error. How can I fix this?

    I have also tried to put the DATEVALUE and VALUE function into it yet the VLOOKUP still won't give the desired result and will provide the outcome as #NA

  8. Hi,
    I had exported a spreadsheet from a work site, and the date came up as yyyymm, but when I convert it in order to create more formulas it isn't working. The example is 201001, is the year 2010 and January, but when I convert it comes up as April 2450. I want to fix this so I can create a formula that can subtract six months. Please help, Thank you!

  9. Hi,

    How to change 6 26 AM to 6:26 AM in excel?

    Thanks in Advance.

  10. I am trying to format the following date cells which is "General" but for some reason it doesn't work. Please help.
    01/25/2016
    01/24/2016
    01/24/2016
    01/24/2016
    01/20/2016
    01/19/2016
    01/16/2016

  11. Hello,

    please guide how to change this date 15-Mar-08 2203 (YY-MMM-DD HHMM) into 08/03/15 22:03 (DD/MM/YY HH:MM).

    currently i am doing manually one by one and i have around two hundred thousand rows.

    please guide ho to do this in excel.

  12. Hi,

    I need help to convert a string of text to date format (ddmmyy).
    Wednesday, October 21, 2015 02:10:03 AM . Thank you

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

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

  15. I have a full row that needs to look like this 01/06/2016 HELP!!!

    Policy End Date
    20160106
    20160228
    20160308
    20160311
    20160326
    20160425
    20160507
    20160523
    20160527
    20160602
    20160602
    20160608
    20160611
    20160626
    20160705
    20160706
    20160710
    20160801
    20160802
    20160806
    20160811
    20160812
    20160910
    20161105

  16. Much obliged very helpful

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

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

  19. I have a field in a CSV file that is a combination of text and date and time. I first save the file as excel, then I use the MID formula to extract the date. However, I'm not able to convert it to an actual date value (so it can be used in calculations) tried all the above, my cell continues to read #VALUE! Pl help!

  20. Help Please! I'm trying to find a formula that work for what I'm needing. I have two due dates 10/1 & 4/1 (Column B contains 1st pmt due date), (Column C contains 2nd pmt due date). I'm trying to find a formula for Column A that will show which pmt either 1st or 2nd pmt is due next.

  21. hello,
    can you help me please .
    I have my dates in this formats : 2,01201E+13 ( 20120123151151)

    how can i solute this problem .
    thank you

  22. thank you madam i follow all formulas
    12-2-2010 =datevalue(this date)

    result # value

    plese help me

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

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

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

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

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

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

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

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

    thanks & regards
    Santosha DS

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

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

  33. sir

    excel date format corrupted

    ex: 20141027

    how to recover it please help me sir

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

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

  36. Very good site with lot of information.

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

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

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

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

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

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

    Plz Urgent

    Regards,
    Naiyar

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

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

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

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

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

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

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

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

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 :)