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 4. Total comments: 740

  1. Hi, hope you can help

    I have the following "Apr 3, 2021 3:54am" and want to convert it to UK Date format using a formula - 03/04/21

    I know I can use the text to columns function but there are multiple columns like this so would be helpful if there was a formula, I tried looking above at the other solutions but could not see one like mine

    Thanks in advance

    1. Hello!
      To convert your text to a date, use this formula.

      =DATE(MID(A1,FIND("~",SUBSTITUTE(A1," ","~",2),1)+1,4), VLOOKUP(LEFT(A1,3), {"JAN",1;"FEB",2;"MAR",3;"APR",4;"May",5;"JUN",6;"JUL",7;"AUG",8;"Sep",9;"OCT",10;"NOV",11;"DEC",12},2,0), MID(A1,FIND(" ",A1,1)+1,SEARCH(",",A1,1)-5))

      In one click, the text is converted to a date using the Text to Date tool. 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.

  2. Here's a "neat" method to convert an 8-digit number into a valid dd/mm/yyyy date:
    (Don't forget to format the result as "short date" 😎 )

    if cell A1 contains: 20220512

    then this formula: =TEXT(A1,"0000\/00\/00")+0

    will yield the date: 12/05/2022

    This is, of course, much simpler method than your solution, explained above:
    =DATE(RIGHT(A1,4), MID(A1,3,2), LEFT(A1,2))

    Enjoy

  3. I'm doing a complicated append formula and when I pull the cell that has the date information it translates it into the Date Value. Is there a way inside of the append formula to have it display the date from the date value? For example, Y2 holds the date displayed as 4/1/2022
    My append formula (I'm keeping it simple as there are about 10 other appends in the cell I'm merging everything into) is ="Paid Thru Date: "&Y2&"... "
    This formula is displaying: Paid Thru Date: 44652...
    I would like it to display: Paid Thru Date: 4/1/2022...
    I can't quite figure out how to get it to convert that 44652 back to the 4/1/2022 inside of the appended formula

    1. Hello!
      To display the date as text, use the TEXT function when concatenating values.
      Please check the formula below, it should work for you:

      ="Paid Thru Date: "&TEXT(Y2,"m/d/yyyy")

      1. Awesome - Thank you so much!

  4. Hey! :)
    I see you explained how to go from an 8 digit number to a date, is it possible to do it the other way around? I need to generate a specific 8-digit code for every test I do, and connect it to the specific date. In some instances, I run more than one test a day so I need to add an extra digit at the end. Eg: on 10.11.2022 I run 3 tests and I need to convert the date to 101120221, 101120222 and 101120223, or even more ideally to a six digit + 1 (1011221, 1011222, 1011223)
    I would appreciate your help

    1. Hello!
      To count the number of matching dates, use the COUNTIF function.
      You can convert a date to a number using the TEXT function and the "ddmmyy" format.

      =IF(COUNTIF($A$1:$A$100,A1)=1, TEXT(A1,"ddmmyy"), TEXT(A1,"ddmmyy")&COUNTIF($A$1:A1,A1))

      Copy this formula down along the column.
      Hope this is what you need.

      1. Thank you for your help.

  5. how can I calculate a month like "January" into Days

  6. Trying to convert text to dates: example : 15-01-1749 (old church records from the 1500's to 1900's)
    Used this formula, like your example:
    =DATE(RIGHT(B14,4),MID(B14,4,2),LEFT(B14,2))

    It returned: 15-Jan-3649

    All of the day/months are correct, but all the years are off by 1900 years.
    What am I doing wrong? Thank you for your help.
    Ann

    1. Hello!
      Excel does not work with dates prior to 1900. If the year number is between 0 and 1899 inclusive, Excel calculates the year by adding that number to 1900.

      1. Thank you - I thought I was losing my mind!
        Trying to think of a workaround. I can make value combined with the right function to turn the year into a number in a new column, and then just display the m/d in a separate column. Is that the best I can do?
        Would any of the date functions in Ablebits help me with this?

        I'm taking data from Family Tree Maker for church parishes with birth, marriage and death dates, with other text fields, so I need to be able to play with different sorts to come up with birth rates, causes of death over time, and other statistical info. Lots of calendar math needed. There are between 7-17k records in each of the 3 parishes.

        Once again, thank your for your help,
        Ann

        1. Hello!
          Extract the year from the date using the YEAR function and write the correct year in a separate cell by subtracting 1900.

          YEAR(DATE(RIGHT(B14,4),MID(B14,4,2),LEFT(B14,2)))-1900

  7. In A1 i have 1984 B1 i have Apple
    A2 I have Jun-1990 B2 i have Orange
    A3 i have 01-May-2020 B3 I have Grape

    i need to join the text as in C1 =Apple (1984)
    C2 =Orange (Jun-1990)
    C3 = Grape (01-May-2020)
    Used text function its not working for C1.

    Please help me in resolving this issue

      1. i have year as 1984 in A1, and 28-Feb-18 in A2,

        if use text to convert it date, 1984 comes as 07-Jul-1909, A2 comes as 28-Feb-2018 correctly, but i need it 1984 as 1984 only instead of 07-Jul-1909.

        1. Hi!
          How can I help if you do not answer my questions and the problem description is completely unclear?

  8. Is it possible determine the number of days from a month text such as "August"

    Many thanks

    1. Hello!
      Convert text to date and determine the first day of the month using the DATEVALUE function. Determine the last day of the month using the EOMONTH function. Find the difference between dates and add 1 day.
      Please check the formula below, it should work for you:

      =EOMONTH(DATEVALUE(1&A1),0)-DATEVALUE(1&A1)+1

  9. Hey,

    I need to covert 20200818 (Aug 18 - 2020) to be 08/18/2020 (mm/dd/yyyy) using a single formula!
    Please help!

    1. Hi!
      Pay attention to the following paragraph of the article above – How to convert 8-digit number to date in Excel.
      It covers your case completely.

  10. How could I convert this to date? JAN01/19
    I'd be forever grateful!

  11. I have two strings.
    #1: 72222 and need it to be 7/22/22
    #2: 120121 and need it to be 12/1/21

    Thank you!

  12. hello
    i have a set of text 551231-02-4556 in which i would extract the first 6 character to be converted to date value result to be 31-12-1955

    any solution to this?
    Thank you

      1. Thank you very much, it works, great.

  13. Hello, Mr. Alexander.. Is there a way to swap the data and month VALUE? Not just the formatting. When I paste date values from a text file or PDF, Excel auto-interprets the values incorrectly. I.e. 02-03-2019 is imported as 03rd Feb instead of 02nd Mar. Simply changing the formatting does not fix this problem.

  14. I have date formatted field(12/20/1984) in excel, when i am saving data as xml. Date is converted to number. What i need to do in excel to save this as date. I used below custom validation to allow field string(Delete) and date

    =OR(AND(ISNUMBER(J3),LEFT(CELL("format",J3),1)="D"),J3="Delete")

    1. Hi!
      Excel stores the date as a number. The .xml format does not have a date format. Save the file as an Excel file (.xlsx) and then you can write the date as a date, not as a number.

      1. Final data I need in XML . I am not clear on statement - Save the file as an Excel file (.xlsx) and then you can write the date as a date, not as a number. Can you give me one example.

          1. Thank You Alex.

  15. How do I convert this date

    Jan-01-1985 TO 1985-01-01

    1. Hello!
      To convert text to date, extract the desired characters using the substring functions.

      =DATE(MID(A1,FIND("~",SUBSTITUTE(A1,"-","~",2),1)+1,4), VLOOKUP(LEFT(A1,3), {"JAN",1;"FEB",2;"MAR",3;"APR",4;"May",5;"JUN",6;"JUL",7;"AUG",8;"Sep",9;"OCT",10;"NOV",11;"DEC",12},2,0), MID(A1,FIND("-",A1,1)+1,2))

      Then use DATE function. Set the cell to the custom date format that you need. For example, "yyyy-mm-dd".

      To convert text to date, you can also use the Text to Date tool.
      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.

  16. Hi, I have this exact date values in my worksheet:
    a. 10/06/2022 8:00:48 p. m. ----> Type: Date ---> Format: d/mm/yyyy h:mm:ss AM/PM
    b. 10/06/2022 8:00:48 p. m. ----> Type: General ---> Format: d/mm/yyyy h:mm:ss AM/PM

    When I compare a=b the result is FALSE. I can't convert b. to date but I'm seeing the exact same values.
    Any suggestions would be very welcome.

  17. Hi I have this data of date from CSV 2022-06-12T20:30:59.611Z may I know how to convert it to Date & Time in excel?

  18. Current format: 12 25 20 22
    How can I convert this to 12/25/2022??

  19. You own a Altroz XM+ 1.2 P car no UK00AA0000;you had serviced your vehicle from OBERAI MOTORS (0000000-Sv&Pa-Dehradun-OberaiM)3 days ago on 29-APR-22,which was a Second Free Service. I require your feedback regarding the same. I Need 29-APR-22 separately from this sentence using a formula in excel. Plz help out.

  20. My formula is not working and not sure what I am doing incorrectly:
    Original w/formula needs to be
    9122004 12/9/2004 9/12/2004

    Here is the formula used:

    =DATE(RIGHT(O434,4),MID(O434,2,2),LEFT(O434,1))

    Any help is appreciated

      1. I am looking to get the out put of 9/12/2004

  21. Monday, February 21, 2022
    WANT IN 02-21-2022

  22. hello
    i typed so many dates
    and i suddenly received a combination of numbers and text in the cell, and I need to return the value to date
    these are examples of the values I'm receiving
    30637514H
    310038084H

  23. Thank you! Great help!

  24. HELP? There is no way to convert this "2022-FEB" from text to a date. I have 40 charts to build each where, and the exports always put the months/years in text format. I am spending time manually retyping the dates for them to work. Is there no formula to make the date a REAL date in excel, All the methods online wont work for this?

    1. Hello!
      Here is the formula that should work perfectly for you:

      =DATE(LEFT(A1,4),VLOOKUP(RIGHT(A1,3), {"JAN",1;"FEB",2;"MAR",3;"APR",4;"May",5;"JUN",6;"JUL",7;"AUG",8;"Sep",9;"OCT",10;"NOV",11;"DEC",12}, 2,0),1)

      You can learn more about DATE function in Excel in this article on our blog.

  25. Hi! Could someone help me if there is a formula to convert general format 07-12-2021 to a date format of 07-Dec-2021? I tried changing the format but it doesn’t work. Thank you!

  26. This blog is much helpful. Thanks Ablebits team. Especially Mr. Alexander Trifuntov.

  27. How can I convert a lot code into a date? Lot code is XX02282A which iquals 02/28/2022 which is an expiry date: PPMMDDYS
    XX - producing plant code
    02 - month
    28 - day
    2 for the year
    A - producing shift

    1. Hello!
      If I got you right, the formula below will help you with your task:

      =DATE("202"&MID(A1,7,1),MID(A1,3,2),MID(A1,5,2))

      You can learn more about DATE function in Excel in this article on our blog.

  28. I have a column of dates as e.g. 31 Mar, 2022 and have been using the formula =DATE(RIGHT(E4,4), MONTH(DATEVALUE(LEFT(E4,3)&"1")), MID(E4,5,FIND(",",E4)-FIND(" ",E4)-1)). It's a weekly report that I convert but lately it has been throwing back #VALUE!. Do you have any idea why one week it will work and another it won't. I can't see any change in the original dates including any spaces. Thank you.

    1. Hello!
      This formula converts the text "Mar 31, 2022" into a date. To convert the text "31 Mar, 2022" to date, you can use the DATEVALUE function, as described in the article above.

  29. Importing a text file date shows as per left column... desired output in right column.. Any formula recommendations to get the desired result? (issue is that excel drops the zero in front of the first string)

    String text Desired Answer
    7122021 12/7/2021
    25102021 10/25/2021

  30. Hi guys
    Any way to convert 2m 19d (2 months and 19 days...!) to an excel recognised date format?
    I realise it is the most ridiculous export, please help!

    1. Hello!
      The information you provided is not enough to understand your case and give you any advice. There are a different number of days in a month. What should be the result? March 19, 1900?

      1. I have a similar request for help. My Excel sheet has the length of service written as "1 year 2 months 28 days". Can I write a formula to convert that to total number of days? And then can Excel convert the number of days to determine the exact hire date.

  31. How to convert this text value to date only:
    Wed Sep 01 00:07:00 UTC 2021

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

      =DATE(RIGHT(A1,4),VLOOKUP(MID(A1,5,3), {"JAN",1;"FEB",2;"MAR",3;"APR",4;"May",5;"JUN",6;"JUL",7;"AUG",8;"Sep",9;"OCT",10;"NOV",11;"DEC",12},2,0), MID(A1,9,2))+ TIMEVALUE(MID(A1,12,8))

  32. Hi , I have a question.
    Emp ID Name Tenure Year Tenure Month Workout Core Time Productivity Productivity Q2 Productivity Q3 Workout Q2 Workout Q3 Time Worked
    20 Pat 2 10 1064.99 893.65 119.17% 119.17% 120.82% 1064.99 1079.71 1008.52
    30 Him 5 9 502.15 421.47 119.14% 119.14% 120.82% 502.15 509.22 469
    32 Piy 2 7 825.75 697.75 118.34% 118.34% 120.82% 825.75 843.04 926
    34 Sak 2 4 939.56 811.97 115.71% 115.71% 120.82% 939.56 981.03 950.6
    33 Rav 8 8 917.66 803.18 114.25% 114.25% 120.82% 917.66 970.41 943.38
    28 Dee 2 9 861.31 754.28 114.19% 114.22% 120.82% 861.54 911.30 902.3
    35 Sam 6 2 907.71 807.12 112.46% 114.22% 120.82% 921.92 975.17 908.45
    31 Om 9 0 892.36 797.5 111.89% 114.22% 120.82% 910.94 963.56 938.4
    19 Log 5 8 834.71 788.82 105.82% 114.22% 120.82% 900.97 953.01 908.28

    Based on this data I want to get maximum and minimum potential benefits from Productivity @ quartile 2 and quartile 3.
    Productivity baseline = sum of workout / sum of core time.
    Productivity at quartile 2 = total workout at Quartile 2 / core time and
    Productivity at quartile 3 = total workout at Quartile 3 / core time and
    and productivity variance

    can you please help
    thanks, Maddy

    1. Hi!
      If I understand correctly, your task is to find the sum of the column and divide the numbers. This has nothing to do with the topic of the article. If you have a question about a specific formula, ask it in the required section.

  33. SAMPLE…
    DATA A1 6
    A2 EA
    A3 11/04/21

    USED FORMULA: =CONCATENATE("SHORTAGE OF ",ABS(A1),"(",A2,")",", ISSUED ON ",A3," (NO VARIANCE)"
    OUTPUT : SHORTAGE OF 6(EA), ISSUED ON 44297 (NO VARIANCE)

    How to convert the value format 44297 to date format using the formula.

    1. formula:
      =TEXT(44297;"mm/dd/yy")
      results in:
      00/dd/yy
      Can you pls help?

      1. Hi!
        The date in Excel is stored as a number. But you must provide to the TEXT function not a number, but a date. Use the DATE function:

        =TEXT(DATE(2021;4;11);"mm/dd/yy")

        I hope it’ll be helpful.

    2. Hello!
      To convert a number to a date in text form, replace 44297 with the formula:

      TEXT(44297,"mm/dd/yy")

      1. Thanks for the help... Happy.

  34. Hello,
    The date in csv document was exported as the following:
    1.57775E+12 (it displays as 1577750400000) in the formula field.
    The cells with the dates are in General format.
    How can I convert such number in the Date?
    All the dates supposed to be Dec.31 of various years.

    Thank you very much for your help!

  35. Hi Alexander, I have a text format like this 15/06/2021 and I want to convert as date 2021/09/22.

  36. I have data from an export that gave me dates formatted as text or general (applying other pre-formatted date styles has no effect): 960212 (YYMMDD) and I need to convert it to a date in this format: 02/12/1996 (MM/DD/YYYY).
    Any help for this?

    Ken

      1. You are a genius, thank you so much. Was struggling a lot because of this.

  37. Hi, I would like to convert this date and time string (Nov 15, 2019 02:34 PM) exported from a system to a valid date field in excel so that I can use the =WORKINGDAYS formula. Many thanks Laura

    1. Hello!
      How to convert your text to date and time, see this comment.
      I hope I answered your question. If you have any other questions, please don’t hesitate to ask.

      1. Hi Alexander, the formula works however, it returns a #VALUE error when the date is a single date eg. 4th rather than 14th of a month.
        Jan 4, 2021 09:22 AM returns #VALUE!
        Jan 15, 2021 08:27 AM returns 15/01/2021 as expected.
        Can you help me identify what in the formula needs to change or if there is a second step I need to complete. The WORKINGDAYS formula works well on the result

        1. Hi!
          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.

          =DATE(MID(A1,FIND("~",SUBSTITUTE(A1," ","~",2),1)+1,4), VLOOKUP(LEFT(A1,3), {"JAN",1;"FEB",2;"MAR",3;"APR",4;"May",5;"JUN",6;"JUL",7;"AUG",8;"Sep",9;"OCT",10;"NOV",11;"DEC",12},2,0), MID(A1,FIND(" ",A1,1)+1,SEARCH(",",A1,1)-5))

          1. Thank you for your help.

  38. Hello all,

    I have a question, I get amount of data as a text genereated in .csv file, for e.g. "02-NOV-18" I would like to convert this as a date in excel format "yyyy-mm-dd". I was trying to use text as a column method, but nothing change, or when I use formula =Value or date.value nothing also change. Someone can help me with that ? Thanks in advance

  39. I WANT TO CONVERT A DATE INTO A DAY EXAMPLE 28/02/1968 IN A GRAPH I WANT TO HAVE ONLY 28 THE LABLE THE THE GRAPH AS BELOW FREBRUARY

  40. Hello, my question is how can i convert text to set as date example 12232021 >>> 12/23/2021

    1. Hi!
      Read the article above carefully and pay attention to the paragraph: How to convert 8-digit numbers to date in Excel.

  41. What is the equivalent function in excel of the "to_date" in Google Sheets?

    Thanks in advance!

  42. I need to convert a text string with military time and date to number so that can calculate total time in minutes. Its currently in hhmm ddmmmyyyyy (i.e start 2345 04JUL2020 stop 0231 05JUL2020). I would appreciate any help.

    1. Hello!
      Please use the following formula/the formula below to solve your task:

      =DATE(RIGHT(A1,4),VLOOKUP(MID(A1,8,3), {"JAN",1;"FEB",2;"MAR",3;"APR",4;"May",5;"JUN",6;"JUL",7;"AUG",8;"Sep",9;"OCT",10;"NOV",11;"DEC",12},2,0), MID(A1,6,2)) + TIME(LEFT(A1,2),MID(A1,3,2),0)

      1. Good Morning Alexander

        This formula was just what I needed.

        Thanks for the tip.

        --Mark

  43. I have this text output from an sql query, but I need to convert it to date so it can be sorted by date and time. Apr 14 2021 8:15AM. I would appreciate your advice.

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

      =DATE(MID(A1,FIND("~",SUBSTITUTE(A1," ","~",2),1)+1,4), VLOOKUP(LEFT(A1,3), {"JAN",1;"FEB",2;"MAR",3;"APR",4;"May",5;"JUN",6;"JUL",7;"AUG",8;"Sep",9;"OCT",10;"NOV",11;"DEC",12},2,0), MID(A1,FIND(" ",A1,1)+1,2)) + TIMEVALUE(MID(A1,LEN(A1)-6,5)&" "&RIGHT(A1,2))

      Set the date and time format in this cell.

  44. Thank you for such an informative article!
    Everything one would wanna know about date settings in Excel has been provided beautifully in this article.

  45. Hello there,

    Hope you are doing well. We normally received our date in the format of 16-July-2021. Would it be possible to convert the original date into a value like YYYYMMDD (example 20210716)? I tried to use the custom formatting option to give me YYYYMMDD and while it does display the right value, the formula bar still shows the slash units (example 2021/07/16). Is it possible to remove the slashes in the formula bar?

    Thank you for your time!

  46. Hello there,

    Hope you are doing well. We normally received our date in the format of 16-July-2021. Would it be possible to convert the original date into a value like YYYYMMDD (example 20210716)? I tried to use the custom formatting option to give me YYYYMMDD and while it does display the right value, the formula bar still shows the slash units (example 2021/07/16). Is it possible to remove the slashes in the formula bar?

    Thank you for your time!

    1. Hello!
      You can learn more about convert date to text in Excel in this article on our blog.

      =TEXT(D1,"yyyymmdd")

      The formula bar shows the real value. If the value is written as a date, then you cannot remove the slash in the formula bar.

  47. Tried various option to change text date and time to real date and time but no luck. 13/6/2021 9:30:00 PM
    Can u help on it

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

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

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

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