Comments on: How to convert date to text in Excel with TEXT function and without formulas

The article explains how to use the Excel TEXT function and a variety of other ways to convert dates to text in Excel. Continue reading →

Comments page 2. Total comments: 212

  1. When I use concatenate(A1,"mm-dd-yyyy") the result is 02-dd-yyyy, so how I can solve this problem

  2. Hi,
    Im trying to figure out how to auto calculate the amount I spent on a daily basis. I already have used =now() to auto enter the date and not change everytime I reopen my google sheet. now what I want is how to let excel give the daily sum of yesterday when I start entering stuff today. Id love to use this for a graphical interpretation of my daily expenses and incomes.

  3. Hi
    Is there a way to take all forms of dates and format them into one singular type .

    Ill try and explain , I have a number of people sending me sheets , some have their default 23.01.1961 some have 23/01/1961 and others 23.1.61 and so on . When I try to search i am not able to find what I am looking for apart from searching all the different ways ?? I am searching about 52,000 entries ! help please

  4. Thank you!!!
    fixed all my pivot tables using this

  5. The year 1950 will be converted as "Nineteen Fifty" In text. For this, can you specify what should I do?

  6. Hi!
    I tried to use the TEXT function with =TEXT(A1; "dd/mm/yyyy") but the result is "08/03/yyyy". I have tried in multiple date forms for A1, and the chosen format, but the year always shows up as yyyy. If I change to =TEXT(A1; "yyyy/mm/dd"), I get an error in value: "A value used in the formula is of the wrong data type". Do you know what could be happening?
    (Side note: If I use the Text to Columns wizard, it works just fine...)

      1. I have used 08/03/2015, 2015/03/08, March 8, 2015,...
        I always make sure the formatting is date.

        1. Hello!
          I write your values as dates. I was able to repeat your mistake only if the letters yyyy are not letters of the English alphabet, but are written in another language.

          1. Thank you that really helped!! I was assuming that it would be yyyy for any language, but in portuguese it is aaaa (of 'ano')! Thank you so much :)

            1. For multiple language you can use "eeee" will give a result as "yyyy" or "aaaa" depends on language version of the person who reads.

              The only complex is "eeee" could not be used as "ee" to see the las 2 digits from year, like "yy" or "aa" does. ("ee" results in "eeee")

              1. Thank you

  7. Hello,

    I want to convert yyyymmdd to text but when I do it shanges to 4something. The other issue is that when I double click its (for example) 5/7/2021 I have it as 20210507 and want to take away the dat formula and just keep the number as text and the same way. Please advice...

  8. Not able to convert 30-04-2021 to text

    1. Hello!
      I hope you have studied the recommendations in the tutorial above. It contains answers to your question.

      =TEXT(A2,"DD-MM-YYYY")

  9. need to get an output with"/2021"alongwith the typed value.
    eg: if i type 1234 and enter the same cell should show "1234/2021"

  10. Correction in the main article:
    ""dd-mmm-yy" - displays as 08-Mar-2015 to avoid any confusion : )"
    should read:
    "dd-mmm-yy" - displays as 08-Mar-15 to avoid any confusion : )"

    1. Hi David,

      You are absolutely right. Fixed, thank you!

  11. hi, i want to convert 37.75 to time 37:45 i did this using format cells but it's format include ss eg
    37:45:00, i want to convert this to 37:45( hh:mm )in text format , please help..

  12. how the date/ month / year ( 03.05.2019) convert into words - third may two thousand and nineteen

  13. How do I convert the number in a date formatted cell into the three letter month abbreviation?
    Ex: visually Jan, actual cell contents 1/12/2018, result when I convert to text 43112.

  14. Hello
    I want to use the TODAY() function within functions such as SUMIFS where the date is in column A. For example if TODAY() is in A5 and the excel sheet has numbers in column V and dates in column P. The formula =SUMIFS(V2:V90,P2:P90,"17/12/2020") gives the correct answer but when I try to use the TODAY() function such as =SUMIFS(V2:V90,P2:P90,"=TODAY()") or =SUMIFS(V2:V90,P2:P90,"=A5") all I get is zero. Today's date is 17/12/2020. How do I use the TODAY() function to get the right answer? I need it to be flexible so I can use TODAY()-1, TODAY()-2 etc. as well.
    Thank you for any help.

    1. Remove the "=" before "TODAY()" and it should work

  15. It worked for me, Thank you

    1. thank you , very useful

  16. 1) Apples- Fruits, $100 (existing typed in one cell)
    How to formula this into 3 dfferent cell to Cell A1,B1,C1

    2) under one cell with 12345
    How can i separate it into Cell A1,B1,C1,D1,E1
    Thanks

  17. how do I convert :

    01/07/2012 to 1st July, 2012

    02/07/2012 to 2nd July, 2012

    03/07/2012 to 3rd July, 2012

    04/07/2012 to 4th July, 2012

    Meaning for:

    01 it is 1st

    02 it is 2nd

    03 it is 3rd

    04 it is 4th

    1. Hello!
      To format date with “st”, “nd”, “rd”, “th” you can use the formula

      =CONCATENATE(DAY(D2)&LOOKUP(DAY(D2),{1,"st";2,"nd";3,"rd";4,"th";21,"st";22,"nd";23,"rd";24,"th";31,"st"})," ",TEXT(D2,"mmmm, yyyy"))

      Day(D2) – returns the day of the month in number
      LOOKUP(DAY(D2),{1,”st”;2,”nd”;3,”rd”;4,”th”;21,”st”;22,”nd”;23,”rd”;24,”th”;31,”st”}) – returns the corresponding suffix of the day
      I hope my advice will help you solve your task.

  18. How to convert "03/25/1997" into "March Twenty-Fifth, Nineteen Hundred Ninety-Seven"

  19. I want to convert the date to complete text like, 1-Jan-2021 to First January Two Thousand and Twenty One.
    Please can you guide in this.

  20. Please help to convert the below mentioned date
    DD-MM-YYYY
    into DD.MM format
    tnx

    1. Hello!
      I hope you have studied the recommendations in the above tutorial.
      Please try the following formula:

      =TEXT(B1,"dd.mm")

  21. Please help to convert the below mentioned date Sep 11 2020 11:52AM
    into DD-MM-YYYY HH:MM:SS format

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

      =IF(MID(A1,18,2)="PM",DATEVALUE(MID(A1,5,2) & "-"&LEFT(A1,3) & "-"&MID(A1,8,4))+TIMEVALUE(MID(A1,13,5))+0.5, DATEVALUE(MID(A1,5,2) & "-"&LEFT(A1,3)&"-"&MID(A1,8,4)) + TIMEVALUE(MID(A1,13,5)))

      Set a custom date and time format in the cell DD-MM-YYYY HH:MM:SS

  22. thanks for your online support it's very helpful.

    Thanks,
    Kumar.S

  23. I am creating a spreadsheet with document name, document type, and date columns. In the final column I am using the "concatenate" function to piece all this data together in order to create a file name (in other words, I'm creating a naming convention for storage of all the files listed on this spreadsheet.

    For sorting purposes, I want the year first, then the month, then the date. I can force the date fields to reflect this by using the custom format option. However, when I use the "text" function to bring that date over into the file name column, the date is still converting into Excel's serial number for that date. (2020.05.05 converts to 43956).

    How can I get the date to carry over properly?

    Here is my formula, with G17 being the date field carrying over into this column:
    =IF(D17=" ",(CONCATENATE(F17,"_",TEXT(G17,"yyyy-mm-dd"),"_",TEXT(H17,"00000000"),"_",I17)),(IF(D17"",(CONCATENATE(F17,"_",G17,"_",TEXT(H17,"00000000"),"_",I17,"_",D17)),(CONCATENATE(F17,"_",G17,"_",TEXT(H17,"00000000"),"_",I17)))))

  24. I would like to convert a text string that contains date format MMM-YY such as "Mar-19" and "Jun-18" to be formatted in MMM-YY format, but I do not want it to assume that the date is of the current year.

    Example:
    Current year is 2020, my cell contains text "Mar-19", but using =Text(A1,"MMM-YY") returns value "Mar-20".
    I would like this to return "Mar-19".

    My hope is to use this text in a subtraction formula to find how many months from this start date to current date.

    1. Hello Daniel!
      To convert text "Mar-19" to date you can use this formula

      =DATEVALUE("01-"&A2)

      I hope my advice will help you solve your task.

  25. Thank you !!! It helped me a lot <3

  26. Hi,
    Could you please share me the process to convert below text to date:
    07 May 2020 17:23:47:000

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

      =DATEVALUE(SUBSTITUTE(A5,TRIM(RIGHT(SUBSTITUTE(A5," ",REPT(" ",20)),20)),"")) + TIMEVALUE(LEFT(TRIM(RIGHT(SUBSTITUTE(A5," ",REPT(" ",20)),20)),8))

      you can try to use Custom Format. Please go to Format Cells, choose Number -> Custom Format and set the followig format:
      dd mmmm yyyy hh:mm:ss
      I hope my advice will help you solve your task.

  27. Hi,I have Mar20. Is it possible to convert it to 3/31/2020, which is the last date of the month?

    Thank you so much if anyone can help.

    1. Hello Jenny!
      Please try the following formula

      =EOMONTH(A1,0)

      Note! Don't forget to set the Date format for cell E1.
      Hope you’ll find this information helpful.

      1. It works! Thank you so much!

  28. Thanks alot for sharing.

  29. Hai mam,
    in a file some date are entered but file sent to one system to another some date should be shuffled (it have 2000 entries in a column) .
    ie.
    10-03-19
    10-03-19
    10-03-19
    04-10-19
    10-08-19
    04-10-19
    10-10-19
    10-08-19
    in this file "10" represent the month. i want this file in dd/mm/yy format. please support me.

  30. I WANT YYYYMM DATE NOT WORKING THE TEXT COMMAND 199707 20/07/1997 I WANT 199707 HOW I CAN DO

  31. Hi,
    i have the following problem.
    When exporting my contacts from Linkedin, I receive a .csv file. I open the file in Excel (2013). One column of this file is filled with dates that look like this:
    10/27/19, 6:13 AM
    This format is text as it sits on the left of the cell.
    Manually, i delete the comma and the time. In this example i come up with:
    10/27/19
    I can't convert this date with the function DATEVALUE. I have a VALUE ERROR.

    I tried to used the "Text to column" Wizard, no results.

    I think that my problem comes from the fact I have a date:
    in a US format
    AND
    in text format.
    Do you have any idea you to solve this?

    Interestingly, i have a like macro my PC, a datepicker that manages to convert my american text date to a UK date. But this means i would have to click all the dates in my column one by one, which is not convenient.

    Thx

    JG

  32. date show as "20191231".how can convert it as "YYYY/MM/DD"

  33. I am not able to change 10-MAY-89 to another format like yyyymmdd.
    The format of the cell is standard and whenever I try to modify it to any date format, nothing happens.

    Thanks for your help,
    Any

  34. i want the format SEP 19 in csv from 01-09-2019
    date to text format
    how to convert , pls tell me

  35. Hello
    I want to change dight into Text.
    Ex:7:30 to convert into 7hrs30min.
    Please let me as soon as possible.
    Its needed for me!

  36. Hi Svetlana Cheusheva,

    Thanks for the notes, it helped me lot.

  37. 3/14/2019 9:30 AM i want this as 14-03-2019 09:00:00
    3/31/2019 6:39 PM - 31-03-2019 18:39:00

  38. Hi Svetlana,

    If I need to convert from text to data the information below, what formula do I have to use?
    10/8/2018
    10/10/2018
    8/8/2019

    I have to mention that I have all this dates in the same column.

    Thank you in advance!
    Roxana

  39. Hi Svetlana,
    I have the following problem:
    I want to subtract the two rows value if both are same date i.e I want to calculate the employee working hours starting time - ending time on a same day ,The values are in diff rows, if only one stamp of employee is there then make it error
    thanks in Advance Please could you help

  40. HI. I have a spreadsheet where the date has been supplied in dd.mm.yy format - Using dots.
    I want to change this to just using the Month. I have tried using the formula =SUBSTITUTE(E2,".","/") but then I can't change than data to only show month?
    Please could you help

  41. Hi Svetlana,
    i have the following problem:
    I can not change the formatting of date that was downloaded form txt file(notepad). From this one 22-10-2018 23:01:01 d/m/yyyy hh:mm:ss to 10-22-2018 23:01:01 m/d/yyyy hh:mm:ss. I even copy that to different sheets but it doesn't work.
    Could you please help with that
    Regards,
    Seymur

  42. GREAT FORMULA !!

  43. A useful article with many good hints and tips but sadly did not address my specific issue.

    I have in Excel a cell '_p_end' which contains a date, say 31/05/2018, which is entered by a user. This is converted to text by =TEXT(_p_end,"dd/mm/yy") to be used in later reports.

    Sadly, some of our users do not use English as their default language. In Excel, for eg a German user, the date displays as 31.05.2018 but it is still a date and may be manipulated as such. However

    TEXT(_p_end,"dd/mm/yy") = #VALUE!

    "dd/mm/yy" is not a valid date format in German.

    How can we overcome this?

    Thank you

    Stuart

  44. Hi hello pls anyone help me in this issue i need convert dates format already in in excel from 15.04.2017 to 15-04-2017
    all the dates are entered with . (dots) only I'm unable to change the format kindly suggest any idea

    1. ssrm

      An interesting problem you have posed. I cannot be certain that all of the information needed to answer you is available in your question but potentially there are solutions.

      Firstly you need to know whether the dates like 15.04.2017 are true dates or text.
      Secondly, you need to consider whether the format (appearance) as 15.04.2017 is simply a result of the locale you are using and if so what the default date format is for that locale.

      I presume, because you ask about displaying them as 15-04-2017 that you have already tried to format the dates to display differently using the Format|Cells|Dates options and have found that they do not respond. This suggests that the dates are text not true dates. The locale that is used then becomes irrelevant. Text is literal (wysiwyg).

      So, if the dates are not true dates but simply text, David Carter provides a quite simple solution at https://www.accountingweb.co.uk/tech/excel/exporting-data-from-sage-line-50-into-excel-1-by-david-carter
      I shall not reproduce all that he says here. If when you try to access it you find that it has been taken down, post an email address here and I shall send a copy to you. In brief, it is designed for a column of dates, and uses the Excel Text to columns functionality, so there is an assumption that you have a column of 'dates' which Excel presently treats as text. The solution converts the text to real dates.

      Once you have true dates you can then choose to format the cells as dates in whatever format is appropriate. If the file is to be shared across multiple locales then you may rather than using the standard date formats available for particular locales, which can result in differences such as you see (15.04.2017 which may appear in other locales as 15/04/17, 04/15/17 or even 17/04/15) set up a custom date format.

      For the custom date format, sadly, I cannot help. If we get a good reply to my post below (73), we may then know how to set one up, but as far as I can see Excel does not respond helpfully across locales, so eg dd-mm-yyyy -> 'dd-00-yyyy' in a German locale, and dd-MM-yyyy -> 'dd-05-yyyy'. We have to use TT-MM-JJJJ, which is of little help if you are in a different locale.

      Finally, I have just noticed that you can select a locale for the date format using Format|Cells|Dates, which will allow you to apply a standard format to display 15-04-2017. English (India), possibly others, contains this format. Whether this works if the file is later transferred to a different locale, I am sorry I do not know the answer to that.

      I hope this helps you
      Stuart

  45. hi please help me!
    how to do this i have 3 column with date and i want it if there is a date display the date but if its blank display the other date in the column.

    thanks so much in advance!

    1. Hello,
      For me to understand the problem better, please send me a small sample workbook with your source data and the result you expect to get to support@ablebits.com. Please don't worry if you have confidential information there, we never disclose the data we get from our customers and delete it as soon as the problem is resolved.
      Please also don't forget to include the link to this comment into your email.
      I'll look into your task and try to help.

  46. I appreciate this, thanks. 1 question about the recommendations:

    dates converted to text strings in the default short date format set in your Windows Regional settings, which is "mm/dd/yyyy" in my case:

    This does all I need EXCEPT I want to change the short date format the text comes through in. I need it to be yyyy/mm/dd. Do I have to change my regional settings or is there another way to do that?

  47. in sell A1 - TEXT 18102017 please convert this as 18/10/2017 in cell B1

    PL PROVIDE SUITABLE FORMULAS FOR CONVERSION USING =TEXT() FUNCTION

    REGARDS

  48. Hi Svetlana,

    I see you are trying to help users with Excel TEXT function used to format time. Well, I've faced problem in international environment, where different users may have different localization of Excel, as well as different local setting of Windows. It cause funny thing, that date formating in Excel TEXT function vary by local setting, e.g. "YYYY-MM-DD" in whatever English, "RRRR-MM-DD" in Czech, "VVVV-KK-PP" in Finish, etc. Excel is not converting it based on local setting, so formula created in one local setting, doesn't work in others. I understand it as MS Excel bug, but it doesn't help me to solve it.
    Have you ever seen this and are you aware of any solution/workaround, which may work? I'd appreciate any tip here.
    My workaround now is this formula: =YEAR(source date cell)&"-"&MONTH(source date cell)&"-"&DAY(source date cell). I'm not fully happy with the solution, as weakpoint is, it may result in single digit month and/or single digit day, which I want to keep as double digit (like 2017-09-01, instead 2017-9-1). Maybe it can be solved by some IF and counting of characters, but it's going to be way too complex, comparing to =TEXT(source date cell; "YYYY-MM-DD").
    Any idea or tip here is very welcomed.

    Regards, Ales

  49. how to convert date of birth in text
    01/07/1965- First july nineteen sixty five in Excel sheet
    pl what formula i have to write.my mob.number is 9423818133

  50. How to convert date of birth in words. Please help in excel.

    thanks

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