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 12. Total comments: 736

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

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

  3. How do I convert

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

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

  5. Thank you!!!!

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

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

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

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

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

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

  12. All world data delete number 03415744310 03016084001 03028927904 03104492453

  13. Awesome Article. Thank You so much.

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

  15. 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."

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

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

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

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

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

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

  22. Hello.-

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

    Thank you.-

    From El Salvador CA.

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

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

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

    Thank you! Changed my world.

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

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

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

  29. Hey Guys,

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

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

  31. hi,

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

    can any one help

  32. Hi,

    How do I split the Month Day and Year without using Text to columns? or at least convert the date to just the day. Example 08/08/2018 to just 08 (day)?

  33. Hi,
    Any help in trying to convert 01/01/2018 into January 1st 2018 please?
    Many thanks

  34. How can I conert a text date like “Thu 5 Jul 2017 11:59 AM” to date ?
    Thanks

    1. Doina:
      You might be able to right click the cell that holds the data and choose Format Cells then Date then the format you want.
      If you need to split that time away from the date, I would use the Text-to-Columns tool.
      Highlight the cell then under Data select the Text-to-Columns tool. In that window choose the Fixed Width button then OK. Next click on the lines you don't want to use to split the data then OK. Then highlight the column that holds the date select the Date dropdown and choose the DMY option then OK.
      Now you can right click the cell with the date and choose Format Cells and choose the Date option and select the date format you want.

  35. Moin:
    Dates and Excel can be a real pain.
    I took your sample "20110328" put it into cell A22 and entered this formula in B22 and formatted it Custom dd/mm/yyyy. Let me know if it works for you.
    =RIGHT(A22,2)&"/"&MID(A22,5,2)&"/"&LEFT(A22,4)

    1. THANK YOU!~! :D

  36. Hi, I'm trying to convert excel date format from 20110328 to 28/03/2011, I used this formula "=DATE(LEFT(B4؛4)؛+MID(B4؛5؛2)؛+RIGHT(B4؛2))" and it was useful. on the other hand I had a lot of dates that it's about daily return and I get these returns to choose the monthly return and their dates. so, I
    used this formula "=RIGHT(E8;5)" as select the last return of a month but it showed number like "40631" .How do i change to get "/mm/yyyy"?
    thanks
    moin

    1. Moin:
      Have you checked the formatting for the cell that holds the 40631? The format should be the mm/yyyy and it looks as if it's General or Number.

      1. I changed the format in customs by mm/yyyy but no change takes place in result!?
        another question, somewhere I haired that when you change the 20110328 to 28/03/2011, you could do this changes with this format "yyyy/\mm/\dd".
        I do this work but it doesn't change.Do you know the correct form of last format???

  37. Anyone help on how to convert a date from a conventional format like 12-03-2018 to look like 12032018?

    Thank you

  38. Thanks so much!!!

  39. How can I covert
    a) 0820 (mmyy) into 31/8/2020
    b) 0216 (mmyy) into 29/2/2016?

    Note: the dd is always the last date of the month.

    Anyone can help?

  40. How do I convert a fractional year to a month, day, year format? i.e. 2016.72

    1. Gary:
      I think this will do it. Where the fractional date is in A11, enter this in an empty cell and format the cell in the format you need.
      =DATE(INT(A11),1,MOD(A11,1)*(DATE(INT(A11)+1,1,1)-DATE(INT(A11),1,1)))

  41. how to convert '08/01/2018 to 08/01/2018...

    1. Suresh:
      You can easily change this text into a date by removing the "'" in front of the date. Excel will then recognize it as a date and you can then modify the display in Format Cells.
      If you need the ellipse in the date remove the "'" go to Format Cells and in the Custom Option in the field enter "@*."

  42. How can i easily convert the following text strings into dates that I can use in formula's

    Friday 30 June 2017
    Thursday 29 June 2017
    Wednesday 28 June 2017
    Tuesday 27 June 2017
    Monday 26 June 2017
    Sunday 25 June 2017
    Saturday 24 June 2017
    Friday 23 June 2017

    I have checked they are text and not dates.

    1. Hello, Shane:
      AbleBits has done a great job of describing how to convert data that is exactly in the state your's is in. Rather than re-type all the steps I'll point you to it. Please see Example 2 in this section of the article above.

  43. Please convert 050818 (Text format) to 05/08/2018

  44. Hi Friends,

    I am looking for help to convert values

    FROM:
    05-01-2018-01.15

    TO:

    05/01/2018 01.15 AM

    Appreciate your help on this

    Thanks
    Khuharshree

  45. How do I change the date format from dd/mm/yyyy (eg. 01/01/2018) to dd-mmm-yy (eg. 01-Jan-18)? None of the formatting tools in Excel are allowing me to change the format, and when I click on "Show Formulas", the dates (which have been prepopulated by an accounting programme and then converted into an Excel spreadsheet) are the same as they appear in the spreadsheet (eg. 01/01/2018) and they appear in the formula bar as '01/01/2018. Please help!

    1. Caro:
      Right click the cell then choose format cell then choose Date from the list and select the display format you want.

  46. how to change this Format 01-Jun-2016 to 01-06-2016 Plz Send me Value

    1. Prakash:
      Right click the cell then choose format cell then choose Date from the list and select the display format you want.

  47. Please help to concern 31122018 into 20181231
    if 31122018 is in cell A1 I want it to be 20181231 in cell A2

    1. =RIGHT(A1,4)&MID(A1,3,2)&LEFT(A1,2)

  48. I thought this would be simple but I can't find it anywhere. I have months listed in column A. I want to create a date that's the first of that month. Something like =date(2018,A5,1), where the A5 cell says, for instance, "April", so it becomes 4/1/18. Except that I can't get it to recognize the text in A5 as a month, even trying many tricks I've seen here and on other sites. Thanks in advance for your help!

    1. have the month column (A) format to custom "mmmm" to show the month only. but be sure the content data still complete 1/1/18 and so forth. In the other cell, input =TEXT(A1,"mm")&"/01/18"

  49. Closer to my current issue is "half" dates.
    I have 1-Jan CSV file report on export from system.
    The 1-Jan cell value should have been 1-01( this was equipment ID #'s).

    My resolution was special paste and split data then manual correct. I would prefer a simple formula conversion is anyone can see one.

  50. Highlight "even days of weeks": sat , mon , wed

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