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

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

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

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

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

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

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

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

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

  9. you're awesome

  10. this is awesome. thank you!

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

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

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

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

  15. Thanks for the help, great job!

  16. all failed
    i had to retype dates
    best way

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  31. how to convert exel colum 4,5,6,7,8,1,2,3, this serial 1, 2,3,4,5,6,7,8 plz send me formula

  32. Respected

    i want to to convert some date of birth of format 24/01/2017 into words of
    format twenty for January twenty seventeen in excel

  33. june 17 2012 it is a text string i have to convert it into date format like 17-jun-2012

    please resolve my query.

  34. Hello, how do i convert dates for example 2017-02 to 17-Dec? I need to enter a formula in the next column.
    2017-02
    2016-12
    2016-12
    2017-01
    2017-01
    2017-02
    2017-02
    2017-03
    2017-01
    2017-02
    2016-12

  35. ifound this helpfull

  36. 108462 748 3 11710 9 1
    i want extract date as 03/11/17
    so please help me

  37. HI Svetlana Cheusheva,
    Need to convert this 26/06/2005 to date format i.e.
    twenty six June Two Thousand Five
    please help..

  38. how to convert date like 12-12-2006 to tweel december two thousend six pls help me and pls say is this possible or not in ms-excel

  39. 13/11/2016 16:44:32 this is text please convert to australian date and time

  40. Thank you!

  41. how can i convert a text date like this Nov/28/16 1:52 PM to a regular date cell like this 11/28/16?

  42. Hello,
    How can I change the date Eg 1116 to text format 1116?

  43. I WRITE LONG NUMERIC VALUE IN EXCEL IT CONVERT INTO SCNTIFIC VALUE 1.1032E+28 WHEN I FORMAT CELL INTO TEXT IT DOESNOT GIVS ME CORRECT VALUE

  44. help on quarterly date display e.g Jan-March.

  45. thanks for your help, though am not able to find a formula that can display cell. please help.

    Regards,
    Jeremmy.

  46. hello,

    i got for example:

    oct-19-2016

    and want to convert it into:

    19.10.2016

    do u have a solution for this?
    thanks anyway!

  47. Can the below formatted data be converted to Date format:

    Sep 1 2016 10:43AM
    Sep 2 2016 10:53AM
    Sep 20 2016 10:37AM
    Sep 21 2016 10:54AM
    Sep 22 2016 10:51AM

  48. Please help me use date function to entire row

  49. Can I apply date value formula to entire row. If yes please help

  50. I have a data to be entered excel like 4-12 and 5-8 ...etc. But it is automatically converted in to date format. How to overcome it.

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