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

  1. HI , I have an issue with the date format when i copy data from server to my desktop.
    for eg :
    2017-02-01 11:00 PM converts to 1486008028000
    2016-120-01 11:00 PM converts to 1480651229000
    How do i retain my original date and time, what is the formula i can use.

    i have tried lot of formulaes posted in these forum but no luck.
    below are few i tried:
    -------------------
    =DATE(LEFT(G1,4),MID(G1,5,2),MID(G1,7,2))+TIME(MID(G1,9,2),MID(G1,11,2),RIGHT(G1,2))
    =--TEXT(G1,"0000\/00\/00 00\:00\:00.000")
    =TEXT(LEFT(C4,4),"mm-dd-yy") & " " & TEXT(TIME(MID(C4,6,2),MID(C4,7,2),RIGHT(C4,4)),"hh:mm:ss")

  2. Hi, i extracted some data from sql, but my dates turns out as follows:
    1273026171
    1273026260
    1273099172
    1273101199
    1273101265
    1273132534
    1273132579
    1274148024
    1274650978
    How can i convert it into readable dates?

  3. I am trying to convert some results from my Left and Right formulas into dates and it will not work.

    Sample - Doe,J 20-01
    20-01 is read for our purposes as Year(2020)-Month(01). No matter how I have extracted the information I cannot convert it to date.

    Thank you in advance for your assistance

  4. May I convert 15-03-2020 to 1 5 0 3 2 0 2 0 for each value in different cell?

  5. Hi,
    could anyone help me with this one ?
    I need to extract week no. and year from text
    Example:
    03-472018 should give 47-2018 or 47/2018
    02-522019 should give 52-2019 or 52/2019

    Thanks a lot.

  6. Excellent article. Thank you.

  7. Great article but i'm struggling with a data set I've imported from CSV.
    All data has been imported with cell formats as TEXT. I have date columns in the format 'MM/DD/YYYY HH:MM AM' and 'MM/DD/YYYY HH:MM PM'.
    I want to keep the original data values in sheet 1 and present these values in sheet 2 in the desired format (DD/MM/YYYY HH:MM) and have tried using the formula =VALUE(Text) and setting the cell format using custom to dd/mm/yyyy hh:mm.
    For some of the values this presents a date time format but as MM/DD/YYYY HH:MM (not what i want) but for other it shows a #VALUE! error.
    Examples (these covert to MM/DD/YYYY HH:MM) which is not the format i want:
    01/11/2020 10:07 PM
    01/11/2020 8:02 AM
    01/10/2020 5:11 PM
    These are not converted and show the #VALUE! error:
    01/13/2020 9:03 AM
    01/13/2020 8:50 AM
    01/13/2020 8:46 AM
    Seems that anything where the DD part of > 12 it does not like the value.
    I've searched everywhere for an answer to this and would really appreciate it if someone could help me.

    1. I actually found a solution to this myself in the end but thought i share it on here in case anyone else comes across this issue.
      It's not the neatest solution but it works.
      =VALUE(CONCATENATE(RIGHT(LEFT(TextDate, 5), 2), "/", LEFT(TextDate, 2), "/", RIGHT(LEFT(TextDate, 10), 4), " ", RIGHT(TextDate, LEN(TextDate)-FIND(" ", TextDate))))
      Then format the cell to be DD/MM/YYYY HH:MM

  8. 8.6750245000162E+26 i need this as ENROLMENT_NO_DATE lainst 8 digit date and remains are number
    kindly give a formula for better understanding.

  9. 12/6/2019 : 2300, in This i need to extract only time with Time Format Like 11:00 PM

  10. when i pull the data from system date are come as below how we are convert this number in date please help me.

    Food Lic Issue Date Food Lic Valid Date
    0.1928125 0.151168981
    0.1928125 0.151168981
    This is very important to know me.

  11. Hello,
    I have a date range in a spreadsheet:
    Dec 5-11 2019
    How to I convert that to show Thursday, Dec 5 to Wednesday, Dec 11?

  12. 20190924183744 this number is combined with date and time.
    how to convert this number to date and time separately by formula of command. I will be pleased if you help me to convert this.

  13. I've got a column of dates formatted as text using mmyy. I need to split them into 2 separate columns formatted as mm & yyyy. Any suggestions?

  14. 29/05/2000 ko twenty May two thousand
    Main

  15. Hi,

    I have a column with 1560000000000 type data in it. How do I convert this text into a date format so I can use it to do further calculations?

    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?

  16. Good day
    I have a question regarding the population of multiple cells based on the date range of cell a and b. So the example is, (AI21) is the user entry of the beginning date. (AI22) is the user entry of the ending date. Once this second date is entered, conditional formatting highlights this range of cells. I am trying to populate the highlighted cells with the drop down selection in (AI23). So if 4 cells are highlighted then the value selected in (AI23) will populate in those highlighted cells. I can send current state workbook if anyone could assist. Thank you again for your consideration. Regards

    1. I posted a question in a comment section. I didn't look first sorry folks.

  17. Thanks
    with your post, i have save a lot of time.
    Regards

  18. I want to use CONCATENATE function to combine data from multiple cells so it can be imported to a different program. However, when I run the CONCAT formula, the SHORTDATE value is returned as the TEXT value of the date.

    So, I need to convert a SHORTDATE format value to the same value but as text.
    For example:

    The text value '43721' returns a short date of '09/13/2019". But when I plug the cell with SHORTDATE of "09/13/2019" into my CONCAT formula, the result is returned at 43721.

    I need the SHORTDATE 09/13/2019 to be returned as text but still read 09/13/2019.
    Any idea how to do this?

  19. How do I convert a series of negative date values to a date format - in particular; PS they are all French or Gregorian dates (because none are older than 20 Dec 1582). So just a straight conversion of a column of these negative numbers to a date numerical format (is ok).
    -82059 which is 29-04-1675 (or 29 Apr 1675)
    -83311 which is 24-11-1671
    Many thanks

  20. I am trying to create passwords from user first name, last name, & DOB. I need just the 2 digit month and the 2 digit day from 9/10/2019 format. This is the formula I have working, but it drops the zero. =CONCATENATE(LOWER(LEFT(A4,1)&LOWER(LEFT(B4,3)))&MONTH(E4)&DAY(E4)) Result- abbb910. I need abbb0910. Thanks for your assistance!

    1. I figured out my own answer and wanted to share in case anyone else was looking. =CONCATENATE(LOWER(LEFT(A4,1)&LOWER(LEFT(B4,3)))&TEXT(E4,"mm")&TEXT(E4,"dd"))

  21. I am needing to convert a date from 20030331 to 03/31/03 or 20011204 to 12/04/01. How do I convert this?

  22. How do I convert

    Aug 18, 2019 08:15CHECKLIST, which is in text format into date format in a separate column.

  23. I am looking for converting Thursday, January 01, 2015 date to UK date format. couldn't find a solution on the web but your website has helped. really saved a lot of time and frustration. great job. thank you

  24. Thank you!!!!

  25. Dec 27 2018 3:55PM General format need help in converting to date and time format using excel

  26. For people with ADHD, the scrolling ad on the bottom makes it darned near impossible to read the article. Thanks.

  27. I Want to convert date 18/05/2024 to 24/05/2018 plz help me

  28. I want to use dates as period of time in one cell. e.g. 2019/05/01 - 2019/05/31.
    I use =eomonth function to get the date from other cell. e.g. =(EOMONTH(A1,-1)+1)&" - "&EOMONTH(A1,0)
    This only shows the date as value and don't know how to change into date format (long/short date does not work).

  29. what is the formula to sum including alphanumeric of the following:
    3 yrs, 9 mos
    0 yrs, 2 mos
    = 3 yrs, 11 mons

  30. Humbly requesting help with this format, which I believe is text and already converting UTC to my local time (-4). How can I get this back to UTC in Excel?
    Many thanks in advance!
    2019-04-15T20:00:00-04:00

  31. All world data delete number 03415744310 03016084001 03028927904 03104492453

  32. Awesome Article. Thank You so much.

  33. I am highly experienced, advanced and skilled. This is a fine, intelligent article, and written in a comprehensible level for novices while not so dumbed down that it is not an efficient resource for gurus. Extremely well done.

    Frankly, I never grasped the benefit of the seemingly silly or inert VALUE(), though I had stumbled onto the (equivalent?) utility of =A1+0 very early on. I've been doing A1+0 for decades; I may now replace that convention with VALUE() as a matter of style. Using A1+0 is sort of saying "This is a stupidly designed product, but I'm even stupider, that I am resorting to something as ludicrous as this."

  34. I am highly experienced, advanced and skilled. This is a fine, intelligent article, and written in a comprehensible level for novices while not so dumbed down that it is not an efficient resource for gurus. Extremely well done.

    Frankly, I never grasped the benefit of the seemingly silly or inert VALUE(), though I had stumbled onto the (equivalent?) utility of =A1+0 very early on. I've been doing A1+0 for decades; I man now replace that convention with VALUE() as a matter of style. Using A1+0 is sort of saying "This is a stupidly designed product, but I'm even stupider, that I am resorting to something as ludicrous as this."

  35. how to convert date 2012019 into mm/dd/yyyy

  36. HOW TO CONVERT DATE 01/05/2019 INTO MMM-YY FORMAT (MAY-19).

    1. Press ctrl+1 in format cells , select customs and type: yyyy-mmm

  37. I imported a text file into a blank Excel sheet. One of the columns has four characters values, i.e. JAN1 or MGG3, .... However, it seems that the format of the cell changes from General to Custom (d-mmm) = 1-Jan. If I change the format to Text, it changes to a number 43101. How do I get it back to JAN1? I have others that happen to be similar to other months JUN5, MAR9...

    1. Hello, George,

      Please try applying the Text format to cells before entering the values. Thus, your initial records JAN1, JUN5, or MAR9 will be kept as they are.
      Otherwise, Excel will treat them as dates and convert right away accordingly. And I'm afraid the only way to get them back is to apply the Text format and enter the correct records manually.

  38. I'm not having any luck with VALUE or DATEVALUE.

    I have a column with dates that show as 1/18/2017 and say they're formatted as "general". I first tried just changing the formatting to "dates" but nothing changed, they still are basically text.

    I then tried using DATEVALUE and VALUE in a new column but it just returns #VALUE!

    I'm at my wit's end. If they had even written them YYYY/MM/DD I could at least have sorted them as text.

  39. Thank you! The DateValue function was exactly what I needed.

  40. Concatenate(DateA,",",DateB)

    then use text to column with "," as a delimiter and then use the wizard to fix the date to the correct format

  41. Hello.-

    I would to express my sincerely thanks to you for this tutorial.-

    Thank you.-

    From El Salvador CA.

  42. what formula should I use to convert 41255 into 12/12/2012

    I know that if cell is number , I can change the cell type to date and will have 12/12/2012

    But I need a formula to use inside /outside excel to convert a 5 digit number to dd/MM/yyyy

    1. Hello!
      You can convert 41255 into 12/12/2012 by using the TEXT function:
      =TEXT(A1, "dd/mm/yyyy")

      Please note that the result will be stored as text.
      If you need the result to be a date, you should apply the custom format: dd/mm/yyyy

      Please let us know if you have any other questions.

  43. I have a downloaded spreadsheet that lists the dates as dd-mm-yyyy and dd/mm/yyyy in the same spreadshet. I am able to change the format for dd/mm/yyyy but for the dd-mm-yyyy I cannot format. The dd-mm-yyyy dates are "General" and
    dd/mm/yyyy are "dates". Does anyone have an idea on how to fix the "General" ones so that I can format them to dates? I really need help with this. I fix it manually but it is so time consuming because each month has over 100 transactions.

  44. This is the BEST website I've ever visited for Excel help. What incredibly clear and worthwhile information.

    Thank you! Changed my world.

  45. Hi,

    I have some dates that I can't seem to find any formula for. My data exports strange, I don't know why it does it, but my dates populate like this:

    1022012 (supposed to be 1/02/2012)

    and the months with two digits populate like

    12232012 (12/23/2012)

    I don't know what formula I can use to formulate this as a normal date. My file is nearly 600K lines and there's no way I can manually change the date for them. Please help!!

  46. How to convert Thu 11:47 PM to date format. Thu 11:47 PM is the email received date.

  47. Hi I have Excel text column having value as 'Wed Feb 07 13:39:40 PST 2018' I want to convert to another column with date/time type with value as 2/7/2018 1:39:40 PM , how to do it?

  48. Hey Guys,

    Great job on this thread! How do I convert Jan/31/18 08:42 PM to 01/31/2018 ?

  49. Hi,

    Please help. I want below text fields in format dd-mon-yyyy

    09011958 like 01-SEP-1958
    10201995
    03301975
    08141975
    10241974

    OUTPUT IS ###### IF I USE ANY OF THE ABOVE METHODS. wHERE AM I GOING WRONG?

  50. hi,

    i want to convert 'Aug 8 2018 4:02AM" in date format dd-mm-yyyy hh:mm

    can any one help

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