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

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

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

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

  4. It worked for me, Thank you

    1. thank you , very useful

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  28. Hi:

    Thanks to you awesome tutorial I was able to convert date to text, but know I want to convert that text value into something measurable MINUTES.

    For example:
    My (A2) cell value is 1d:03h:40m  1day + 3hours + 40 minutes  (1*24*60)+(3*60)+40 =1660

    Can you please advise how can I perform or execute this conversion from TEXT(Date) to NUMBER (Minutes)?

    Any questions feel free to let me know.
    Thanks in advance.

    1. Hi!

      1d:03h:40m is a very specific format, so the formula is also highly specific:

      =VALUE(LEFT(A2,FIND("d",A2)-1))*24*70+VALUE(MID(A2,FIND("h",A2)-2,2))*70+VALUE(MID(A2,LEN(A2)-2,2))

      Please note that it will work correctly only if hours and minutes always contain 2 digits, with leading zeros if needed.

  29. Hi;

    I want to convert number of days to y m d.
    Eg; 1092 days and have Excel convert that to 2years 11months 6days

    Thankfull for quick replay.

    1. Hi!

      Because there are leap and non-leap years, and the number of days in each month varies from 28 to 31, we can only suggest the formula with a certain degree of approximation. I.e. 365 days are counted as a year, and 365/12 is counted as a month:

      =CONCATENATE(IF(INT(A1/365)>0,INT(A1/365)&" years",""),IF(INT(MOD(A1,365)/(365/12))>0," "&INT(MOD(A1,365)/(365/12))&" months", "")," ",IF((A1-INT(A1/365)*365-INT(MOD(A1,365)/(365/12))*(365/12))>0,INT((A1-INT(A1/365)*365-INT(MOD(A1,365)/(365/12))*(365/12)))&" days",""))

      Where A1 is the cell containing the number of days you want to convert.

      For 1092 days, the formula returns 2 years 11 months 27 days.

      1. i want some more i dont have days in there i have only Date of Joing a one person then i want everything do in a one cell.

        1. Syed,

          If you want to display the difference between 2 dates, say between the Date of Joining (A1) and today's date, then you can use the DATEDIF function to calculate the date difference, and the formula is much simpler:

          =DATEDIF(A1, TODAY(), "y") &" years, "&DATEDIF(A1, TODAY(), "ym") &" months, " &DATEDIF(A1, TODAY(), "md") &" days"

      2. Thanks so much it working :)

  30. Please help me to convert date format to text format as like this

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

    1. Hi HABIBUL,

      You can enter one of the following formulas in B1, depending on which time unit you want to come first - month or day:

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

      1. Dear Svetlana, I am using Excel 2010 v 14.0.473
        When using the formula =text(A1,"ddd") I get "The formula you typed contains an error". A1 = 2016/11/17

        Please help?

        1. Hello Riaan,

          It may happen if a value in A1 is a text string, not a date. To check this, select A1 and look at the Number Format box on the Home tab in the Number group. If it displays General or Text, you are dealing with a text string that looks like a date. In this case, you will need to convert text to date first.

          1. Dear Svetlana, nope, the format is Date. Even if I point the formula to an empty cell, I get the same error. This does not seem right.

            Even when I use Datevalue and point to my date field that is either formatted as text or date, I get #VALUE!

            All other Excel formulas work perfectly.

            Unfortunately I cannot post screen shots here of my spread sheets to prove my points to you.

            Any suggestions?

  31. Hai,

    Please help me to solve the following issue.
    here I have certain shifts & their start time and end time.
    how to get the hourly count for each shift for one day

    for example:

    Shift Start End 1st Hr - 2nd Hr - 3rd Hr - 4th Hr
    Shift 1 00:30 12:30 32 32 32 32
    Shift 2 01:30 13:30 32 48 48 48
    Shift 1 02:30 14:30 32 48 56 56

    1. Hi Wroxwiley,

      Your table was mangled during posting and I am not sure I can follow you, sorry for this. If you can post the same question on our forum and attach a sample worksheet for better understanding, our support team will do their best to figure it out.

Post a comment



Thanks for your comment! Please note that all comments are pre-moderated, and off-topic ones may be deleted.
For faster help, please keep your question clear and concise. While we can't guarantee a reply to every question, we'll do our best to respond :)