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 17. Total comments: 731

  1. I WRITE LONG NUMERIC VALUE IN EXCEL IT CONVERT INTO SCNTIFIC VALUE 1.1032E+28 WHEN I FORMAT CELL INTO TEXT IT DOESNOT GIVS ME CORRECT VALUE

  2. help on quarterly date display e.g Jan-March.

  3. thanks for your help, though am not able to find a formula that can display cell. please help.

    Regards,
    Jeremmy.

  4. hello,

    i got for example:

    oct-19-2016

    and want to convert it into:

    19.10.2016

    do u have a solution for this?
    thanks anyway!

  5. Can the below formatted data be converted to Date format:

    Sep 1 2016 10:43AM
    Sep 2 2016 10:53AM
    Sep 20 2016 10:37AM
    Sep 21 2016 10:54AM
    Sep 22 2016 10:51AM

  6. Please help me use date function to entire row

  7. Can I apply date value formula to entire row. If yes please help

  8. I have a data to be entered excel like 4-12 and 5-8 ...etc. But it is automatically converted in to date format. How to overcome it.

  9. In my case I am having dates in different format as mentioned below. How can I convert those in to a single format.Please share your ideas.
    Eg;
    01-12-2015
    12-30-2016
    2016.30.12
    12.30.2016

    Help me to convert theses above date to common format like date-month-year.

  10. bingo, you are great ablebits.

  11. Hii how are you..
    I am facing to convert Jun 4 2016 12:01:18:000AM to date format.
    Please help me in this regard

    Thanks

  12. HI Svetlana Cheusheva,
    Need to convert this 1471408181 to date format please help

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

  14. Hi, I have scraped a date from a site and I have in the following format:
    "
    August 11, 2016
    ,
    6:36 am
    "

    I used =TRIM(MID(R2,4,17))&" "&TRIM(MID(R2,54,8)) to get the display to
    "August 11, 2016 12:04 pm"

    I am unable to convert it to Excel date. Would you have any advise/ tips on the approach? Thank you in advance.

    Great page BTW. Fantastic examples.

  15. Hi, Svetlana.

    Can you help me on what formula should I input in converting the following text to date and time? I am doing a tracker of our team's login and logout and I would need to calculate the total hours of our timelogs. Given the data, I would need to convert the text first to time and date.

    Sample text: 01/06/16 (03:45pm)

    Many thanks in advance!

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

  17. Dear All,

    We have a question please check and resolve.

    Date-1 , Date-2
    7/17/2012 , 41125

    Enter a formula to get the no of hours between the two dates?

    Regards,
    Sandeep

  18. Hi, how do I change a quarter into a date. So if value is 20163, I want to get first day of Q3 i.e. 1-July-2016.
    Thanks.

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

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

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

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

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

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

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

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

  27. Hi,

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

    Thanks in Advance.

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

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

  30. Hi,

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

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

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

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

  34. Much obliged very helpful

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

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

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

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

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

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

    result # value

    plese help me

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

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

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

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

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

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

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

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

    thanks & regards
    Santosha DS

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

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

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