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 10. Total comments: 731

  1. Hi there!
    I have a narration in cell D2 like so "ELECTRICITY CHARGES Bill for the month of April 2019".

    Is there a formula which would return just the month i.e. "April"? I want to be able to use that for a pivot.

    1. Hello!
      To extract the penultimate word from the text, use the formula

      =MID(A1,FIND("*",SUBSTITUTE(A1," ","*",LEN(A1)-1- LEN(SUBSTITUTE(A1," ",""))),1)+1, FIND("*",SUBSTITUTE(A1," ","*",LEN(A1)- LEN(SUBSTITUTE(A1," ",""))),1)- FIND("*",SUBSTITUTE(A1," ","*", LEN(A1)-1-LEN(SUBSTITUTE(A1," ",""))),1)-1)

      Hope this is what you need.

  2. Sir,

    If my date is in this format 05-17-2020 so tell me which for i use for change the format in dd-mm-yyyy

    1. Hello!
      If I understand correctly, you want to change the date format.
      Check what date format you have installed on Windows. Go to Control Panel – Time – Region Settings – Change data formats
      If you do not want to change the date format for all files, use the second method.
      Please go to Format Cells, choose Number -> Custom Format and set
      dd-mm-yyyy

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

  3. hi,
    How Do to convert this text(250220) into date format(12-Feb-2020).
    250220 - DDMMYY to DDMMYYYY
    look forward your support
    regards
    satish

    1. If your system date format is DD-MM-YY, then you can use the formula to turn text into a date

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

      The second version of the formula

      =DATE("20"&RIGHT(D1,2),MID(D1,3,2),LEFT(D1,2))

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

  4. Hey Alexander,

    I'm trying to get a formula to turn "May 29th 2020, 23:58:18:000" (which comes in from an export file as a general format in a .csv excel sheet), into "2020-05-29" in one cell and "23:58:18" in a different cell both formatted into a date so that it can be filtered via pivot charts through the year, month, day, and time of day.

    I've tried a few different ways of doing this already, but am unable to get the formatting to work correctly, any suggestions?

    Thanks in advance, you seem very knowledgeable in excel!

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

      =DATEVALUE(MID(A10,SEARCH(" ",A10,1)+1, SEARCH("th",A10,1) -SEARCH(" ",A10,1)-1)&" "&LEFT(A10,SEARCH(" ",A10,1)-1) &MID(A10,SEARCH(",",A10,1)-5,5))

      and

      =TIMEVALUE(REPLACE(MID(A10,SEARCH(",",A10,1)+2,50), LEN(MID(A10,SEARCH(",",A10,1)+2,50))-4,4,""))

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

      1. Perfect,
        Thank you!

  5. Hello sir
    i have a huge excel file in notepad format when i convert it to excel and after applying text to column some date shows as text and swap date and month

    17/03/2020 (ok)
    03/07/2015(converted to text, but actual date is 07/03/2015)
    29/01/2015 (ok)
    29/01/2015 (ok)
    29/01/2015 (ok)
    27/03/2019 (ok)
    24/08/2015 (ok)
    16/05/2018 (ok)
    02/07/2015 (converted to text, but actual date is 07/03/2015)
    02/07/2015(converted to text, but actual date is 07/03/2015)
    like this
    how can i covert all into once with correct format

    best regards

  6. Hi Alexander,
    How I can convert the below date with time to date only dd/mm/yy?
    2019-11-13 07:10:24.858757 -> dd/mm/yy
    Best Regards

    1. Hello Arni,
      If you want to change the appearance of the cell, but not change its content, use the Custom Format. Please go to Format Cells, choose Number -> Custom Format and set format dd/mm/yy.

      If you want to remove the time value from the date, then use the formula that will refer to this cell

      =DATEVALUE(TEXT(D1,"mm/dd/yy hh:mm:ss"))

      where mm/dd/yy - is system date format.

      Check what date format you have installed on Windows. Go to Control Panel – Time – Region Settings – Change data formats
      I hope this will help, otherwise please do not hesitate to contact me anytime.

    2. Sorted now.

  7. Hello,
    I want to convert 4 digit number to date
    Example "0204" to date 02/04/2020.

    1. Hello Dilan!
      I hope you have studied the recommendations in the above tutorial. Extract the day number, extract the month number and add the year.

      =DATE(2020,LEFT(F1,2),RIGHT(F1,2))

      I hope it’ll be helpful.

  8. Hi,

    I want advice on a formula please, I want to to generate a future date 6 months in the future to be calculated from a start date and a word, so for example: the first date would be in one column, the word high in the next column would generate a future date 6 months on, but only generated when the word high is in the second column
    01/01/2020 High 01/06/2020

    Thank you

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

      =IF(B1="high",EDATE(A1,6),"")

      I hope this will help

  9. How do you convert this kind of text to date? For example, 7111978, 3151980. I tried using the convert text to date but I am not getting anywhere.

    Thank you

    1. Hello Barbara!
      The formula with which you can convert tex to date, I wrote above for you. Please note that in order for this formula to always work, the number of digits in the month number must always be 2.
      If you, for example, have written 1112019. How can the formula determine whether it is November 1 or January 11?

  10. Hi,

    Can someone please help me with converting text to date e.g 7111978. I tried using converting text to date. I followed the steps but still not getting anywhere.

    1. Hello Barbara!

      If I understand your task correctly, the following formula should work for you:

      =DATE(RIGHT(B5,4),MID(B5,LEN(B5)-5,2),LEFT(B5,LEN(B5)-6))

      I hope this will help

  11. Just some clarity, I want know if Excel can automatically detect a new place and automatically the dates change to that country from the way you entered them while in your country

  12. I’m having a small issue with a formula not always working.
    When I use =SUM(IF(F4>G4,F4-G4,G4-F4)) in cell F6, it does not always calculate correctly. So
    if F4 is 04/08/2020 09:18:00 PM and G4 is 04/08/2020 09:10:00 PM it returns 0:08:00 which is correct. But on the next line of if F5 is 04/09/2020 07:00:00 PM and G5 is 04/09/2020 09:49:00 AM i get ######################. Both F4 and F5 are formatted for date and time, while F6 is formatted for time.

    1. Hello Stan!
      I am sorry, it is difficult to say what may be the cause of the issue based only on your description. As far as I can see, you do not use cells G5 and F5 in your formula. Besides, you work only with one row so the SUM function is not necessary here. Might it has happened that you have written the formula incorrectly?
      I will try to be an extrasensory expert and suppose that you want to use the following formula:

      =SUM(IF(F4:F5 > G4:G5, F4:F5-G4:G5, G4:G5-F4:F5))

      If this is not what you need, please clarify your calculations so that I will help you better.

  13. how can i convert below number into Date
    1052019
    Basically it 05th of October 2019 but i need to convert into proper date format so can you help me out to solve this issue.

    1. Hello FURQAN,
      Please try the following formula:

      =DATE(RIGHT(TEXT(A1,"###"),4), LEFT(A1,LEN(TEXT(A1,"###"))-5), MID(TEXT(A1,"###"), LEN(TEXT(A1,"###"))-5,2))

      however, the date of October 15, recorded as 10152019, this formula will not calculate correctly.

      it is necessary that the day is always written as a two-digit number, for example, October 5 as 10052019. Then you can use this formula:

      =DATE(RIGHT(TEXT(A1,"###"),4), LEFT(A1,LEN(TEXT(A1,"###"))-6), MID(TEXT(A1,"###"),LEN(TEXT(A1,"###"))-5,2))

      I hope it’ll be helpful.

  14. What formula would work to convert two separate date headers into one and then converting them into the MM/DD/YYYY format? In the example below I have the birthday in column "E" and and birth year in column "F". I think ideally I would merge that to show January 20th, 1930 in one column and then change the format after. I tried concatenating those two and then format cells --> date but it wouldn't let me change the date that way.
    Example:
    Birthday Birth Year
    January 20th 1930

    1. Hello Matt!
      Please try the following formula:

      =DATEVALUE(MID(A2,FIND(" ",A2,1)+1,2) & "-"&LEFT(A2,FIND(" ",A2,1)) & "-"&B2)

      Then go to Format Cells, choose Number -> Date and set format you need.
      Hope you’ll find this information helpful.

  15. Hi, I have dates input as a continuous number - yyyymmdd and I would like to have it as a date - ddmmyyyy.
    For example, I have 20180808 in cell D2 - I was trying the following formula based on the above guidelines. Could you tell me where I'm going wrong?
    =DATE(LEFT(D2,4), MID(D2,5,2), RIGHT(D2,2))
    Thanks

    1. Hello Henry!
      To convert a number to a date, you must first convert it to text.
      Please try the following formula:

      =DATEVALUE(LEFT(TEXT(D1,"###"),4)&"-"&MID(TEXT(D1,"###"),5,2)&"-"&RIGHT(TEXT(D1,"###"),2))

      Then go to Format Cells, choose Number -> Date and set format you need.
      Hope you’ll find this information helpful.

      1. =DATEVALUE(LEFT(TEXT(D1,"###"),4)&"-"&MID(TEXT(D1,"###"),5,2)&"-"&RIGHT(TEXT(D1,"###"),2))

        if there is any wrong in this formula instead of above
        =RIGHT(D1,2)&"-"&MID(D1,5,2)&"-"&LEFT(D1,4)

  16. We need to have formula to convert text to date using specified format - in the same manner as TEXT function works.

  17. I have a Problem.In Excel CellS5=IF(G5="","",">="&G5)this formula,when i enter a date in G5 as 01/04/20 the cell S5 shows the result as >=43922.What to do? Please help me.Thanks.

    1. Hello Nitin!
      Cell S5 formatting as a number. In cell S5 go to Format Cells, choose Number -> Date and set format you need.

      1. Thank you Alexander. Your advice helped me a lot! Have a great year!

  18. Hi,
    Good day!
    Could someone help me covert these,
    Example:
    1170217 to 17 Feb 2017,
    1180816 to 16 Aug 2018,
    930524 to 24 May 1993

    Thanks

    1. 30607061701471
      i need to covert this number to the below
      3
      060706 date of birth which will be from left 06 is the year (2006) 07 is the month (July) and 06 is the date
      and i don't need to change the rest of it

      1. I finally found it,

        =IF(LEFT(A2,1)+0=2,DATE(MID(A2,2,2),MID(A2,4,2),MID(A2,6,2)),DATE(MID(A2,2,2)+100,MID(A2,4,2),MID(A2,6,2)))

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

      =DATE(IF (LEN(A1)=7,"20" & MID(A1,LEN(A1)-5,2), MID(A1,LEN(A1)-5,2)), MID(A1, LEN(A1)-3,2), RIGHT(A1,2))

      Please go to Format Cells, choose Number -> Custom Format and set
      dd mmm yyyy
      I hope it’ll be helpful.

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

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

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

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

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

  24. Excellent article. Thank you.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  40. How do I convert

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

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

  42. Thank you!!!!

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

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

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

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

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

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

  49. All world data delete number 03415744310 03016084001 03028927904 03104492453

  50. Awesome Article. Thank You so much.

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