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 4. Total comments: 212

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

  2. GREAT FORMULA !!

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

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

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

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

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

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

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

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

    thanks

  11. Hi,
    I am trying to return the number of days between the 2 dates
    AE (6/9/2017 17:30 and AD (2/7/2017 9:35)
    Formula: =TEXT(AE6-AD6,"dd,hh:mm:ss")
    result: 01,07:54:45

    How can I return the 3 digits?
    I tried this formula:
    =INT(AE8-AD8)&"Days" & TEXT(AE8-AD8,"H""hrs""m""mins""")
    but then this does not seem to be in an order if I am trying to filter quickly.
    Could you please advise on this?
    Thanks!

  12. Hi
    I am using "Text to Column" feature to convert date. however it is not converting the 29 February 2017.
    Grateful if you please guide.
    Regards,
    Taufiq

  13. Hello,

    Apologies if you have covered this above, I have tried a bunch of your instructions that look applicable but none seem to work for me.

    I would like to type the following into a cell for today's date for example: 2905, and then for it to change to this: 29/05/17. Or if it needs to have the same amount of digits, type this: 290517 to get this 29/05/17.
    If I want it in the same cell, can this be done by conditional formatting? If not, can the formula go in a cell away from the current columns (so I don't need to have two columns next to each other just for the conversion)?

    Your help is greatly appreciated. If you could spell it out step by step that would be great, I am a bit useless. i.e: "click in A2 where you have typed the date as 2905, then click in cell D2..." :)

    Thanks! Brooke

  14. how to convert date to text in excel.for examble 10/03/2000 is convert to ten-march-two thousand like this

    1. Not possible ,
      you can do this formula
      =TEXT(a2,"ddd mmmm yyyy") the output you would receive would be Tue October 2000

  15. How do I create a formula when a specific text is typed in a cell, the current time & date will appear in an assigned cell?:
    A2 = YES
    B2 = (display current time and date)
    A2 = NO
    B2 = (will be blank)
    Thanks!

    1. =IF(A1="yes",NOW()," ")
      change the format for b1 so that it shows only time
      ie by going to format cells selecting time and select the appropriate one you required

  16. Hi,
    Anybody help me to change this time format to words ?

    (55:56:14 , it shows 7 hours, 56 minutes and 14 Seconds)
    it should be 55 hours.... ...

    INT(D179)&" Days," & HOUR(N179)&" hours, " &MINUTE(N179)&" minutes and "&SECOND(N179)& " Seconds"

  17. Hello! I require conversion of date of birth (25/09/1998) to date of birth in words as "Twenty fifth September Nineteen Ninety Eight" in Excel. Please help me.

  18. I am trying to make the formula that in cell A1 has a date and other cell B1 specific date or text i.e "Not Selected" and result in other cell C1 should be the month and days while cell B1 can be counted the today date. I could not make that formula. Please help me. Thank you in advance.

  19. Hi Svetlana,

    I am trying to convert several weeks of date formatted eg;Wednesday, 1 March 2017 to straight text 'WED" for use in pivot table /graphs.

    Custom formatting using "ddd" still retains the "number" value, and using a notepad to convert will not work as I am using Macros.

    Thank you in advance.

  20. Dear Svetlana,

    When I write 2013 on my excel sheet I have the problem that excel sees it as 1905, I have tried to use =Year(2013), change format, use text to columns but doesn't seem to found a way to correct this, could you help me?
    Best regards

  21. How do you convert 2015-JAN-10 to read 01-10-15 in excel?

  22. Is there a way to have a cell pull date data from one cell (ex. May 15, 2017) to say 'Mid May' instead?

  23. How to convert 'January 18th 2017, 12:04 am' to '18/01/2017 12:04 AM' in Excel?

  24. Hi,

    Can you please help me to convert the date format 24/01/2017 in "Twenty-Forth January Two Thousand Seventeen"..??

    Please let me know.. its very urgent

  25. hi.
    how to change date of birth figure in to words in ms excel 2010. for Example 4/3/2002.4th May Two Thousand Two

  26. Hi Svetlana,

    I am trying to copy a either an entire worksheet or a range of data from one workbook to another. I can do this successfully manually, by selecting all the cells with data in them and pasting into a newly created worksheet (Date format is maintained). However when I try to do this via VBA any date with a day greater than 12 gets converted to text [US (m/dd/yyyy)/Australian (dd/mm/yyyy) format change]. I've tried formating the cells first to US format dates but this doesn't help. Any sugestions?

  27. Hi Svetlana.

    This blog is awesome. Thanks

    I have a column D in an Excel sheet that shows date exported from a system in the format;
    day/ month / yyyy as shown in examples below
    29/07/2010
    24/04/2010
    9/7/2010
    20/07/2010
    15/08/2010
    etc

    I need to convert these dobs in to a 4 digit PIN code DDMM
    so results from above will be:
    2907
    2404
    0907
    2007
    1508

    I've tried =TEXT(D2,"mmdd") but it keeps the slashes / and always retains the yyyy at the end
    I've tried =TEXT(D2,"dd")&TEXT(D2,"mm") but same issues

    A maths friend gave me this and it seemed to work for a while:
    =IF(LEN(TEXT(100*DAY(D2)+MONTH(D2),0))=3,CONCATENATE("0",TEXT(100*DAY(D2)+MONTH(D2),0)),TEXT(100*DAY(D2)+MONTH(D2),0))

    I'm using Excel 2010. Help please.

    Thank you in anticipation

    1. Hi Mark,

      This simple formula works just fine for me:
      =TEXT(D2, "ddmm")

      You can check it in a new empty sheet by typing a few dates manually in your default date format, not copy/paste from the existing sheet. If it works with the regular dates typed manually, then most likely the problem is with the exported dates, e.g. they may be text strings that look like dates. To check this, select any cell with a date, and look at the Number Format box on the Home tab > Number group. If it shows Text or General, you are dealing with text strings, not dates. In this case, you can simply extract the 1st, 2nd, 4th, and 5th character using this formula:
      =LEFT(D2,2)&MID(D2,4,2)

      Please note, the above formula will work only if all the dates are in the same format, i.e. dd/mm/yyyy.

      If there are dates formatted as d/m/yyyy (without leading zeros), you will need to convert text strings to dates first, and then apply =TEXT(D2, "ddmm") to the converted dates.

      1. OK - thanks for confirmation & the extra info

  28. that was very simple way of doing.
    Is there no other option, using Text and columns??

    1. Sure, you can use the Text to Columns feature too as demonstrated in this example:
      Covert text to date with Text to Columns. I prefer 'Replace All' because it's faster, but it's up to you.

      Another way to convert a date in mm.dd.yyyy (or dd.mm.yyyy) format to mm-dd-yyyy (or dd-mm-yyyy) is using the Substitute function:
      =VALUE(SUBSTITUTE(A1, ".", "-"))

  29. Hi,
    I would like to learn, how to convert 01.01.2016 to 01-01-2016 format.

    1. =SUBSTITUTE(A1,".","-")

  30. Hi all,
    I would like to convert date, i.e. 31-12-2015 into text format... like Thirty one - December - Two thousand fifteen.

  31. I would like to enter a ratio like "1:1" or "7:5" in a cell that has been previously formatted as Text. Excel 2013 constantly changes my entries to date/time formats. How hard is it for the geniuses in Redmond to understand that when I format a cell as Text I want whatever I enter in it to be displayed EXACTLY AS I HAVE ENTERED IT and NOT AS ANYTHING ELSE???????

  32. How to use d(), m() and y() functions in excel

  33. I have cells formated as text.now i wantto genrate todaydate but in text formate these cells does not read today date.plz help

  34. i want to know how to convert date into words(english) in excel 2003 or 2007
    for eg. 22.04.2010 - convert it into Twenty Two April 2010 or Twenty Two April Two Thousand Ten.

  35. is it possible to covert a numeric date into complete Text format? e.g 13/02/2015 in thirteenth February Two thousand and fifteen.

  36. Hi my question is when I am use

    =E1&" "&" "&E2&" "&TEXT(E3, "MMM YY ") formula.

    I want to change the year from 2016 to 2015 but when copy that formula and past it into the next cell the year stay the same even when I put it as paste fill without formatting.

  37. how to convert date for example 14/07/2008 in word " Fourteenth Jun Tow Thousand"

  38. i want to know how to convert date into words(english) in excel 2003 or 2007
    for eg. 22.04.2010 - convert it into Twenty Two April 2010 or Twenty Two April Two Thousand Ten.

  39. 27/5/2016
    21/4/2016
    29/5/2016
    15/5/2016

    not able to change this date format to mm/dd/yy

    1. Hello Priti,

      Select the cells, pres Ctrl+1 to open the Format Cells dialog, select Date under Category and choose the desired format. Or, select Custom under Category, and type mm/dd/yy in the Type box.

  40. 15/06/2006i want to result fifteenth june two thoushand six please help me

  41. how to convert to date month year words in excel

  42. Hi Mam! Could you help me convert "Sat, May 14, 2016" into "14/5/2016" format and separate "Sat" from the string when the former started with asterisk mark. Thanks in advance.

  43. This article is really helpful, thank u so much Svetlanta...

  44. Hi Svetlana
    how to convert date of birth in text
    02/02/1966- Second February nineteen sixty six in Excel sheet
    pl what formula i have to write
    my mob.no is 7773876579

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

  46. Hi Svetlana,

    When I use the Text function within a formula, it is still displaying a number. Any reason why it is doing it? Note that when I try the function in a standalone cell, its working perfectly fine.

    Here's what I mean-

    ="select ric_code,exchange_code,expiry_date from smd_live_raw..t_ds_optfuture where expiry_date =TEXT(42587,"dd-mmm-yyyy")"

    The number 42587 is being taken from a cell that contains a date.

    Any help please? Thanks.

    -Kishore

    1. Please change general function after use this formula TEXT(42587,"DD MMM YY")
      ans : 05 Aug 16

  47. how do u create if formula for
    if 06:00:00 to 9:00:00 should be P
    after 11:00:00 AM should be HAL
    Before 2 PM should be HAL again

  48. Hi there,

    Please help me - I have this birth date that I want to change to text ie 19720101 should read 01 Jan 1972, how do I do that?

    Thank you so much!
    Thersia

    1. Hello THERSIA,

      From your example, it's not quite clear which of the two "01" in 19720101 is month and which is day. Assuming it is the "yyyymmdd" format, you can use one of the following formulas (where A1 contains 19720101):

      To convert the number to a date:

      =DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2))

      As the result, the formula returns a serial number representing the date. Now, select the formula cell, press Ctrl+1 and set the following date format: "dd mmm yyyy". Please see How to create a custom date format in Excel for the detailed steps.

      To convert the number to a text string:

      =TEXT((DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2))), "dd mmm yyyy")

  49. When I paste the column to notepad - it does not change it to text. When I paste it back in excel - it is still a date. I am using office 365

  50. Hi
    I used some software for entry so I export my entries and worked for invoice date + 30 = schedule date
    In our report 1 to 12 date its calculated but 13 to 31 dates calculation its not able and the answer its #value
    Please help me how could solve this error

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