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 9. Total comments: 737

  1. I cannot do calculation with "9/25/2019 12:00:00 AM". It seems it is text. How can I convert it to "Sep 25 2019"? I want to do a subtraction with another cell in that format to get the days in between.

    1. Hello!
      If your date is written as text, you can convert it to a real date using the formula

      =DATE(MID(E1,FIND("~",SUBSTITUTE(E1,"/","~",2),1)+1,FIND(" ",E1,1) - FIND("~",SUBSTITUTE(E1,"/","~",2),1)-1), LEFT(E1, SEARCH("/",E1,1)-1), MID(E1,FIND("~",SUBSTITUTE(E1,"/","~",1),1)+1, FIND("~",SUBSTITUTE(E1,"/","~",2),1) - FIND("~",SUBSTITUTE(E1,"/","~",1),1)-1))

      Then set the date format you want in the cell.

  2. How do I covert this number to a date - Month/Day/Year
    I tried this formula and it does not work.
    =DATE(RIGHT(A2,4), MID(A2,3,2), LEFT(A21,2)) 1/6/2020
    Posting Date Transaction Date
    06012020 05292020
    06012020 05282020
    06012020 05292020

    1. Hello!
      The formula

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

      or

      = MID(A2,3,2)&"/"&LEFT(A2,2)&"/"& RIGHT(A2,4)

      works.

  3. I have imported data from our school information system. Birthdays are showing as 8/20/2002. I need the format to be a text before I can upload the file to another system. The format has to be in mmddyyyy, which I have done so my cell now shows 08202002, but I need it to be recognized as text, not a date before I upload it. If I try to convert it to text, it shows the number 37498, which I understand is the number of days since Jan. 1 1900. How do I get the cell to recognize 08202002 as text and no longer as a date? If I upload it as it is showing, I receive an error stating my date is in the wrong format, because it is still showing as a date not a number.
    Thank you.

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

      =TEXT(F10,"mmddyyyy")

      How to convert date to text read more here.

  4. Hi Alexander
    Please help to convert
    8/19/2020 7:37:23 PM in General to 19-08-2020 19:37

    1. Hello!
      If your data is written as text, use the DATEVALUE function. Please read the above article carefully.

      1. HI ,
        I HAVE TRIED DATEVALUE BUT NOT NOT WORKED.Please help to take a difference for two entries as they are text strings.Thanks
        8/14/2020 7:48:53 AM 8/17/2020 12:29:35 PM

        1. Hello!
          What is the date format on your computer - dd/mm/yyyy or mm/dd/yyyy? If the format is mm/dd/yyyy, then your text should be automatically converted to date.

  5. Thank you so much for this! This is very helpful!

  6. Hello
    How do i please convert this "40464% order date in cell C2 into a normal date format

  7. Hi. Previously, if I type "8-10" it would be treated as mmdd resulting to "Aug-10" or depending on default format. Now, excel treats "8-10" as ddmm and shows October-08. Is there a way to change the default syntax of typing dates without having to change the date format every new excel worksheet?

  8. Hello,

    How to convert 2020-04-17T10:58:58Z to DD/MM/YYYY
    Thank You

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

      =DATE(--LEFT(D3,4),--MID(D3,6,2),--MID(D3,9,2))

      The formula returns the date. You can use any date format you need.

  9. Hi!
    I have a question, I wanted to get a continuous string of yyyymmm, but if I do it I get something like 201913. For example, I started off with date 201906, and I wanted that to keep going into 202006 without me having to change the 201913, 201914, 201915 etc. Is there a way to do this? For my formula I just do the cell+ 1, which is why i get the 201913.

    Thank you!

    1. Hello!
      If you wanted the string "yyyymmm" then 201913 is not possible. After all, month 13 does not exist! So what numbers do you want? In addition, if you add 1 to the date, it means +1 day, not +1 month.

  10. Hello, I want to put 3 reminder dates at least in one cell, and then used those dates to determine the last activity date of all dates in my sheet, meaning there would be different cells selected which will have one single date. Formating for reminder dates would be (01/06/2020 (alt+enter) 06/06/2020 (alt+enter) 12/06/2020)

    1. Hello!
      In this case, your dates will be written as text. Explain what you want to do with these cells? Give an example of the desired result. It’ll help me understand it better and find a solution for you. Thank you.

  11. Hi Alexander,

    I need to convert YYYYMMDD to MMDDYY for all the cells is it possible. Please help

    1. Hello Shruthi!
      I’m sorry but your task is not entirely clear to me. Could you please describe it in more detail? Is your data recorded as a date or as text? Do you want to get the result as a date or as text? Write an example of the source data and the result you want to get.

  12. Hi,
    I need to convert dates in the following format - MMYY - from TEXT to DATE format. The dates are entered as follows:
    114 for 01/1/2014
    1015 for 10/1/2015
    616 for 06/1/2016
    The day is always understood to be 1 so it is never entered. The year is always understood to start with 20 so it is never entered. Zeros are never entered in front of single-digit months.
    Do you have a formula to convert?
    Any help would be appreciated!

    1. Hello Sandra!
      To convert text to date, use the LEFT and RIGHT functions

      =DATE("20"&RIGHT(A1,2),LEFT(A1,LEN(A1)-2),1)

      I hope it’ll be helpful.

  13. I have two columns with date and time stamp as strings ex. June-23-2020 12:38:42 PM.
    First I want to convert these to normal date and time format so that I can find time different between two columns. Please suggest.

    1. Hello!
      To select a date from your text, use the formula

      =DATEVALUE(MID(Z1,FIND(" ",Z1,1)-4,4)&"-" &MATCH(LEFT(Z1,3),{"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"}) &"-"&MID(Z1,FIND(" ",Z1,1)-7,2))
      To select time from the text, use the formula

      =TIMEVALUE(RIGHT(Z1,LEN(Z1)-FIND(" ",Z1,1)))

      Hope this is what you need.

  14. I took out an output from some tool which gave me the numeric value of the datetime -
    June 25, 2020 16:06:37 as 1593065197208, when i exported it in csv.
    i am not sure what 1593065197208 is and how to convert it back to

    1. ** correction **
      convert it to June 25, 2020 16:06:37 in excel. Can you please explain what that number is and how to convert it.
      Thanks in Advance.

  15. Glad to see that you have replied to almost all queries. I have tried to follow many options that have been provided but unable to get the text into date format. I have this number in text format that in reality is last-logged in date & time stamp in source system. When retrieved it comes as text as 132374551396990975
    How can i format it into date or date & time stamp.

  16. Hello,
    I'm trying to convert text to dates from text that has a variable number of digits i.e.

    28042020 = 28/04/2020
    29042020 = 29/04/2020
    4052020 = 4/05/2020
    5052020 = 5/05/2020

    Is there a formula that will recognise that there is a digit missing (the "0")?

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

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

      Hope this is what you need.

  17. Hi, Can you help me turn an identification into a date? I have a person's initial followed by their DOB, and want a column that reads just the DOB. For example, AB010107 to 1/1/07.

    1. Hello Mel!
      Extract numbers from text. Then convert these numbers to a date.

      =DATEVALUE(CONCATENATE(LEFT(CONCAT(IF(ISNUMBER(--MID(A1,ROW($1:$93),1)), MID(A1,ROW($1:$93),1),"")),2),"/", MID(CONCAT(IF(ISNUMBER(--MID(A1,ROW($1:$93),1)), MID(A1,ROW($1:$93),1),"")),3,2),"/", RIGHT(CONCAT(IF(ISNUMBER(--MID(A1,ROW($1:$93),1)), MID(A1,ROW($1:$93),1),"")),2)))

      Remember to apply the date format to the cell with the formula.

    2. Also the date format I want is month/day/year.

      1. Too bad you didn't say that before

        =DATEVALUE(CONCATENATE(MID(CONCAT(IF(ISNUMBER(--MID(A1,ROW($1:$93),1)), MID(A1,ROW($1:$93),1),"")),3,2),"/", LEFT(CONCAT(IF(ISNUMBER(--MID(A1,ROW($1:$93),1)), MID(A1,ROW($1:$93),1),"")),2),"/", RIGHT(CONCAT(IF(ISNUMBER(--MID(A1,ROW($1:$93),1)), MID(A1,ROW($1:$93),1),"")),2)))

  18. Hi! How can I convert 20th Mar 2020 into 20/03/2020 format? I've tried all the above suggestion but it still not working. Thanks!

      1. It doesn't work out. Please note that it comes in from an export file as a general format in a .csv excel sheet. Thank you!

        1. Hello!
          The formula I sent to you was created based on the description you provided in your first request. However, as far as I can see from your second comment, your task is now different from the original one. Hence, the formula fails to work. First convert your .csv file to Excel.

  19. Good morning,
    Could you please advise how could I convert data "202001" to JAN-20?
    Thank you in advance!

    1. Hello!
      If you want to convert data to a date, use the formula

      =DATEVALUE(RIGHT(A20,2)&"/1/"&LEFT(A20,4))

      and then apply a custom format

      Mm-yy

      To convert data to text as a date, use the formula

      =VLOOKUP(--RIGHT(A20,2), {1,"JAN";2,"FEB";3,"MAR";4,"APR";5,"MAY";6,"JUN";7,"JUL";8,"AUG";9,"SEP";10,"OCT";11,"NOV";12,"DEC"}, 2,0)&"-"&MID(A20,3,2)

      Hope this is what you need.

  20. I have used the DATEVALUE function in the past and it works fie for me when the date string is in d/m/yyyy format. Now I received files files with the date in m/d/yyyy format and the function is returning an error. I'm guessing that DATEVALUE expects the string date format to be per Windows region settings, or some setting in Excel. Is there a way to "tell" DATEVALUE what the date string format is?
    I am using Excel 2007 under Win 10-64.

    1. Hello!
      You need to change the date string according to the regional format of Windows. Use something like this for the DATEVALUE function

      =DATEVALUE(MID(B11,SEARCH("/",B11,1)+1,SEARCH("/",B11,SEARCH("/",B11,1)+1)-SEARCH("/",B11,1)-1) & "/"&LEFT(B11,SEARCH("/",B11,1)-1) & "/"&RIGHT(B11,4))

      Instead of the format m/d/yyyy you get d/mm/yyyy

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

  21. You might want to mention in bold somewhere that Excel is heavily dependent on Regional settings in our Windows PC. Two people might see completely different results based on regional settings. One of my colleagues in another part of the world was seeing #VALUE! using my Excel sheet which was displaying fine for me. The culprit - Date format in Windows region settings!! Took hours to figure out.

  22. how to convert date to text

  23. How do I convert Date A's format to Date B's format?

    Date A: 9/25/2019 21:52
    Date B: 01-07-2020 3:59:00 AM

    1. Hello Adam!
      I think this article will help in solving the problem of how to change the format of the date and time.
      I hope this will help, otherwise please do not hesitate to contact me anytime.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  47. Excellent article. Thank you.

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

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

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

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