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

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

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

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

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

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

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

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

  8. 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, ".", "-"))

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  31. How to convert Mon, 30 Nov, 2015 to 30-Nov-2015 format

  32. I have the same question as above.

    date format example 01/01/2015 say in cell no a1
    i want to convert as follows 01012015 in cell no b1

    But when I tried what you recommended it didn't work.

    =TEXT(A1,"mmddyyyy")
    =TEXT(A1,"ddmmyyyy")

    Is there another way? Thank you so much for the help!

    1. Hi Tiffany,

      I've just tried the formula on my sheet and it has worked fine. How exactly doesn't it work in your Excel? Does it return a wrong result or error? If the latter, what error?

  33. Hi Svetlana,

    I hope you can help me. I have 9/1/2015 12:11:53 PM (cell A2) which i need to convert into text. I have used some of the idea above which is =text(A2,"dd/mm/yyyy hh;mm;ss"), some cell works but some cell doesn't.
    Do you know is there any other ways to convert this? Thanks in advance.

    1. Hi Yennie,

      In the time part of your format, try replacing semicolons with colons, like this:

      =TEXT(A2,"dd/mm/yyyy hh:mm:ss")

  34. Ah - just saw your post; many thanks!

  35. Cracked it!

    =IF(ISBLANK(N2);"";(TEXT(N2;"mmm-yy")))

  36. Hi Svetlana,

    Thanks for such an informative post.

    Is there a way to construct a formula like the below to return nothing if the source cell is blank? Right now, it is returning Jan-OO for all empty cells.

    =TEXT(N2;"mmm-yy")

    Thanks,
    Donal

    1. Hi Donal,

      Sure. You can use the IF function to check for blank cells, like this:
      =IF(N2=""; ""; TEXT(N2;"mmm-yy"))

  37. Hi Svetlana,

    Wondering if you could help me. I have a date in cell AB5, in US english format (mm/dd/yyyy) and a formula =TEXT(AB5, "mmm/yy") in cell AX5. For some reason the formula returns the date in date format rather than my desired Mon/YY format- any ideas why this may be the case?

    Best regards

    Jon

  38. Hi i i have 14/10/2015 11:01
    14/10/2015 11:01
    14/10/2015 11:01
    14/10/2015 11:01
    14/10/2015 11:02
    14/10/2015 11:02
    14/10/2015 11:02
    14/10/2015 11:02
    14/10/2015 11:02
    14/10/2015 11:02
    14/10/2015 11:02
    14/10/2015 11:02

    i want to add am/pm to each one. the date is currently in text mode.

  39. Hi
    I want formula For text Date in Excel like that
    Date:20/10/2015
    "Twenty ,Ten, Two Thousand Fifteen"
    Thanks plz reply

  40. good evening Sir,
    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

  41. Is there a way to convert a birthday (01/15/2012) into number of months and days (45 months 1 day)? I need the number of months and days to keep a continuous count as of the current day.
    Thanks.

    1. Hi Patty,

      You can use the following formula, where B2 is the cell containing a birthday date:

      =DATEDIF(B2,TODAY(),"M") & " Months " & DATEDIF(B2,TODAY(),"MD") & " Days"

  42. Is there a way to sort the text dates in chronological order in a pivot once you've used the formula in the data?

    I'm doing a rolling year oct-2014 through sept-2015 (displayed as 15-Sep with text formula) but when pivoting this the dates show as alphabetized (ie 14-dec,14-oct,14-nov,15-apr,15-aug and so on)

  43. I WANT TO LEARN DATE CONVERT INTO WORDS SUCH AS 01/01/2005,ONE JANUARY TWO THOUSAND FIVE

  44. Hello, i just want to know the formula if i input "date" from cell1, and on cell2, output will be the year only. Like for example if i input date 9/29/2015 on cell1, the output for cell2 must be "A" for year 2015. Then if year 2016, the output will be B, and so on. Thanks.

  45. Good news... I figured it out. This is how I did it:
    For B5, function is =IF(OR($K$2="",$L$2=""),"",TEXT(EDATE($K$2,1),"mmm 'yy"))
    For C5, function is =IF(OR($K$2="",$L$2=""),"",TEXT(EDATE($K$2,2),"mmm 'yy"))

    and so on.

  46. Hi Svetlana,

    Do you know of a way to increment months from a date value input so it'll automatically show up in correct month/year order in a table?

    Example: I have 12 columns, A to L. In K2, I input 9/20/15 (as the Start Date).
    Then for A5 to L5, I retrieve from K2 and it should automatically increment month/year (as needed).
    A5 has formula of =IF(OR($K$2="",$L$2=""),"",TEXT($K$2,"mmm 'yy"))
    The output for A5 is Sept '15
    I want B5 to automatically become Oct '15, C5 to become Nov '15, D5 to become Dec '15, E5 to become Jan '16, and so on.
    Any suggestions on how to do this?
    Many thanks.

  47. Hi,

    Is there a formula to convert 18/09/2015 to "Weekday" or 6/09/2015 to "Weekend"?

    Thanks,

    1. Hi JP,

      You can use the following formula:
      =IF(WEEKDAY(A1, 2)<6, "Weekday", "Weekend")

      Where A1 is the cell with a date.

  48. sir,my birthday 06-04-1983 ko excel formula se convert word six april nineteen eghty three

    1. Sir,Can you help me in converting date into wordssuch as 05.04.1995 into Fifth, April, Nineteen Ninety five

  49. Hi Please please help

    I want to mark "P" if the time-value is <7:00:00 hrs

    1. Hello Azhar,

      Supposing your have a list of times in column A, you can use the following formula:
      =IF(A1<TIMEVALUE("7:00:00"), "P", "")

      1. 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
        Plese help me..

        1. Sunil,

          I don't know a formula that could do this. Most likely a macro is needed in this case.

  50. Hi!

    I tried so much to find a way that i could use the number of the week of the year and the day of the week and make it a number. For example if we are in the 34th week of the year and the day is Tuesday then the text should show 3402. If its the 24th week of the year and the day is Monday then the text should show 2301 . The first 2 digits are the week of the year, and the other 2 digits are the monday-tuesday etc. Could you pls help me?

    1. I did it :) see you

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