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 2. Total comments: 511

  1. how to change this date format December 14, 2023 to 14/12/2023

  2. Hi,

    I was trying to convert this 10012023 to 10/01/2023, i tried this formula, =TEXT(DATE(RIGHT(T4,4),LEFT(T4,1),MID(T4,2,2)),"MM/DD/YYYY") but the result is this 01012023.

    Thank you for the help

    1. Hi! Please pay attention which characters you extract using the LEFT and MID functions. Try the formula:

      =TEXT(DATE(RIGHT(T4,4),LEFT(T4,2),MID(T4,4,2)),"MM/DD/YYYY")

      You can use the formula

      =DATE(RIGHT(T4,4),LEFT(T4,2),MID(T4,4,2))

      Apply the date format "MM/DD/YYYYYY".

      1. 26/07/2021-15/09/2021 need to change it to

        07/26/2021-09/15/2021 the above formula is giving wrong answer

        1. Hi! Your data is not dates, but a text string. You can extract the start and end dates using the LEFT, MID, and RIGHT functions. Then, convert them to actual dates using DATEVALUE.

          =DATEVALUE(LEFT(A1, 10))
          =DATEVALUE(RIGHT(A1, 10))
          =DATEVALUE(MID(A1, 12, 10))

          I can also offer a solution without the formulas.
          If you prefer a non-formula approach, you can use the Text to Columns wizard:
          Select the cell with the date range.
          Go to the “Data” tab and click “Text to Columns.”
          Choose the delimiter (-) and specify the format for the resulting column (e.g., “Date”).
          Click “Finish” to convert the text dates to two actual dates.

  3. HI,

    This is awesome. Thank you very much. i have my date imported in this format ~40464%, please how do i convert it.

  4. Hi Dev Team,
    I have the data like this :
    Sep 26, 1989

    But I apply the date with this formulae:
    =DATE(YEAR(cell),MONTH(cell),DAY(cell)).

    It shows me error. PLease suggest me

    1. Hi! If I understand correctly, your date is written as text. Use substring functions to extract separate parts of the date from the text string. Try this formula:

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

      I recommend paying attention to the Text to Date tool. This tool easily converts text strings of different formats to dates. 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. I will really pay the attention on this.
        Thanks 1000 times.

  5. 2023/10/09 08:36:51

    Hi, I am struggling to put this date and time format in general or text format that still displays as per above that it can be utilized in a pivot table.
    If I put it in general format it displays as

    45208 3589236111

  6. GMT
    2022_121:00:00:00.000
    2022_122:00:00:00.000
    2022_123:00:00:00.000
    2022_126:00:00:00.000

    I need to convert the above GMT time to a time usable by excel.
    The above is what is entered into the cell by the data pull. I need to pull a time delta between the value, but i need to convert into something usable by excel.
    THanks

  7. Hi can any one share that how we can add text in my date in once cell showing e.g (Date as of : 03-Oct-23)

  8. Wow this is fantastic info! What formula would convert Aug182023 to a date format? All the dates have a three letter month, two number day, and four number year. (I don't want to use the Text to Columns wizard.)

  9. I'm trying to change this 2016-08-08T13:00:00.000Z to 2016-08-08 in excel

  10. Need to convert YYMMDDHHMM to DDMMYY format. Can someone help

  11. Dear Svetlana, Alexander,

    Unless I misread and missed an answer to my issue in your post and other solutions, would you be able to give a hint how to deal with the following situation?

    I am working on a Dutch machine and receive dates hard-coded in the csv file as "21MAR2024". Data import GUI offers "DMY" option how you suggest, but my Excel is unable to read the result. It would it read it had it "21MRT2024" written been.

    Without installing another language pack, writing a macro or substituting "MAR" with "MRT", is there any other way to force Excel to recognize the correct date in my case?

    Regards,

    1. Hi! Excel in dates uses your computer's local settings. So the best choice, I think, would be substituting.

      1. Dear Alexander,

        Thank you for a quick answer!

  12. I have a date that shows as 18/11/2015 10:04:38 AM that is generated from an external database. The number format is General. I have tried several tips you suggested above to convert to a date format i can use for my pivot table, but none works. Can you help?

  13. how to covert Friday, March 03, 2023 4:42:12 PM to 3/3/2023 16:42

  14. if Feb'23 then result should be 1-Feb-2023 and in next cell 28-Feb-2023 (ie. start date and end date); if its Jan23-Mar23 it should be 1-Jan-2023 and 31-Mar-2023. Please help me with this

  15. I have dates in the format 02-12-2022 and I want to convert them to 02-Dec-2022. I am using the below formula but it does not see to work after day 12th.

    =IF(ISNUMBER(C2),DATE(YEAR(C2),DAY(C2),MONTH(C2)),DATE(MID(C2,LEN(C2)-3,4),LEFT(C2,LEN(C2)-8),MID(C2,LEN(C2)-6,2)))

    If I apply this formula to 12-12-2022 it gives me 12-Dec-2022 but as soon as the day value changes to 13 i.e, 13-12-2022 it gives 12-Jan-2023.

    Please help!!!!

    1. Hi!
      If I understand your task correctly, try the following formula:

      =IF(ISNUMBER(C2),DATE(YEAR(C2),DAY(C2),MONTH(C2)),DATE(MID(C2,LEN(C2)-3,4),MID(C2,LEN(C2)-6,2),LEFT(C2,LEN(C2)-8)))

      I'd recommend you to have a look at our Text to Date tool. This simple tool effortlessly converts text strings of different formats into dates without formulas. 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. Hello. Is there a way to translate a sequence of numbers in to date and time. As an example, to convert 2303252000 to 25/03/2023 20:00. This format is used by ICAO on NOTAM YYMMDDhhmm.

      1. Hello. Thank you very much for your reply. It was very helpfull! I used the following formula (with ; rather , (excel2007 user :( ) and adding 20& for 2000+ year and it worked
        =DATE((20&MID(A1;1;2));MID(A1;3;2);MID(A1;5;2))+TIME(MID(A1;7;2);MID(A1;9;2);0)

        Thank you very much again!

  17. Hello,
    Im trying to insert data from MSSQL with SSIS into Excel. In MSSQL date is DATETIME or DATETIME2, everything looks good until insert into excel when excel formats it by itself to DATE without TIME in the rows and date with time is shown only in formula box. How to set Excel so it will show exactly as im importing from database with DATETIME in rows and it is DATE type in excel also with time in rows?
    Thanks for your help.

  18. Hi. I have a problem that I can not solve. I have a Serial Number of equipment i.e 1Z6A08655AT. I need to take out from it the production date - these is represented by two first characters 1Z. 1 stands for 2021, Z stands for December.

    First character indicating Year
    1 is 2021,
    2 is 2022,
    3 is 2023
    ...

    Second represent months:
    1,2,3,4,5,6,7,8,9, X,Y,Z

    I need to have outcome in mmm-yy format

    No idea how to deal with it. Appreciate if you could share how to fix this ;)

    1. Hi!
      Use the MATCH function to find a match for the month character.

      =MATCH(MID(A1,2,1), {"1","2","3","4","5","6","7","8","9","X","Y","Z"}, 0)

      To get the year, use this formula

      LEFT(A1,1)+2020

      Use the DATE function to get the production date.

      =DATE(LEFT(A1,1)+2020, MATCH(MID(A1,2,1), {"1","2","3","4","5","6","7","8","9","X","Y","Z"},0),1)

      Hope this is what you need.

      1. This was awesome. I just coincidentally had the same challenge and this helped massively. Thank you

  19. Hi,

    I have extracted data from my netbank and receive the dates in decimal numbers, such as;
    Date
    0,150497685
    0,108831019
    0,067164352
    0,858136574

    How can I concert this to dates?

  20. Hi,

    I have dates in the format 02-12-2022 and I want to convert them to 02-Dec-2022. I am using the below formula but it does not see to work after day 12th.

    =IF(ISNUMBER(C2),DATE(YEAR(C2),DAY(C2),MONTH(C2)),DATE(MID(C2,LEN(C2)-3,4),LEFT(C2,LEN(C2)-8),MID(C2,LEN(C2)-6,2)))

    If I apply this formula to 12-12-2022 it gives me 12-Dec-2022 but as soon as the day value changes to 13 i.e, 13-12-2022 it gives 12-Jan-2023.

    Can you please help me?

    1. Hi Team,

      Can someone please respond here?

  21. By reading the comments I am sure you can help. I exported a report from a tool, the report has a date column but the column has two different date formats, one format interprets the date correctly, but the other format interprets the day as the month, so even all data is from January from example, when I filter the date what should be Jan 1- Jan 12 shows as Jan 1st, Feb 1st, march 1st and so on, how can I correct that, Thank you in advance.

    1. I found a solution.

  22. I have the below text structure downloaded from a dataset that I need to convert into a date format that SPSS or Jamovi can use. I do not need the day of the week, just a numeric MM/DD/YYYY. I've tried converting by formatting the cells as dates, using the flash fill tool, and using =DATEVALUE, but none worked (no change at all). I will ultimately have thousands of entries; so, manual conversion is not feasible! Any ideas on how I can convert? The original date selection was made using a calendar tool where one selects a specific date and the text date is produced in the .csv download, if that's helpful.

    Mon Dec 27 2021

    Thank you!

    1. Hello!
      Use substring functions to extract the desired parts of the text for the DATE function. We have a more universal and simple solution that will solve the problem in a couple of clicks. I recommend paying attention to 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.

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

      1. Thanks so much! I'll give these recommendations a try.

  23. Hey there! I am trying to show a range of dates - "Number of Days" - that calls back to two other dates outside the print area. But the two dates keep showing up as text.

    I am using the following super basic formula. =C9&" ("&C11&" - "&C10&")"

    C11 and C10 are my two dates that show up as text. I want them to show up s mm/dd/yyyy. Can you help a newb out?

  24. Hi, I've got

    "Tue Nov 29 2022 15:50:53 GMT-0600 (Central Standard Time)" and would like it formatted into: "MM YYYY"

    Any ideas are greatly appreciated! Thank you!

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Post a comment



Thanks for your comment! Please note that all comments are pre-moderated, and off-topic ones may be deleted.
For faster help, please keep your question clear and concise. While we can't guarantee a reply to every question, we'll do our best to respond :)