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 6. Total comments: 726

  1. Hi there,

    We get a date in text format (for example April 12 2002) - is it possible to covert this to the date format DD-MMM-YY (for example 12-APR-02.

    Thank you

  2. Hi sir,

    i want to create a format for particular cells.
    In that cell if i type 26062021as a text by pressing enter i want as a date 26/06/2021 in the same cell.
    is it possible?

  3. Hi,

    We get a date in text format in dd/mm/yyyy hh:mm:ss format from Application when we download it into excel. I need to convert this into Date in mm/dd/yyyy hh:mm:ss format. For example date is in text format (30/06/2021 11:53:32) (dd/mm/yyyy hh:mm:ss). I need to convert it into Date as 06/30/2021 11:53:32) into Excel. I understand that if date is coming from application in dd/mm/yyyy format and if your system time on which you are opening and using excel is not in dd/mm/yyyy format then excel does not recognize it as date. Do we need to change the system date format using Language preference to make it work?

    1. Hello!
      The formula below to solve your task:

      =--(MID(A2, SEARCH("/",A2) + 1, SEARCH("/",A2,SEARCH("/",A2)+1) - SEARCH("/",A2) - 1)&"/"&LEFT(A2, SEARCH("/",A2,1)-1)&MID(A2,SEARCH("/",A2,SEARCH("/",A2)+1),100))

      We have a tool that can solve your task in a couple of clicks - Ablebits Tools - Text to data. It is available as a part of our Ultimate Suite for Excel that you can install in a trial mode and check how it works for free.

      1. Thanks Alexander for your quick reply and the formula. I tried the formula that you shared but it looks like it gives result in text format only. I need that results in Date format. Can you please help?

          1. I have tried to do format cell with the help of Custom formatting with mm/dd/yyyy hh:mm:ss. but it did not work. As I understand to distinguish date and text date is date will be right aligned while text date will be left aligned and for text dates If several text dates are selected, the Status Bar only shows Count. which is the same in my case as after applying the formula that you shared and applying format cell on that. If I tried to run other Date functions on that cell it is giving Value error.

            Earlier I was able to get the desired results but for that I need to use three cells with different formula to get the Date in mm/dd/yyyy hh:mm:ss in Date format. That's why I requested your help if that can be done with single formula.

            1. Hi!
              My system date format is mm/dd/yyyy. The formula works for me and returns the date. Check if the formula was copied correctly if there is a double minus at the beginning. Use the program that I recommended to you.

              1. Hi Alexander,

                After changing Language preference to English (United Kingdom) which changes my system date to dd/mm/yyyy which is matching with text date dd/mm/yyyy hh:mm:ss on excel, Formula is working as expected. Thanks a lot for your help and providing feedback patiently.

              2. Hi Alexander,

                my system date format is m/d/yyyy. I missed to add double minus at the beginning. After adding double minus it is showing the result in Date format however it shows the date in the same format for date till 12/06/2021 hh:mm:ss like dd/mm/yyyy hh:mm:ss and when I choose custom formatting to dd/mm/yyyy hh:mm:ss it converts in into 06/12/2021 hh:mm:ss.

                As soon as I choose date 13/06/2021 hh:mm:ss and later dates, formula starts giving value error. so it looks like it is not picking date greater than 13th. considering 13 as month?

  4. Hi my question is simple, please solve this!

    Problem: number format = 05/03/2017
    when you change the above no. format to U.S. date format MM/DD/YY, the result is 03/05/2017. This is 05th of March 2017 as per the format, but the actual date for me is 03rd of May 2017. I want to convert the number 05/03/2017 to US format MM/DD/YY that should give me 05/03/2017 and not 03/05/2017, I hope my question is clear? Awaiting for any help!

    1. Hello!
      I didn't really understand what is your start date and what date you want to get. But this formula should help

      =DATEVALUE(TEXT(A1,"dd/mm/yyyy"))
      or
      =DATEVALUE(TEXT(A1,"mm/dd/yyyy"))

  5. Need to convert to date
    15AUG21V40528

    1. Hello!
      I’m not sure I got you right since the description you provided is not entirely clear. However, it seems to me that the formula below will work for you:

      =--LEFT(A1,7)

      Set the date format in the cell

  6. I have given date of 20200911043955

    How to change it to date?

    1. =DATE(LEFT(B1,4),MID(B1,5,2),RIGHT(B1,2)) this should help assuming given is a text form of "yyyymmddhhmmss"

  7. Hi,

    I have a column of data. One column named Data Changed but the values under are 2.02009E+13. How can I changed this to Date Format?

    1. Hello!
      Pay attention to the following paragraph of the article above: How to convert number to date in Excel - How to convert 8-digit number to date in Excel. Your number is too long to display in normal format. Look in the formula bar and you will see it in full. You can use the formula from this paragraph.

  8. Hello , i have dates like this ; 12/03/2021 22:49:46 and 13/03/2021 02:59:02
    this are written in text.

    When i use formula : =TEXT(Test!A2648,"yyyyddmm,hh:mm:ss") i am getting 20210312,22:49:46 and it is correct
    but if i use same formula on 13/03/2021 02:59:02 i am getting the same text, no like on the first one.
    i tried to change yyyyddmm to yyyymmdd , tried to add / but nothing. can you help me?

    1. Hello!
      Your dates are written in dd/mm/yy hh:mm:ss format as text. The date of March 12 turns into December 3 for you. The second date does not change since month 13 does not exist. Use the guidelines above to convert your text to the correct date. And then apply a custom format.
      I recommend reading this guide: I recommend reading this guide: How to change date format in Excel and create custom formatting

      If something is still unclear, please feel free to ask.

  9. I have this problem:
    I received a file with this date format 13/04/2021,which i need to be shown as 4/13/2021.
    Text format wont allow me to change it.
    I tried the formula =DATE(YEAR($A1),DAY($A1),MONTH($A1) and it worked on a string like 12/4/2021, but when April appeared as 04 it didnt work.

    1. Hello!
      If your date is written as text, I recommend using the instructions in this article: How to split text in Excel using formulas.

      =DATE(RIGHT(A2,LEN(A2) - SEARCH("/", A2, SEARCH("/", A2) + 1)),MID(A2, SEARCH("/",A2) + 1, SEARCH("/",A2,SEARCH("/",A2)+1) - SEARCH("/",A2) - 1),LEFT(A2, SEARCH("/",A2,1)-1))

      Hope this is what you need.

      We have a tool that can solve your task in a couple of clicks - Ablebits Tools - Text to data. It is available as a part of our Ultimate Suite for Excel that you can install in a trial mode and check how it works for free.

  10. Hello, I am trying to convert this type into a date format in Excel.

    9/9/2020 12:53:42 PM -05:00

    I used to use this: =DATEVALUE(MID(C2,1,10))+TIMEVALUE(MID(C2,12,8))-TIMEVALUE("5:00")
    but it isn't working for me. Any thoughts?

    1. Hello!
      I believe the following formula will help you solve your task:

      =DATEVALUE(MID(C2,1,8)) + TIMEVALUE(MID(C2,10,11)) - TIMEVALUE("5:00")

      I hope it’ll be helpful.

    2. Hi

      I have applied this formula: =DATE(RIGHT(A4,4),MONTH(DATEVALUE(LEFT(A4,3)&"1")),MID(A4,5,FIND(",",A4,5)-5)) but it has applied to first 2 dates
      Jan 2, 2017
      Jan 02, 2017
      I want to write a single formula to convert them into an appropriate date format for all the dates. Please suggest.

      1. Hi,
        Unfortunately, it is impossible to convert all your values to a date using one formula. Your values contain a different order of dates.

  11. Hello,
    I want to write a single formula to convert them into an appropriate date format for the below all the dates.

    Jan 2, 2017
    Jan 02, 2017
    Jan 2 2017
    Jan 02 2017
    Jan 2, 17
    Jan 02, 17
    Jan 2 17
    Jan 02 17
    2 Jan 17
    2 Jan 2017
    02 Jan 17
    02 Jan 2017
    2, Jan 17
    2, Jan 2017
    02, Jan 17
    02, Jan 2017

    Could you please suggest me the single formula for the above dates.
    Thank you so much in advance for the help.

    1. Hello!
      I hope you have studied the recommendations in the tutorial above. It contains answers to your question. Also read the comments and answers to previously asked questions. For example, this comment. Or this one.

  12. Hello,
    If I want month only in these 3 dates of 5.6.2012, 05.06.2021,31.1.2021 which actually seems as date but it is not. It is in text format. so ,how can I found the mid value (Month) from Mid formula. Please suggest.

  13. Hi

    I have this number 20210413090001. I want to convert it to date and time,
    2021/04/13 09:00:01

    please advise the formulae to use.
    thank you

      1. Thank you v much. Appreciate your precious time to guide me

  14. how to covert below number in date
    132627705569531000

  15. Hi!
    I have a some letters. Every letter represent a range of dates. The letters have priority between themselves. The ranges may overlap themselves.

    In result I need to obtain the (dates/ranges in correct order: from earliest date - to latest) of letter with priority is higher than other letter present in same day/range.
    We ignore letter with lowest priority, in exchange for the best.

    1. https:// i.imgur.com/ZYmSLO4.png

      I made an example in excel, image is above (link).
      This what im asking may br hard to realize, but i would thankful even with an advice, how to do that.
      (I think the main problem is to separate the rages, and regroup them)
      Thanks anticipated!

      1. Hello!
        Based on your description, it is hard to completely understand your task. However, I’ll try to guess and offer you the following:
        In cell L7, write down the formula

        =IF(SUM((F7:F19="B")*(G7:G19>J7)*(H7:H19

        1. Thanks !
          Ill try.

  16. Hi, I have the below date value in cell 16-JUL-2018 - 09:00. But, excel consider this as text value and it is left aligned. I want to convert this to date format as 16-JUL-2018 - 09:00. Appreciate your help.

      1. Sorry Alexander ! May be i did not explained my requirement correctly.

        My requirement is, imported date is in text format, 05-Mar-2021 - 09:00 and i want to convert that to Date in the same format.

        Appreciate your help.

        1. Hi,
          The formula works for me. I don't know which dash is used in your text. Replace the dash with a short one.

          =--SUBSTITUTE(A2,"-","",1)

  17. HI

    DATE 01/04/2021 CHANGE IN TO 01-04-2021

    HOW CAN I CHANGE THIS FORMET

  18. Hi Guys,

    In the report the its '1/31/2021 12:00 AM'
    '1/31/201 8:20 PM'
    I want to convert that into '2021/1/21 00:00'
    '2021/1/31 20:20'

    Please help how to get this

  19. Hello,
    I need to convert a four digit-number "2020" to a year. I've tried to use the the Custom + yyyy method and it keeps printing 1905. I have no other date information - month/day, just the year. Please help!

    1. Hello!
      You need to convert number to date. Use the DATE function

      =DATE(2020,1,1)

      Then use the custom date format "yyyy"

  20. How do I covert this date format ; 18/02/1900 to text (numbers)

  21. Am working on staff pension how do I calate the due date of retirement, retire ment age is 65. Staff date of birth is 02/5/1958. Which formular can add 65 to date format to get expected date of retirement

  22. how to convert a text "Sun Jan 12 2020 05:29:59 GMT+0530 (India Standard Time)" to a date

  23. Hi,

    when i paste this

    "48618130 21-2-17 00:11:53 sell 1.00 XAUUSD 1789.19000 1826.6 1785 21-2-17
    01:47:41 1787.84000 0.00 -1.79 135.00"

    from my trading journal (you can see source here http s://imgur.com/a/JRNOF5J)
    into my excel it change date to 21.2.2017, but correct is 17.2.2021. I tried set cell to be TEXT but not working.

    Can you give me advice how to format it or code for macro or how to turn off this autochange ?

    date is in 1 cell...

    1. Hi,
      In your trade journal, the date format is "yy-mm-dd". In your Windows settings, the date format is "dd-mm-yy".
      Option 1. Change the default date format in the Windows Control Panel.
      Option 2.

      =DATE("20"&LEFT(A1,2),MID(A1,4,FIND("-",A1,FIND("-",A1,1)+1)-FIND("-",A1,1)-1),RIGHT(A1,LEN(A1)-FIND("-",A1,FIND("-",A1,1)+1)))

      I hope it’ll be helpful.

  24. hi wanted to ask you help on converting Sep 27 2018 to dd/mm/yyyy in excel .. super thanks

  25. Hi, could you please help me to converting date & time like 20210205062248 into mm/dd/yyyy hh:mm:ss AM/PM format

  26. Hi there,
    Could I get some help with converting a date like this 19660218 to yyyy/mm/dd

    Thank you,
    Robert

    1. Hello!
      To get the date use this formula —

      =DATE(LEFT(A2,4),MID(A2,5,2),RIGHT(A2,2))

      Use custom format "yyyy/mm/dd"

  27. Hello Alexander how would I convert 2014-01 to January using a formula?

    1. Hello!
      I recommend using the VLOOKUP function to select the name of the month

      =VLOOKUP((--RIGHT(B1,2)), {1,"January";2,"February";3,"March";4,"April";5,"May";6,"June";7,"July";8,"August";9,"September";10,"October";11,"November";12,"December"}, 2,0)

      I hope this will help

      1. Hello Alexander-

        Unfortunately that did not work! Is there another possible solution for 2014-01?

        Perhaps something with a right or left or trim function?

          1. When I enter the formula I get #VALUE!

            I even copy and pasted to make sure I am entering it in correctly.

            1. Hi,
              Unfortunately, without seeing your data it is impossible to give you advice.

              Unfortunately, I don't understand the reason for your error. The formula works for me. Could you please describe your task in more detail and send us a small sample workbook with the source data and expected result to support@ablebits.com? Please include the link to your blog comment.

              We'll look into your task and try to help.

  28. Hi Alexander, how do you convert number 1 to January 30, 2021, and 1.25 to February 7, 2021, 5 to May 31, 2021, so on and so forth. is this even possible?

    1. Hello!
      I believe the following formula will help you solve your task:

      =EOMONTH(DATE(2021,1,1),A1-1)+(A1-TRUNC(A1))*31

      I hope it’ll be helpful.

      1. Thank you so much! this is very helpful!

  29. How do i convert numbers from 11:07 to 11.07?

    1. Hello!
      I guess 11:07 is the time. You can convert it to text -

      =TEXT(A1,"hh.mm")

      I hope I answered your question. If something is still unclear, please feel free to ask.

  30. Is there a formula I can use to get 041620 (MMDDYY) to generate to April 16, 2020?
    Thank you!

    1. Hello!
      I believe the following formula will help you solve your task:

      =DATEVALUE(TEXT(A1,"##-##-##"))

      Set the date format in the cell.
      I hope it’ll be helpful.

  31. How do I make it so that when I type 05082017 it automatically switches to 05/08/2017? Without needing to separate the number with “.” “,” “/“ or anything

  32. Hi,

    Is there a way to convert a cell value that is actually a year, but formatted as a number, to an actual year that responds correctly to formulas?

    For instance,

    Column C displays manufacture years as : 2019, 2017, 2020, etc.
    These are all currently just numbers, but should be years so that they can be used in other formulas and tables.

    Thanks in advance.

  33. Hello!
    At work I have encountered a recurring problem with several dates extracted from a SCADA program, that have a structure such as:
    2020 - 12 - 31 19:00.106. For my monthly reports, I would like this date to look like: 31.12.2020 19:00, aka the dd/mm/yyyy hh:mm format.
    However, whenever I try to convert the date into the format I wish to convert it into, using the Format Cells Menu, nothing changes.
    Could you please provide a solution for my problem? I have tried multiple commands, however, converting it into the desired format only works if I manually delete the digits after the . (As in 19:00.106). I would greatly appreciate your help, for I would like to simplify my work.

    1. I have made a mistake in typing, for which I apologize. The date format I export from the SCADA program is: 2020 - 12 - 31 19:00:01:106. I forgot to type in the seconds.

  34. 1944 to 2020

  35. helpful, thank you!

  36. 4/12/2021- 4/16/2021 to Monday 12th April 2021 - Friday 16th April 2021

  37. How to get Monday 12th April 2021 to Friday 16th April 2021

  38. Hello Alexander,
    Looks like you've provided great information so far. My question is no doubt simple, but I can't seem to find an answer.

    I need to convert 3-character month text ("JAN" ... "DEC") into a 2-digit text month name ("01" ... "12").
    Basically all I need is to add a leading zero to months less than 10 (Jan~Sept), but need a formula to work with any month text.

    Can you help, please? Thanks so much.

  39. Can Someone help me convert this text to date?

    Nov 26, 2020

  40. Hi, Good day!
    Can you help me to convert day into date, eg. Saturday, 6:30 pm (as shows in outlook email), need to change it as 21-Nov-2020,6 :30 pm in excel.
    Also if not possible, kindly help to split day and time into 2 separate columns in excel.
    This would help me to find the duration of the task completed.
    Thanks dear

    1. Hello!
      Saturday, 6:30 pm cannot be converted to a date since there are 52 Saturdays in the year.
      Please check out this article to learn how to split text in a cell.

  41. Hello guys.
    I have an excel with dates. I have a several dates like: nov/7/2020, then when I convert to dates with dd/mm/yyyy format it works fine but bellow the last date with november, the october's dates start, and those dates like this: oct/1/2020, doesn't work the date conversion via "Text" to Column".
    I need help
    Bests regards. Thanks a lot.

    1. Hello!
      I’m sorry but your task is not entirely clear to me. For me to be able to help you better, please describe your task in more detail. Please specify what you were trying to find, what formula you used and what problem or error occurred. Give an example of the source data and the expected result.
      It’ll help me understand it better and find a solution for you. Thank you.

  42. Hi,

    Can someone help with following format please? I need change the format for date e.g 21/09/2020, but it comes up as 5 digit number e.g 44095. How to convert this number to date please? thank you

  43. Hi,

    Can someone help with following format please? I need change the format for date e.g 21/09/2020, but it comes up as 5 digit number e.g 44095. thank you

  44. Hey,
    Could someone help me covert following:

    Oct 30, 2020 (as a text from a .cvs file) - > date
    Aim will be to sort the dates from oldest to newest at the end (what is not possible at this point)

    Many thanks in advance,

    1. Hello!
      Please try the following formula to convert text to date:

      =DATE(RIGHT(D1,4),MONTH(1&LEFT(D1,3)),MID(D1,5,2))

      I hope my advice will help you solve your task.

  45. Hey, what can I do so that if I enter the number 240402 in A1, I get 24/04/02 in the same cell?

  46. Hai Alex,

    Can you help me?

    I want to convert "September 30th 2020, 17:00:08.680" to "October 1st 2020, 00:00:08.680", so i want to change to +8 with automatically change the date and time.

    Any idea how to do that automatically?

    Thank you Alex.

    1. Hello!
      Please have a look at this comment.
      Add to date 1. If your data is written in cell D1, and the new date is in D3, then you can use the formula

      =TEXT(D3,"mmmm d yyyy")&MID(D1,SEARCH(",",D1,1),50)

      I hope my advice will help you solve your task.

  47. Thanks a lot.

  48. Hello Alexander,

    First of all, great work here helping everybody out! Thanks for that.

    My question is a bit different from most. I have dates like this: early Jan 2021, middle Jan 2021 and late Jan 2021 for example.

    From this info a new date should appear in the next column.
    - Early Jan 2021 > 25-02-2021
    - Middle Jan 2021 > 05-03-2021
    - Late Jan 2021 > 15-03-2021

    I can turn Early Jan 2021 into dates by using the replace function to turn the word Early into 05.
    My question is how do I go from 05 Jan 2021 in one cell to 25-02-2021 in another?

    I hope I made myself clear.
    Thanks again!

    1. Hello!
      If I understand your task correctly, the following formula should work for you:

      =DATEVALUE(SUBSTITUTE(A1,"Early",5,1))+51

      I hope this will help, otherwise please do not hesitate to contact me anytime.

      1. Also would there be a way to make the formula conditional? So Early would be changed to 5, Middle would be changed to 15 and Late would be changed to 25?

        1. Hello!
          I wrote this formula based on the description you provided in your original comment. Please note that if you’d provided me with the precise and detailed description of your task from the very beginning, I’d have created a different formula that would have worked for your case.

          =IF(ISNUMBER(SEARCH("early",A1,1)), EDATE(DATEVALUE("1 "&MID(A1,FIND(" ",A1,1)+1,50)),1)+24,
          IF(ISNUMBER(SEARCH("middle",A1,1)), EOMONTH(EDATE(DATEVALUE("1 "&MID(A1,FIND(" ",A1,1)+1,50)),1),0)+5,
          IF(ISNUMBER(SEARCH("late",A1,1)), EOMONTH(EDATE(DATEVALUE("1 "&MID(A1,FIND(" ",A1,1)+1,50)),1),0)+15,"")))

      2. Thank you for your help Alexander and it works, but only for January. If I use February for example it does not return 25-03-2021 but instead 28-03-2021. I guess what I am trying is a bit too complicated.

      3. I'm sorry, I don't quite understand what's happening in this formula.
        What should be in cell A1 in this example? And what would be the output of this formula?

          1. Ah! If I delete the instance number it works! Thank you so much.

          2. I think my Excel might be different. The variables for my SUBSTITUTE function are: (text; old_text; new_text; [instance_num])

  49. I'm having a hard time with this:
    C2 has a custom date format of 2020-10-21 and is set using custom formatting built into Excel
    D2 has a static general formatted number of lets say 0085001832000
    E2 has a digit, lets use 9
    F2 has a custom formatted "date" that was added to the custom formatting selection of yymmdd and is pulling from the date from C2 in a yyyy-mm-dd custom format (i.e. 201021).
    G2 has a number in a text format of 20200045
    My issue is when I try to combine D2, E2, F2 and G2 into H2, everything works out great with the exception of the Date from cell F2. The combined string says 00850018320009"1144124"20200045. I'm not sure what's happening with the area in "" which should be the date information in F2. Anyone have any idea why it's converting it into what appears to be a number that represents the date?

    1. Hello!
      Dates are stored in Excel as numbers. If you want to add a date to a text string, then you need to convert the date to text with TEXT function. Read more in this guide.
      If there is anything else I can help you with, please let me know.

  50. I managed to convert some dates as MMDDYYYY to MM-YR. However, only half converted. The rest remains as MMDDYYYY. How do I format the balance?

    1. Hello!
      Unfortunately, without seeing your data it is difficult to give you any advice. Please provide me with an example of the source data and the expected result. I can assume that some dates are written as text. What does format mean MM-YR ??

      1. It means Month-Year

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