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

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

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

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

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

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

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

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

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

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

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

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

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

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

    please resolve my query.

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

  15. ifound this helpfull

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

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

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

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

  20. Thank you!

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

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

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

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

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

    Regards,
    Jeremmy.

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

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

  28. Please help me use date function to entire row

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

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

  31. In my case I am having dates in different format as mentioned below. How can I convert those in to a single format.Please share your ideas.
    Eg;
    01-12-2015
    12-30-2016
    2016.30.12
    12.30.2016

    Help me to convert theses above date to common format like date-month-year.

  32. bingo, you are great ablebits.

  33. Hii how are you..
    I am facing to convert Jun 4 2016 12:01:18:000AM to date format.
    Please help me in this regard

    Thanks

  34. HI Svetlana Cheusheva,
    Need to convert this 1471408181 to date format please help

  35. Dear Svetlana Cheusheva,

    I would like to ask you how to convert mm/dd/yy to dd-mmm-yy. I definitely can't find the solution. Could you help me to solve this problem??

    Thank you, XD

    Example:
    mm/dd/yy to dd-mmm-yy
    04/02/2016 to 02-Apr-16

    1. Dear Socheata,

      You can easily do this by changing the cell format:

      Select the cell(s) with dates, press Ctrl+1 to open the Format Cells dialog, select Custom under Category, and type the format code dd-mmm-yy in the Type box. That's it!

      For the detailed instructions, please see How to change the date format in Excel.

  36. Hi, I have scraped a date from a site and I have in the following format:
    "
    August 11, 2016
    ,
    6:36 am
    "

    I used =TRIM(MID(R2,4,17))&" "&TRIM(MID(R2,54,8)) to get the display to
    "August 11, 2016 12:04 pm"

    I am unable to convert it to Excel date. Would you have any advise/ tips on the approach? Thank you in advance.

    Great page BTW. Fantastic examples.

  37. Hi, Svetlana.

    Can you help me on what formula should I input in converting the following text to date and time? I am doing a tracker of our team's login and logout and I would need to calculate the total hours of our timelogs. Given the data, I would need to convert the text first to time and date.

    Sample text: 01/06/16 (03:45pm)

    Many thanks in advance!

  38. Hello, is it possible to change date format from "Nov 05, 2015" to "11/5/2015"

    1. Hi Tim,

      If your dates are entered as dates and not as text strings, select all the dates you want to re-format, press Ctrl+1 to open the Format Cell dialog, select Custom under Category, and type the following format in the Type box: mm/d/yyyy

  39. Dear All,

    We have a question please check and resolve.

    Date-1 , Date-2
    7/17/2012 , 41125

    Enter a formula to get the no of hours between the two dates?

    Regards,
    Sandeep

  40. Hi, how do I change a quarter into a date. So if value is 20163, I want to get first day of Q3 i.e. 1-July-2016.
    Thanks.

  41. Hi. I need to convert a date to financial year e.g. 14/07/2016 to 2016/17. There doesn't appear to be an option in excel to do so. I assume that I would need an If statement such as =IF(A1=>01/4/2016, "2016/17", "2015/16") but in order to do this I have to amend thestatement. Is there any other way to achieve this as I have to cover many years of data?

  42. I have date format like 01-01-1900 00:00:00 and i want the format to be like 1900-01-00 00:00 but it's not changing as it is text format and i have tried many solutions like clicking text to columns,changing format in format cells ,apply format painter,specifying =datevalue() function in empty cell but none of them worked.Can anybody help me in overcoming this issue?

  43. Thank you!
    "Text to Columns wizard - formula-free way to covert text to date"
    helped me!!!!

  44. HI,
    I am facing a weird issue, i generated report from QUIX, where i see few of the dates as *******, i do not know how it has come and how to convert those into dates.

    Please help in this regard,
    Suma

  45. Hello,
    For some reason my company's database exports the date as "Jun 17th, 2016" in text format. I can't get any of these functions to work and my suspicion is the "th"s and "nd"s, any ideas?

    1. I'd use a nested SUBSTITUTE to replace the "st,", "th," and "rd," with "," then apply the formula.

  46. Hi,

    In a excel column few cells has correct date format but other cells date format is shown in text. How to format the entire column in correct date format.

    Regards,
    Manoj

  47. 0
    down vote
    favorite
    I have been trying to use the CONCATENATE function in the excel for creating multiple values of the financial statements for different years. My main formula is as under:

    =VLOOKUP(TEXT(CONCATENATE(A1,"-",C6,"-",RIGHT(B7,2)),"dd-mm-yy"),range,column,FALSE)

    Although the (TEXT(CONCATENATE(A1,"-",C6,"-",RIGHT(B7,2)),"dd-mm-yy") gives me the date that I want (e.g. 10-Jun-10) yet when I use this in the VLOOKUP it does not treat it as a date and returns the error. How can I fix this?

    I have also tried to put the DATEVALUE and VALUE function into it yet the VLOOKUP still won't give the desired result and will provide the outcome as #NA

  48. Hi,
    I had exported a spreadsheet from a work site, and the date came up as yyyymm, but when I convert it in order to create more formulas it isn't working. The example is 201001, is the year 2010 and January, but when I convert it comes up as April 2450. I want to fix this so I can create a formula that can subtract six months. Please help, Thank you!

  49. Hi,

    How to change 6 26 AM to 6:26 AM in excel?

    Thanks in Advance.

  50. I am trying to format the following date cells which is "General" but for some reason it doesn't work. Please help.
    01/25/2016
    01/24/2016
    01/24/2016
    01/24/2016
    01/20/2016
    01/19/2016
    01/16/2016

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