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

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

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

  2. Hi,

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

  3. Hi,

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

  4. Hey,
    Could someone help me covert following:

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

    Many thanks in advance,

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

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

      I hope my advice will help you solve your task.

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

  6. Hai Alex,

    Can you help me?

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

    Any idea how to do that automatically?

    Thank you Alex.

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

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

      I hope my advice will help you solve your task.

  7. Thanks a lot.

  8. Hello Alexander,

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

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

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

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

    I hope I made myself clear.
    Thanks again!

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

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

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

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

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

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

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

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

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

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

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

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

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

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

      1. It means Month-Year

  11. In A2 column I have 200 hundreds of dates as a text string, like, how to get all these into MMDDYYYY format? Here I only know with an open eye which one is a year, and the month and days are confusing.

    1949/5/2
    2-3-2002
    09/11/1988
    11/12/1987
    03/02/1999
    02-08-91

    1. Hello!
      All of your dates are spelled differently. Therefore, you need to write a formula for each of them. Use the guidelines in this article, as well as the DATE, LEFT, MID, RIGHT functions.
      You will find many ways to convert text to date in the comments to this article.

  12. Hello,
    I have a transaction date and time as text, 2020092120371600. I need it to display as date and time, including the seconds, in a single cell. Ideally the above would display as 09/21/20 21:37 16:00, where the 16:00 represent seconds. Can this be done such that the column of transactions date/times can be sortable? Thanks in advance for assistance with this.

    1. Hello!
      To convert your text to date and time, use the formula

      =DATE(LEFT(A1,4),MID(A1,5,2),MID(A1,7,2))+TIME(MID(A1,9,2),MID(A1,11,2),MID(A1,13,2))

      Use a custom date and time format too
      mm/d/yyyy hh:mm SS:"00"

      I hope my advice will help you solve your task.

      1. This worked perfectly! Thank you!
        In certain cases, the last 2 digits in my time stamp are not "00". Maybe this is called hundredths of a second? Can Excel also handle this via formatting? Even though the value is very, very small, it does make a difference in my sort-order. An example would be; 2020091712225895

        1. Hello!
          Change the formula to show milliseconds

          =DATE(LEFT(D1,4),MID(D1,5,2),MID(D1,7,2)) + TIME(MID(D1,9,2),MID(D1,11,2), MID(D1,13,2)) + (1/24)/60/60/1000*RIGHT(D1,2)

          Milliseconds are usually 3 digits. If you only use 2 digits then

          =DATE(LEFT(D1,4),MID(D1,5,2),MID(D1,7,2)) + TIME(MID(D1,9,2),MID(D1,11,2), MID(D1,13,2)) + (1/24)/60/60/1000*RIGHT(D1,2)*10

          Also use another custom date and time format
          mm/d/yyyy hh:mm ss.000

          1. Every last decimal and digit looks great? I really am thankful for your time to help with this. I know how you did this, but I struggle writing these. It's like magic when I double-click the cross that populates the column on down. Cheers!

            1. i am very thankful to you if you convert text
              Jan 15, 2021 15:33:25
              in to 15,1,2021 and 15:33:25

  13. I solved it myself! :3
    I hope is useful for someone else.

    Sep 7 2020 5:14PM
    Sep 11 2020 1:12AM
    Sep 18 2020 10:18PM
    Mar 3 2020 10:34PM

    first column
    =date(MID(A2,8,4),MONTH(1&LEFT(A2,3)),MID(A2,5,2))
    second column
    =TIMEVALUE(MID(A2,13,5)&" "&RIGHT(A2,2))

    Regards.

  14. Need to convert the following format into two columns. So it will look like this:
    Sep 7 2020 5:14PM 09/07/2020 5:14 PM
    Sep 11 2020 1:12AM 09/11/2020 1:12 AM
    Mar 3 2020 10:00PM 03/03/2020 10:00 PM

    Thank you for your support.

    1. Hello!
      If I understood correctly, you want to split the text into 2 parts. Use "M" as separator.
      Please use the following formula:

      =LEFT(A1,FIND("M ",A1,1))

      =RIGHT(A1,LEN(A1)-FIND("M ",A1,1)-1)

      1. Hello Alexander!

        I'm sorry but that's not what I want to do. On COLUMN A it will show the text "Mar 3 2020 10:34PM"

        What I want is that in COLUMN B will show the date in number format>>> 03/03/2020
        And in COLUMN C it will show the time>>> 10:34 PM

        I tried the formulas that you provided and they gave me #VALUE! error.

        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.

        2. The CSV text comes like this:
          Sep 7 2020 5:14PM
          Sep 11 2020 1:12AM
          Sep 18 2020 10:18PM
          Mar 3 2020 10:34PM

          I want to make it: mm/dd/yyyy
          And on another column: h:mm AM/PM

    2. To make it a little bit more clear. Type A to Type B:

      Type A
      Mar 3 2020 10:00PM (:00PM is not a typo, that's how the system gives me the value)
      TYPE B
      COLUMN 1----COLUMN2
      03/03/2020---- 10:00 PM

  15. Dear sirs, can I ask for a wee bit of your expertise since my own is not enough in this case?

    I am looking to do comparisons between quarters during the year but with the closing balance date fixed from previous year. Let me explain:
    E.g. QUARTER 4(b)=2020-12-31, QUARTER 3(c)=2020-09-30, Closing balance (QUARTER 4(a))=2019-12-31.
    I have created formulas for Q4(b) and Q3(c) (linked to another date cell) respecively so that when Q4(b)=>Q1(b), Q3(c)=>Q4(c). But, i want the Closing balance date Q4(a) (ie. 2019-12-31) to remain the same during the year UNTILL the new accounting year starts. So when Q1(b)=2021-03-31, Q4(c)=2020-12-31 and Q4(a)=2020-12-31.
    In words, Q(b) and Q(c) change during the year where Q(a) remain the same with previous year's closing date, and it's only when the Q(b)/Q(c) year change as Q(a) changes closing date one year forward.
    I find the Q(a) value quite tricky to solve.

    Thank you and regards,
    Daniel

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  37. how to convert date to text

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

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

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

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

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

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

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

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

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

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

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

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

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

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