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 13. Total comments: 731

  1. I have a downloaded spreadsheet that lists the dates as dd-mm-yyyy and dd/mm/yyyy in the same spreadshet. I am able to change the format for dd/mm/yyyy but for the dd-mm-yyyy I cannot format. The dd-mm-yyyy dates are "General" and
    dd/mm/yyyy are "dates". Does anyone have an idea on how to fix the "General" ones so that I can format them to dates? I really need help with this. I fix it manually but it is so time consuming because each month has over 100 transactions.

  2. This is the BEST website I've ever visited for Excel help. What incredibly clear and worthwhile information.

    Thank you! Changed my world.

  3. Hi,

    I have some dates that I can't seem to find any formula for. My data exports strange, I don't know why it does it, but my dates populate like this:

    1022012 (supposed to be 1/02/2012)

    and the months with two digits populate like

    12232012 (12/23/2012)

    I don't know what formula I can use to formulate this as a normal date. My file is nearly 600K lines and there's no way I can manually change the date for them. Please help!!

  4. How to convert Thu 11:47 PM to date format. Thu 11:47 PM is the email received date.

  5. Hi I have Excel text column having value as 'Wed Feb 07 13:39:40 PST 2018' I want to convert to another column with date/time type with value as 2/7/2018 1:39:40 PM , how to do it?

  6. Hey Guys,

    Great job on this thread! How do I convert Jan/31/18 08:42 PM to 01/31/2018 ?

  7. Hi,

    Please help. I want below text fields in format dd-mon-yyyy

    09011958 like 01-SEP-1958
    10201995
    03301975
    08141975
    10241974

    OUTPUT IS ###### IF I USE ANY OF THE ABOVE METHODS. wHERE AM I GOING WRONG?

  8. hi,

    i want to convert 'Aug 8 2018 4:02AM" in date format dd-mm-yyyy hh:mm

    can any one help

  9. Hi,

    How do I split the Month Day and Year without using Text to columns? or at least convert the date to just the day. Example 08/08/2018 to just 08 (day)?

  10. Hi,
    Any help in trying to convert 01/01/2018 into January 1st 2018 please?
    Many thanks

  11. How can I conert a text date like “Thu 5 Jul 2017 11:59 AM” to date ?
    Thanks

    1. Doina:
      You might be able to right click the cell that holds the data and choose Format Cells then Date then the format you want.
      If you need to split that time away from the date, I would use the Text-to-Columns tool.
      Highlight the cell then under Data select the Text-to-Columns tool. In that window choose the Fixed Width button then OK. Next click on the lines you don't want to use to split the data then OK. Then highlight the column that holds the date select the Date dropdown and choose the DMY option then OK.
      Now you can right click the cell with the date and choose Format Cells and choose the Date option and select the date format you want.

  12. Moin:
    Dates and Excel can be a real pain.
    I took your sample "20110328" put it into cell A22 and entered this formula in B22 and formatted it Custom dd/mm/yyyy. Let me know if it works for you.
    =RIGHT(A22,2)&"/"&MID(A22,5,2)&"/"&LEFT(A22,4)

    1. THANK YOU!~! :D

  13. Hi, I'm trying to convert excel date format from 20110328 to 28/03/2011, I used this formula "=DATE(LEFT(B4؛4)؛+MID(B4؛5؛2)؛+RIGHT(B4؛2))" and it was useful. on the other hand I had a lot of dates that it's about daily return and I get these returns to choose the monthly return and their dates. so, I
    used this formula "=RIGHT(E8;5)" as select the last return of a month but it showed number like "40631" .How do i change to get "/mm/yyyy"?
    thanks
    moin

    1. Moin:
      Have you checked the formatting for the cell that holds the 40631? The format should be the mm/yyyy and it looks as if it's General or Number.

      1. I changed the format in customs by mm/yyyy but no change takes place in result!?
        another question, somewhere I haired that when you change the 20110328 to 28/03/2011, you could do this changes with this format "yyyy/\mm/\dd".
        I do this work but it doesn't change.Do you know the correct form of last format???

  14. Anyone help on how to convert a date from a conventional format like 12-03-2018 to look like 12032018?

    Thank you

  15. Thanks so much!!!

  16. How can I covert
    a) 0820 (mmyy) into 31/8/2020
    b) 0216 (mmyy) into 29/2/2016?

    Note: the dd is always the last date of the month.

    Anyone can help?

  17. How do I convert a fractional year to a month, day, year format? i.e. 2016.72

    1. Gary:
      I think this will do it. Where the fractional date is in A11, enter this in an empty cell and format the cell in the format you need.
      =DATE(INT(A11),1,MOD(A11,1)*(DATE(INT(A11)+1,1,1)-DATE(INT(A11),1,1)))

  18. how to convert '08/01/2018 to 08/01/2018...

    1. Suresh:
      You can easily change this text into a date by removing the "'" in front of the date. Excel will then recognize it as a date and you can then modify the display in Format Cells.
      If you need the ellipse in the date remove the "'" go to Format Cells and in the Custom Option in the field enter "@*."

  19. How can i easily convert the following text strings into dates that I can use in formula's

    Friday 30 June 2017
    Thursday 29 June 2017
    Wednesday 28 June 2017
    Tuesday 27 June 2017
    Monday 26 June 2017
    Sunday 25 June 2017
    Saturday 24 June 2017
    Friday 23 June 2017

    I have checked they are text and not dates.

    1. Hello, Shane:
      AbleBits has done a great job of describing how to convert data that is exactly in the state your's is in. Rather than re-type all the steps I'll point you to it. Please see Example 2 in this section of the article above.

  20. Please convert 050818 (Text format) to 05/08/2018

  21. Hi Friends,

    I am looking for help to convert values

    FROM:
    05-01-2018-01.15

    TO:

    05/01/2018 01.15 AM

    Appreciate your help on this

    Thanks
    Khuharshree

  22. How do I change the date format from dd/mm/yyyy (eg. 01/01/2018) to dd-mmm-yy (eg. 01-Jan-18)? None of the formatting tools in Excel are allowing me to change the format, and when I click on "Show Formulas", the dates (which have been prepopulated by an accounting programme and then converted into an Excel spreadsheet) are the same as they appear in the spreadsheet (eg. 01/01/2018) and they appear in the formula bar as '01/01/2018. Please help!

    1. Caro:
      Right click the cell then choose format cell then choose Date from the list and select the display format you want.

  23. how to change this Format 01-Jun-2016 to 01-06-2016 Plz Send me Value

    1. Prakash:
      Right click the cell then choose format cell then choose Date from the list and select the display format you want.

  24. Please help to concern 31122018 into 20181231
    if 31122018 is in cell A1 I want it to be 20181231 in cell A2

    1. =RIGHT(A1,4)&MID(A1,3,2)&LEFT(A1,2)

  25. I thought this would be simple but I can't find it anywhere. I have months listed in column A. I want to create a date that's the first of that month. Something like =date(2018,A5,1), where the A5 cell says, for instance, "April", so it becomes 4/1/18. Except that I can't get it to recognize the text in A5 as a month, even trying many tricks I've seen here and on other sites. Thanks in advance for your help!

    1. have the month column (A) format to custom "mmmm" to show the month only. but be sure the content data still complete 1/1/18 and so forth. In the other cell, input =TEXT(A1,"mm")&"/01/18"

  26. Closer to my current issue is "half" dates.
    I have 1-Jan CSV file report on export from system.
    The 1-Jan cell value should have been 1-01( this was equipment ID #'s).

    My resolution was special paste and split data then manual correct. I would prefer a simple formula conversion is anyone can see one.

  27. Highlight "even days of weeks": sat , mon , wed

  28. Hi,

    How would I be able to convert "WED-14-JAN-1976 00:00:00" into 14/01/1976?

    Thank you in advance!

    1. Hi, Calum!

      Please note that you can convert your text string into a date using the standard Excel Text to Columns feature and the DATE function. Please see Example 2 in this section of the article above.

      As an alternative, you can first format the column where you're going to paste the resulting dates into the necessary Date format and then enter the following formula into the first cell of this column:

      =DATEVALUE(MID(A1,5,11))

      Where A1 is the cell that contains your text string.

      After that you can copy this formula down along the column.

      1. Hi, No, but what if I need to convert serial into a date which is not in a separate cell, but being a part of the text line, so the that Date format can't be applied to this cell.

        1. Vlad:
          It sounds as if you will need to use the Text-to-Columns tool first, split out the serial value and then convert the resulting serial value using the Format Cells/Date process.

  29. Trying to convert 17-JAN-18 10.54.53.000000000 AM to 1/17/2018. Thanks.

  30. Hi ,

    Need an urgent help !!!

    Date for system gets extracted in whole numbers format i.e., 20182602

    How would i convert the same into "DD/MM/YYYY" or "YYYY/MM/DD"

    1. this is my issue too... did you find any solutions?

  31. Hi, I have date in number 43012, how can I convert it to dd/mm/yyyy.

    thanks

  32. This saved my day!
    Many thanks

  33. Hi, I am trying to convert 25/12/1875 to 25-DEC-1875
    please

    1. Hello,

      If I understand your task correctly, please try the following formula:

      =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"/12/","-DEC-"),"/11/","-NOV-"),"/10/","-OCT-"),"/9/","-SEPT-"),"/09/","-SEPT-"),"/8/","-AUG-"),"/08/","-AUG-"),"/7/","-JUL-"),"/07/","-JUL-"),"/6/","-JUN-"),"/06/","-JUN-"),"/5/","-MAY-"),"/05/","-MAY-"),"/4/","-APR-"),"/04/","-APR-"),"/3/","-MAR-"),"/03/","-MAR-"),"/2/","-FEB-"),"/02/","-FEB-"),"/1/","-JAN-"),"/01/","-JAN-")

      Please note that the formula above works only with dates prior to January 1, 1990.
      Hope this will help.

  34. Hi, I'm trying to convert 11/9/2017 to a date but when I charge the format it comes up as #########. How do I convert it to a date?

    Thanks!

    1. you need to make the column width wider

      1. sorry: this just happened to me when I tried to use the number format to correct the issue of a date displaying as text with only numbers (ie: 20200816)
        1. see if the date is actually text (either by using the filter function and then trying to sort or by grabbing a few cells and seeing if it tries to add it up..)
        if the info is actually 'text' and you want it to be 'date'
        Data > Data tools > text to columns - separate by /
        then once it's in the columns, but it back together again as =DATE(YEAR,MM,DD)
        copy and paste as values.

    2. 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 also don't forget to include the link to this comment into your email.
      I'll look into your task and try to help.

  35. If E day is 25 December 2017 and I have a column that is E + or - the number of days from E day, how do I make the next column show the calendar date from the E day

  36. I want to restrict user to use date format in mm/dd/yyyy. However when i send the excel to user. He can enter date in mm-dd-yyyy. Can you please tell what can we use in data validation to restrict user to only use mm/dd/yyyy

    1. Hello, Gaurav,

      Please try using a VBA macro. We are always ready to help you, but we do not cover the programming area (VBA-related questions).
      You may try to find the solution in VBA sections on mrexcel.com or excelforum.com.

      Sorry we can't assist you better.

  37. Hi there,

    You can call me Padman. I find your page is different than others in the net. I managed to learn a bit here.

    My problem is unique to me, haven't seen the solution here or in any other sites.

    I downloaded a bunch of data, one of them is date - "1/13/2017 8:10:00 AM" (left aligned in the cell), when I try to format that, I get "#VALUE!" error, when I use the formula "=DATEVALUE(TEXT(B__,"mm/dd/yyyy"))". For some data, like "1/12/2017 10:50:00 PM", it's converted to "12-Jan-2017". Need your help to resolve and get the dates converted.

    I'll post more appreciation once my issue gets resolved. Message me if there's any question. Thanks

    Padman

  38. Hello,

    I would like to sort below date format but it is not formatted as DATE. How do i change this to a DATE format so I can sort in chronological order? thank you in advance.

    Jan 01, 2016
    Mar 10, 2016
    Feb 11, 2016
    Feb 07, 2016
    Jan 22, 2016
    Feb 12, 2016
    Jul 07, 2016
    Jun 27, 2016
    Jul 07, 2016
    Jul 02, 2016
    Jul 17, 2016
    Jul 16, 2016
    May 29, 2016
    Dec 06, 2016
    Feb 17, 2017
    Jul 16, 2016
    Jul 12, 2016
    Feb 08, 2017
    Feb 16, 2017
    Jun 14, 2016

  39. Here's my problem:
    =CONCATENATE("Campaign Performance"," ",+Procedure!D1)

    which results in:
    Campaign Performance 43021

    When I want:
    Campaign Performance 10/13/2017

    +Procedure!D1 is a formatted date field
    Anybody have any ideas?

    1. Little bit late, but for posterity:

      You can use the TEXT function like so:
      ="Campaign Performance "&TEXT(Procedure!D1,"dd/mm/yyyy")

  40. how do i convert: "september 10, 2017" and "march 12, 2016" to dd/mm/yyyy?

    1. Hi!

      By using the TEXT function (the result will be a text string:): =TEXT(A1, "dd/mm/yyyy")

      Or, by applying the custom format (the result is a date): dd/mm/yyyy

  41. hey i want to convert date into next date..
    Example:- 1/4/2015= 2/4/2015
    15/4/2015=16/4/2015

  42. Very good solution. Converted the below format date into standard format by following the above steps. Thanks alot.

    Feb 1, 2012 2:39:39 PM AST

  43. 8.6.2017
    8.6.2017
    dd.mm.year

    calculate the days with current today date

    1. Hello,

      if I understand your task correctly, you will need to use DATE function. You will find the instructions on how to work with the function on this blog post.

      If you need an instant solution, you can try our Date & Time Wizard from our collection of add-ins for Excel - Ultimate Suite. You can download its trial version from this web page.

  44. Love, love, love your site. It helped me so much. So easy and fast finding help. I will recommend to everyone. Thank you

  45. Hi,
    Good Day!
    How to convert complete no of entries in ss from 2017-07-03 12:47:47 IST to 2017-07-03 12:47:47, i need to remove text(IST) in all the date entries.

  46. How to convert below date format into "DD/MM/YYYY HH24:MI:SS"

    Jul 1, Sat 2017 6:30:01:236
    Jul 1, Sat 2017 6:30:01:330
    Jul 1, Sat 2017 6:30:01:421
    Jul 1, Sat 2017 6:30:01:564
    Jul 1, Sat 2017 6:30:01:678
    Jul 1, Sat 2017 6:30:42:303

    Please assist.

  47. you're awesome

  48. this is awesome. thank you!

  49. please how do i convert 25/5/17 "twenty-fifth may twenty seventeen" to a format excel recognizes? thank you

  50. Hi,

    I have a bunch of date/time stamps that look like this:

    1497541289
    1497541291
    1497541294
    1497541297
    1497541299
    1497541300

    How do I convert them to real dates & times?

    Thanks,
    Pete

    1. Hi, Pete,

      could you please specify what are these values stand for?

      It's just the very last date in Excel (December 31, 9999) is stored under the 2958465 number. The stamps you provided simply can't represent the integers of the dates for Excel.
      So, for us to help you better, please specify what these values should transform into exactly.

      Thanks!

      1. Hi,
        I have also the DateStamp like : 1551363970 and I need to transform into : 2/28/2019 2:26:10 PM.
        Is any formula to conver this?
        Thank you,

        1. Hi Emil,

          Assuming your date stamp is in A2, please try this formula:
          =A2/(24*60*60)+DATE(1970,1,1)

          Don't forget to apply the needed date format to a cell with a formula.

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