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 14. Total comments: 735

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

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

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

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

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

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

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

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

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

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

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

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

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

    thanks

  14. This saved my day!
    Many thanks

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  29. you're awesome

  30. this is awesome. thank you!

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

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

  33. hi team,

    i am trying to convert excel date format from 5/29/2017 to 2017-05-29 but not working. can anyone give me a hand?

    Thanks.

  34. Please help me, If I have in a cell this: 2015-06, and I need to have Jun-2015, How should I do?

  35. Thanks for the help, great job!

  36. all failed
    i had to retype dates
    best way

  37. Hi,

    I am using Google sheet. I have a column in which the date is entered as text "Apr 21,2017 - Apr 25,2017". Now, I need your help to convert this to 04/21/2017 - 04/25/2017.

    Please help me, if possible.

    Thank you in advance.

  38. Good morning!

    my date data is in custom data type '00000000'
    changing it to string loses the leading zero and using the formula

    =DATE(RIGHT(E3,4), LEFT(E3,2), MID(E3,3,2))

    also does not work with the leading zero. any thoughts?

    example below from my worksheet

    10021980 10/02/80 (great)
    03301980 09/01/82 (not good)

    1. I had a similar problem to Joshua's with leading zeroes. I was able to solve it using an "if" argument with the "len" argument to adjust for where the date formula picked up the month and day numbers from the string.
      My data were, for example:
      14.09.74 (in cell e2, format mm.dd.yy)
      02.11.78
      my formula was: =DATE(RIGHT(E2,2),MID(E2,IF(LEN(E2)=8,4,3),2),LEFT(E2,IF(LEN(E2)=8,2,1)))
      I hope this helps anyone searching for help on this particular problem with leading zeroes.

    2. Hello, Joshua,

      could you send us your sample workbook with the data and the result you expect to get to support@ablebits.com? It's a bit difficult to help you right now, since we need to take a look at the way your data is formatted and arranged.

      1. one way around is
        =DATE(RIGHT(E3,4), LEFT(E3,len(e3)-6), MID(E3,3,len(e3-5)))

        this way it can change the spot it starts reading the number from depending on the length of the number string since it doesn't see the lead 0

        1. I am not sure if this website is still checked, but can I get help writing a formula for the following number format. 00102998, the first number would be the year, so 2000 in this case. It actually goes 1 – 0 for 1990 – 2000. Then the second two numbers are the month. The day is not important and can be any 2 digit number.

  39. Leading 0 issue
    my formula works for dates with no leading zero
    =DATE(RIGHT(E2,4), LEFT(E2,2), MID(E2,3,2))

    10021980 10/02/80 -> worked
    03301980 09/01/82

  40. Hi,

    I have a column with 'Apr 7, 2016 10:39 AM' type data in it. How do I convert this text into a date/time format so I can use it to do further calculations? I.e Find the difference in time elapsed between 'Apr 7, 2016 10:39 AM and Apr 14, 2016 09:21 AM'?

    I've tried text to column (which I don' want to do in order to protect the data source). Is there a formula for this?

    1. Hi, Izzy,

      activate the cell with a text you want to transform, then under the Home tab find Number format box, and choose Long Date or Short Date to your liking. If it already says Date – your data is ready for calculations.

      1. Hi,

        I know this is an old, thread, but I've tried everything so far, so I'm gonna give this a shot as well.
        I have the same issue as Izzy and I've tried what you advised in your reply as well as the options in the article but as a I result I only get either the date "2/1/2020" or only the time "00:15:00". Regardless of formatting, I can't get them both as Date+ time or "2/1/2020 00:15:00". Any ideas?

  41. Converting a number to date for me isn't working.
    For example (when I follow your directions), I have a "number-date" of 1926. It "converts" to a date of 4/10/1909. 1927 converts to 4/11/1909, etc.

    1. Hello Benny,

      Please give an example of your full "number-date" and expected result. We will try to work out a proper method.

  42. Hi ,

    i'm trying to sort the below list of data in excel,

    03/26 0:00
    03/26 1:00
    03/26 2:00
    03/26 9:00
    03/26 10:00

    but the results are not as per my requirement

    03/26 0:00
    03/26 1:00
    03/26 10:00
    03/26 2:00
    03/26 9:00

    expected :

    03/26 0:00
    03/26 1:00
    03/26 2:00
    03/26 9:00
    03/26 10:00

    So i believe the issue will be resolved by converting the time to 00:00 format. i dont want to do it manually . could you help me out ?

    1. Hi, Chitti,

      it is most likely that Excel recognizes the data as a plain text, and you need to convert it. Please, follow the instructions from the article to check that in your document and convert the data.

  43. how can i convert this date "25/3/2017" to "3/25/2017"

  44. Dear, I am very depressed to calculate the age in Excel so please help me to solve my problem mostly my work to calculate is for example

    10.04.1987
    04.08.1999
    10.04.1987
    04.08.1998
    10.04.1986
    04.06.1992
    30.04.1981
    02.08.1996
    11.04.1989
    02.07.1993

    more than 4000 or 5500 date of birth have been calculated please share with me any formula to solve the matter in seconds please

  45. =+"Date- "&TODAY()
    Value is -42797 How to convert In One Cell for Date ( Date-42797)
    Date-03/02/2017

  46. can you please help me in taking difference between creation time and the current time of the particular log data

    Creation time 02/23/2017 12:29:34 PM
    Current Time 02/25/2017 18:40:33 PM

    Difference in hours

  47. Can you please explain about the Date converted to General numbers.

    example: 1/1/2000 while convert to general number 36526

    How to calculate the number?

  48. Hi,

    I try to show a date in format MMMM YYYY - coming from DD-MM-YYYY. I use the below function to convert the date:

    TEXT(B5;"[$-409]mmmm yyyy")

    From date '01-12-2016' I would get 'December yyyy'.

    Any idea why year is not shown, when month is?

  49. I am using macros and i want to convert serial number 42779
    to a date. I will not be able to ctrl+1 and choose from the menu. Is there any other option ?

  50. Good day

    i tried with no success.
    9/26/2016 95
    9/26/2016 95
    blank 30551
    i calculated the number of days from today to the 2016 date, however the blank cell indicate thousands days. How do i calculate it? please help.
    i used the following formula.
    =NETWORKDAYS(G3,TODAY(),IFERROR(G3,0))

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