Mar
26

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

Since Excel is not the only application you work with, sometimes you'll find yourself working with dates imported in an Excel worksheet from a .csv file or another external source. When that happens, chances are the dates will export as text entries. Even though they look like dates, Excel won't not recognize them as such.

There are many ways to convert text to date in Excel and this tutorial aims to cover them all, so that you can choose a text-to-date conversion technique most suitable for your data format and your preference for a formula or non-formula way.

How to distinguish normal Excel dates from "text dates"

When importing data into Excel, there is often a problem with date formatting. The imported entries may look like normal Excel dates to you, but they don't behave like dates. Microsoft Excel treats such entries as text, meaning you cannot sort your table by date properly, nor can you use those "text dates" in formulas, PivotTables, charts or any other Excel tool that recognizes dates.

There are a few signs that can help you determine whether a given entry is a date or a text value.

Dates Text values
  • Right-aligned by default.
  • Have Date format in the Number Format box on the Home tab > Number.
  • If several dates are selected, the Status Bar shows Average, Count and SUM.
  • Left-aligned by default.
  • General format displays in the Number Format box on the Home tab > Number.
  • If several text dates are selected, the Status Bar only shows Count.
  • There may be a leading apostrophe visible in the formula bar.

How to distinguish dates from text strings in Excel

How to convert number to date in Excel

Since all Excel functions that change text to date return a number as a result, let's have a closer look at converting numbers to dates first.

As you probably know, Excel stores dates and times as serial numbers and it is only a cell's formatting that forces a number to be displayed as a date. For example, 1-Jan-1900 is stored as number 1, 2-Jan-1900 is stored as 2, and 1-Jan-2015 is stored as 42005. For more information on how Excel stores dates and times, please see Excel date format.

When calculating dates in Excel, the result returned by different date functions is often a serial number representing a date. To convert such serial number to date in Excel, all you have to do is change the cell formatting. For this, select a cell or a range of cells with the numbers you want to convert to dates and press Ctrl+1 to open the Format Cells dialog. On the Number tab, choose Date, select the desired date format under Type and click OK.
Converting numbers to dates in Excel

Yep, it's that easy! If you want something more sophisticated than predefined Excel date formats, please see how to create a custom date format in Excel.

If some stubborn number refuses to change to a date, check out Excel date format not working - troubleshooting tips.

How to convert 8-digit number to date in Excel

It's a very common situation when a date is input as an 8-digit number like 10032016, and you need to convert it into a date value that Excel can recognize (10/03/2016). In this case, simply changing the cell format to Date won't work - you will get ########## as the result.

To convert such a number to date, you will have to use the DATE function in combination with RIGHT, LEFT and MID functions. Unfortunately, it is not possible to make a universal formula that will work in all scenarios because the original number can be input in a variety of different formats. For example:

Number Format Date
10032016 ddmmyyyy 10-Mar-2016
20160310 yyyymmdd
20161003 yyyyddmm

Anyway, I will try to explain the general approach to converting such numbers to dates and provide a few formula examples.

For starters, remember the order of the Excel Date function arguments:

=DATE(year, month, day)

So, what you need to do is extract a year, month and date from the original number and supply them as the corresponding arguments to the Date function.

For example, let's see how you can convert number 10032016 (stored in cell A1) to date 3/10/2016.

  • Extract the year. It's the last 4 digits, so we use the RIGHT function to pick the last 4 characters: RIGHT(A1, 4).
  • Extract the month. It's the 3rd and 4th digits, so we employ the MID function to get them MID(A1, 3, 2). Where 3 (second argument) is the start number, and 2 (third argument) is the number of characters to extract.
  • Extract the day. It's the first 2 digits, so we have the LEFT function to return the first 2 characters: LEFT(A2,2).

Finally, embed the above ingredients into the Date function, and you get a formula to convert number to date in Excel:

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

The following screenshot demonstrates this and a couple more formulas in action:
Converting number to date in Excel

Please pay attention to the last formula in the above screenshot (row 6). The original number-date (161003) contains only 2 chars representing a year (16). So, to get the year of 2016, we concatenate 20 and 16 using the following formula: 20&LEFT(A6,2). If you don't do this, the Date function will return 1916 by default, which is a bit weird as if Microsoft still lived in the 20th century :)

Note. The formulas demonstrated in this example work correctly as long as all numbers you want to convert to dates follow the same pattern.

How to convert text to date in Excel

When you spot text dates in your Excel file, most likely you would want to convert those text strings to normal Excel dates so that you can refer to them in your formulas to perform various calculations. And as is often the case in Excel, there are a few ways to tackle the task.

Excel DATEVALUE function - change text to date

The DATEVALUE function in Excel converts a date in the text format to a serial number that Excel recognizes as a date.

The syntax of Excel's DATEVALUE is very straightforward:

=DATEVALUE(date_text)

So, the formula to convert a text value to date is as simple as =DATEVALUE(A1), where A1 is a cell with a date stored as a text string.

Because the Excel DATEVALUE function converts a text date to a serial number, you will have to make that number look like a date by applying the Date format to it, as we discussed a moment ago.

The following screenshots demonstrates a few Excel DATEVALUE formulas in action:
Convert text to date using the Excel DATEVALUE function.

Excel DATEVALUE function - things to remember

When converting a text string to a date using the DATEVALUE function, please keep in mind that:

  • Time information in text strings is ignored, as you can see in rows 6 and 8 above. To convert text values containing both dates and times, use the VALUE function.
  • If the year is omitted in a text date, Excel's DATEVALUE will pick the current year from your computer's system clock, as demonstrated in row 4 above.
  • Since Microsoft Excel stores dates since January 1, 1900 , the use of the Excel DATEVALUE function on earlier dates will result in the #VALUE! error.
  • The DATEVALUE function cannot convert a numeric value to date, nor can it process a text string that looks like a number, for that you will need to use the Excel VALUE function, and this is exactly what we are going to discuss next.

Excel VALUE function - convert a text string to date

Compared to DATEVALUE, the Excel VALUE function is more versatile. It can convert any text string that looks like a date or number into a number, which you can easily change to a date format of your choosing.

The syntax of the VALUE function is as follows:

=VALUE(text)

Where text is a text string or reference to a cell containing the text you want to convert to number.

The Excel VALUE function can process both date and time, the latter is converted to a decimal portion, as you can see in row 6 in the following screenshot:
Using the Excel VALUE function to convert a text string to date

Mathematical operations to convert text to dates

Apart from using specific Excel functions such as VALUE and DATEVALUE, you can perform a simple mathematical operation to force Excel to do a text-to-date conversion for you. The required condition is that an operation should not change the date's value (serial number). Sounds a bit tricky? The following examples will make things easy!

Assuming that your text date is in cell A1, you can use any of the following formulas, and then apply the Date format to the cell:

  • Addition: =A1 + 0
  • Multiplication: =A1 * 1
  • Division: =A1 / 1
  • Double negation: =--A1

Converting text to dates via mathematical operations

As you can see in the above screenshot, mathematical operations can convert dates (rows 2 and 4), times (row 6) as well as numbers formatted as text (row 8). Sometimes the result is even displayed as a date automatically, and you don't have to bother about changing the cell format.

How to convert text strings with custom delimiters to dates

If your text dates contain some delimiter other than a forward slash (/) or dash (-), Excel functions won't be able to recognize them as dates and return the #VALUE! error.

To fix this, you can run Excel's Find and Replace tool to replace your delimiter with a slash (/), all in one go:

  • Select all the text strings you want to convert to dates.
  • Press Ctrl+H to open the Find and Replace dialog box.
  • Enter your custom separator (a dot in this example) in the Find what field, and a slash in the Replace with
  • Click the Replace All

Replacing a custom delimiter with a slash

Now, the DATEVALUE or VALUE function should have no problem with converting the text strings to dates. In the same manner, you can fix dates containing any other delimiter, e.g. a space or a backward slash.

If you prefer a formula solution, you can use Excel's SUBSTITUTE function instead of Replace All to switch your delimiters to slashes.

Assuming the text strings are in column A, a SUBSTITUTE formula may look as follows:

=SUBSTITUTE(A1, ".", "/")

Where A1 is a text date and "." is the delimiter your strings are separated with.

Now, let's embed this SUBSTITUTE function into the VALUE formula:

=VALUE(SUBSTITUTE(A1, ".", "/"))

And have the text strings converted to dates, all with a single formula.

Converting text strings with custom delimiters to dates

As you see, the Excel DATEVALUE and VALUE functions are quite powerful, but both have their limits. For example, if you are trying to convert complex text strings like Thursday, January 01, 2015, neither function could help. Luckily, there is a non-formula solution that can handle this task and the next section explains the detailed steps.

Text to Columns wizard - formula-free way to covert text to date

If you are a non-formula user type, a long-standing Excel feature called Text To Columns will come in handy. It can cope with simple text dates demonstrated in Example 1 as well as multi-part text strings shown in Example 2.

Example 1. Converting simple text strings to dates

If the text strings you want to convert to dates look like any of the following:

  • 1.1.2015
  • 1.2015
  • 01 01 2015
  • 2015/1/1

You don't really need formulas, nor exporting or importing anything. All it takes is 5 quick steps.

In this example, we will be converting text strings like 01 01 2015 (day, month and year are separated with spaces) to dates.

  1. In your Excel worksheet, select a column of text entries you want to convert to dates.
  2. Switch to the Data tab, Data Tools group, and click Text to Columns.
    Switch to the Data tab and click Text to Columns.
  3. In step 1 of the Convert Text to Columns Wizard, select Delimited and click Next.
    In step 1 of the Convert Text to Columns Wizard, select Delimited and click Next.
  4. In step 2 of the wizard, uncheck all delimiter boxes and click Next.
    On step 2, uncheck all delimiter boxes and click Next.
  5. In the final step, select Date under Column data format, choose the format corresponding to your dates, and click Finish.

In this example, we are converting the text dates formatted as "01 02 2015" (month day year), so we select MDY from the drop down box.
Choose the format corresponding to your dates, and click Finish.

Now, Excel recognizes your text strings as dates, automatically converts them to your default date format and displays right-aligned in the cells. You can change the date format in the usual way via the Format Cells dialog.

Note. For the Text to Column wizard to work correctly, all of your text strings should be formatted identically. For example, if some of your entries are formatted like day/month/year format while others are month/day/year, you would get incorrect results.

Example 2. Converting complex text strings to dates

If your dates are represented by multi-part text strings, such as:

  • Thursday, January 01, 2015
  • January 01, 2015 3 PM

You will have to put a bit more effort and use both the Text to Columns wizard and Excel DATE function.

  1. Select all text strings to be converted to dates.
  2. Click the Text to Columns button on the Data tab, Data Tools group.
  3. On step 1 of the Convert Text to Columns Wizard, select Delimited and click Next.
  4. On step 2 of the wizard, select the delimiters your text strings contain.

    For example, if you are converting strings separated by commas and spaces, like "Thursday, January 01, 2015", you should choose both delimiters - Comma and Space.
    Select the delimiters your text strings contain.

    It also makes sense to select the "Treat consecutive delimiters as one" option to ignore extra spaces, if your data has any.

    And finally, have a look at the Data preview window and verify if the text strings are split to columns correctly, then click Next.

  5. On step 3 of the wizard, make sure all columns in the Data Preview section have the General format. If they don't, click on a column and select General under the Column data format options.

    Note. Do not choose the Date format for any column because each column contains only one component, so Excel won't be able to understand this is a date.

    If you don't need some column, click on it and select Do not import column (skip).

    If you don't want to overwrite the original data, specify where the columns should be inserted - enter the address for the top left cell in the Destination field.

    When done, click the Finish button.
    Make sure all columns have the General format and choose where to insert the columns.

    As you see in the screenshot above, we are skipping the first column with the days of the week, splitting the other data into 3 columns (in the General format) and inserting these columns beginning from cell C2.

    The following screenshot shows the result, with the original data in column A and the split data in columns C, D and E.

    Text strings are split into 3 different columns.

  6. Finally, you have to combine the date parts together by using a DATE formula. The syntax of the Excel DATE function is self-explanatory:

    =DATE(year, month, day)

    In our case, year is in column E and day is in column D, no problem with these.

    It's not so easy with month because it is text while the DATE function needs a number. Luckily, Microsoft Excel provides a special MONTH function that can change a month's name to a month's number:

    =MONTH(serial_number)

    For the MONTH function to understand it deals with a date, we put it like this:

    =MONTH(1&C2)

    Where C2 contains the name of the month, January in our case. "1&" is added to concatenate a date (1 January) so that the MONTH function can convert it to the corresponding month number.

    And now, let's embed the MONTH function into the month; argument of our DATE formula:

    =DATE(F2,MONTH(1&D2),E2)

And voila, our complex text strings are successfully converted to dates:
The text strings are successfully converted to dates.

Quick conversion of text dates using Paste Special

To quickly convert a range of simple text strings to dates, you can use the following trick.

  • Copy any empty cell (select it and press Ctrl+C).
  • Select the range with text values you want to convert to dates.
  • Right-click the selection, click Paste Special, and select Add in the Paste Special dialog box:
    Quick conversion of text dates using Paste Special
  • Click OK to complete the conversion and close the dialog.

What you have just done is tell Excel to add a zero (empty cell) to your text dates. To be able to do this, Excel converts a text string to a number, and since adding a zero does not change the value, you get exactly what you wanted - the date's serial number. As usual, you change a number to the date format by using the Format Cells dialog.

To learn more about the Paste Special feature, please see How to use Paste Special in Excel

Fixing text dates with two-digit years

The modern versions of Microsoft Excel are smart enough to spot some obvious errors in your data, or better say, what Excel considers an error. When this happens, you will see an error indicator (a small green triangle) in the upper-left corner of the cell and when you select the cell, an exclamation mark appears:
Error indicators in Excel

Clicking the exclamation mark will display a few options relevant to your data. In case of a 2-digit year, Excel will ask if you want to convert it to 19XX or 20XX.

If you have multiple entries of this type, you can fix them all in one fell swoop - select all the cells with errors, then click on the exclamation mark and select the appropriate option.
Fixing text dates with two-digit years using Excel Error Checking

How to turn on Error Checking in Excel

Usually, Error Checking is enabled in Excel by default. To make sure, click File > Options > Formulas, scroll down to the Error Checking section and verify if the following options are checked:

  • Enable background error checking under Error Checking;
  • Cells containing years represented as 2 digits under Error checking rules.

Turning on Error Checking in Excel

This is how you convert text to date in Excel and change dates to text. Hopefully, you have been able to find a technique to your liking. In the next article, we will tackle the opposite task and explore different ways of converting Excel dates to text strings. I thank you for reading and hope to see you next week.

You may also be interested in:

149 Responses to "How to convert text to date and number to date in Excel"

  1. Irfan says:

    Thanks

  2. Kevin says:

    I found the DATEVALUE worked but then I realized that the Day and Month were transposed. i.e. April 12th came out as Dec. 4th and March 20 produced an error.
    Is there any fix for that?

  3. John says:

    Im looking for the exact opposite: how to prevent excel from formatting my data to date and I found this useless shite !

    • John,

      This article explains how to convert text to date in Excel.

      To prevent Excel from converting your data to a date, type an apostrophe (') before it, e.g. '1/1/2015. In this case, the input data will be a text string.

      To convert existing dates to text strings, you can use the TEXT function or Text to Columns wizard, as demonstrated in How to convert date to text in Excel.

    • Gill Leo says:

      Dear John,

      The tips above are very well laid out and helpful.

      If they are not what you were looking for, should you just keep looking rather than passing such rude remarks?

    • Luhkey says:

      John, you are a useless shite. This article was the best I've seen with clear directions laid out with great visual examples. If you were looking for the opposite of this article, learn how to use Google and don't waste our time by posting your stupidity. PS: If you want the opposite, put an apostrophe before your entry in the cell, and Excel will only interpret the contents as plain text.

    • BL says:

      OMG John,

      you are very inconsiderate and self absorbed. You have no idea nor the patience to learn anything. Please go somewhere else....

  4. Ryan Campbell says:

    Do you know how to convert the text value of a date to the date format within a formula result?

    For example, if I were to have a formula that results with "Today is 7/14/15" but I want to be able to link the date to an outside link, I keep getting "Today is 42195." How can I change the format within the formula?

  5. Amit Giri says:

    I want to convert date which is in text format, number of period of date in one cell (e.g. 03/31/2014-09/30/2014,10/01/2014-04/01/2015 and so on).How am I convert it into number & change format date.

  6. Astraea82 says:

    Hi. I am trying to covert a date time value that looks like this, 13/01/90 00:00, is left aligned but does not have an apostrophe in the formula anywhere, into a standard datetime format like this, 12/12/1990 15:00:00, so that the data works with the rest of my calculations. Any suggestions?

  7. Matt says:

    Very helpful, very clearly laid out, this saved me a lot of time. Thank you Svetlana!

  8. Reed says:

    I want to convert a timestamp of 81215182533 to 8/12/15 18:25:33. How do I do that?
    Thanks

    • Erhan says:

      Exactly the problem I am facing with. Is there an answer?

      • Hi Reed,

        It's next to impossible to write a formula for this particular example, because the first item (supposedly a month number) contains just one digit (8). In other dates, there can be 2 digits, and no algorithm is smart enough to distinguish between these cases.

        If your source data had a leading zero, i.e. 081215182533 (mmddyyhhmmss format), then you could use the following formula:

        =DATE(MID(A1,5,2), LEFT(A1,2), MID(A1,2,2)) + TIME(MID(A1,7,2), MID(A1,9,2),RIGHT(A1, 2))

    • Wayne says:

      Reed, this is probably way too late to help, but I solved a similar issue today. My date column was written as plain text and did not have leading zeroes.

      The date, March 19, 2015 was written as 31915.

      DateText in cell A2
      31915

      Based on this page and some other searches, I used this formula:
      =DATE( YEAR , MONTH , DAY )
      =DATE((20&RIGHT(A2,2)),LEFT(A2, LEN(A2)-4),LEFT(RIGHT(A2,4),2))

      HTH someone,
      Wayne

  9. SAGAR NAYAK says:

    how to convert date to date value format?

  10. Tony says:

    Hi How do I group different dates in a month to a single format.

    Eg
    01/03/2015
    02/03/2015
    03/03/2015

    I want all to show Mar-15, not showing the day in the pivot table.

  11. rithy says:

    20150805
    20150806
    I want to showing is
    05/08/2015
    06/08/2015

  12. JozefSk says:

    Hallo.

    I have problem, with automatic conversion of typed vallues in Excell 2003. When i type 31-8 excell not change vallue in to date, its still text. When i set cell to date format its still text. When i type 10 its converted to 10 1 1900. This appear after i installed SP.
    How to activate automatic conversion func?

  13. ashok says:

    how to deal with this data. i want uniform data.in date format.
    ACTIVE_DATE
    3/28/2006
    3/28/2006
    3/28/2006
    3/28/2006
    3/28/2006
    3/28/2006
    3/28/2006
    3/28/2006
    3/28/2006
    37382
    38934
    39114
    39114
    39114
    39114
    39114
    39114
    39114
    39114
    39114
    39114
    39114
    39114
    39114
    39114
    39114
    39114
    39063
    7/22/2000
    7/22/2000
    7/22/2000
    7/22/2000
    7/22/2000
    7/22/2000
    3/26/1998
    3/26/1998
    3/26/1998
    36561
    36561
    36561
    36561
    36561
    36561
    36561
    36561
    36561
    36561
    36561
    36561
    36561
    36561
    36561
    36561

  14. Mick says:

    I Have various columns with different interpretations of the date and time.. working with 10,000 lines is there a formula to make them all the same.. original file is a CSV download.

    10/02/2015 19:30 13/10/2015 06:00:00
    10/04/2015 2:30 10/11/2015 18:30
    10/01/2015 19:00 10/10/2015 19:00
    10/05/2015 1:30 13/10/2015 19:05:00
    10/06/2015 2:00 13/10/2015 02:00:00
    10/09/2015 1:30 17/10/2015 14:10:00
    10/03/2015 3:30 14/10/2015 06:43:00
    10/08/2015 15:30 10/11/2015 15:30
    10/09/2015 0:00 10/10/2015 00:00
    10/07/2015 23:00 10/11/2015 16:00
    10/03/2015 5:00 10/10/2015 16:00
    10/07/2015 23:30 15/10/2015 08:00:00
    10/08/2015 23:00 17/10/2015 05:20:00

  15. Paul says:

    I want to convert a series of 6 numbers to a mm/dd/yyyy format automatically when I type the series of numbers into the cell. Example
    120457 I want the cell to display the result as 12/04/1957. Please help.

  16. Prehisto says:

    Hello!
    So I have this annoying problem, i need to change my "text" date to some kind of date format with QUARTERS so the data mining add in could recognize it as time stamp.
    My date is in format like "2007_09", I managed to get in format "2007 Q3",but still it does the trick only for selective analysis not data mining.

    I was wondering that i could format at least the year part as date, but the DATE function does not work because i need month and day as well :(.

    Do you have any ideas?

  17. Jinous says:

    I am having some problems with Excel dates.
    The dates are stored in a large Excel spreadsheet with 537 rows. I want to to import this Microsoft Access but the dates are incorrectly converted. The data in the column is stored in the following formats in the same column.

    Custom format: d/mmm/yy and mmm/yy

    Result of import: These transfer in Microsoft Access correctly.

    General format: no format any numeric or text value can be entered.

    Forexample dates stored as general format:
    2011 2011 give 03/07/1905 03/07/1905.

    I have tried using the maths option (1*A2), DATEVALUE(), VALUE() options and these did not work.

    Could you tell me the best technique in coverting these dates?

  18. thaw says:

    hi,
    i have problem on date function,
    A1 hv month name, and cel B1 have year(2015),how i can get display as first of particular month date and date. for example.
    APRIL 2015. i want to display this as 01/04/2015,
    pls help anyone

  19. Naiyar says:

    20-02-1191-20-02-2015=???? What is the formula?

    Plz Urgent

    Regards,
    Naiyar

  20. jeetendra singh says:

    Date 07/04/2014
    Date 19/07/2014
    Accounting days Excel #value!

  21. Deepak says:

    how i can convert 6 to 06:00:00 format though excel

    please let me know any one..

    • Hi Deepak,

      First, divide 6 by 24 (there are 24 hrs in a day). The formula can be as simple as =A1/24. This will yield the decimal value (for 6 the result will be 0.25).

      Now, select the cell with the formula result, right-click and select Format Cells from the context menu. The Format Cells dialog will appear, you choose Time on the left pane, and the format you want on the right pane under Type. Please see the following example for full details: How to convert numbers to time format in Excel.

  22. Hafiz says:

    Hi there,

    I am using Text to Column function to change the dates from 20160101 to look like this 01/01/16. The only problem is, its picking the formula and not the date itself. Just to describe it a bit better, all my dates are coming from another source. Is there any way I can change 20160101 to 01/01/16.

    Thanks

  23. Raman says:

    I need help. I already have a date say - 01 Apr 2016. Now I need to add 18 moths to this date. How do i do it in Excel. The result should be 01 Sep 2017.

  24. Mona De Silva says:

    Hi,
    I have copied data across into the spreadsheet. Eg:
    7-01
    6-04

    and its copying across as 7-Jan & 6-Apr. How can I stop this?
    Thank you

  25. Prakash says:

    Very good site with lot of information.

  26. Jose says:

    eg between dates;
    01/01/2016 and 31/01/2016 = Jan 16
    01/02/2016 and 28/02/2016 = Feb 16
    01/03/2016 and 31/03/2016 = Mar 16
    ... till Dec.

    How can I perform this using excel?

    Many thanks.
    Jose

    • Hi Jose,

      You can use the following formula to convert your dates to text in the specified format:
      =TEXT(A1, "mmm yy")

      Or, to change only the display format, select the cells, right-click, then click Format Cells... and set custom format to mmm yy.

      The difference is that the formula converts a date to a text value, while setting a custom format to a cell keeps the original value and changes only the visual representation of a date.

  27. sachin says:

    1/2/2015 12:13:48 PM
    31/03/2015 08:53:45

    How i will convernt into month-year
    E.G. Jan-2015

    • Hello Sachin,

      To keep the source data and change only the display format, you can select the cells, press Ctrl+1 to open the Format Cells dialog, select custom under Category on the left pane, and type mmm-yyyy in the Type box.

      To convert the dates to text, use the following formula:
      =TEXT(A1, "mmm-yyyy")

      Where A1 is the source date you want to convert.

  28. anvesh says:

    sir

    excel date format corrupted

    ex: 20141027

    how to recover it please help me sir

  29. PM says:

    Very useful article. I used the method where I first change from text to number, and then change format of the number to date. However, then I wanted to delete the column which has the "text" dates. On doing so, the date column (one in real "date" format) loses its data too, because it is dependent on the data from the "text dates". How can I delete the "text date" column and still retain the newly created date column? Thanks.

    • Hi PM,

      You simply need to replace formulas with their calculated values. To do this, select the entire date column, press Ctrl+C to copy it, then right-click the selection and click Paste Special > Values. Done!

  30. Imran Inamdar says:

    Hi
    I have column of duration in time formate 00:00:00
    now i want to concate a text to it result should like this time="00:00:00"
    for this i am using CONCATENATE
    but result is coming like this time"0.001335343545"
    I want the time should be converted as it in string formate

  31. Santosha DS says:

    please help me in converting this 2016-01-06T10:18:36.983+05:30 to values (ex:1440684229) Unix time

    thanks & regards
    Santosha DS

  32. Ganesh says:

    Very well illustrated. Great... Found the answer here for a date format that was in text format in google sheets. Tried all tricks including text to columns and then didn't know how to proceed. Date function helped it. Liked the different problems associated with this crazy dates and the solutions you have for them.

  33. Purvesh says:

    Thanks a ton. !! this saved 4 hrs of work of mine. Got it done in 5 mins instead !

  34. Avnish says:

    I have dates in the format yyyy-mm (e.g. 2015-01, 2015-02, 2015-03, etc). These dates are currently in cells with text format. I would like to split the dates and store the years as 2015, 2016, etc) in separate cells and I would like to store the months as: "January" instead of 01, "February" instead of 02, "March" instead of 03, etc in separate cells. Could you please show me how this can be done automatically. Thank you for any help you can give.

  35. Avnish says:

    Thank you for your prompt response Svetlana. The separation of the year works correctly, but for some reason the month part always shows December. I tried it on 2015-01, 2015-02 and 2015-03 and they all show the month as December instead of January, February and March. Is it because it is treating the hyphen as part of the month? How can I get around this problem?

  36. Avnish says:

    Apologies Svetlana. I tried it on a blank worksheet by inserting 2015-01, 2015-02 and 2015-03 and the formula you gave me to separate the month works correctly. I can therefore only assume that my spreadsheet is somehow corrupted, although I cannot visibly see it. I will have to figure out how to overcome this. Thank you so much for your help.

  37. ahmed says:

    how can i convert this date 10111989 to this 10/11/1989
    i tried but i could not ,so could you help me

  38. cheekian says:

    i have a thousand of date, all in the date format, but some people key in dd/mm/yy, some people key in mm/dd/yy, both also recognize as correct format in the excel, however, i know some of the date is wrongly key in, so i use "text to column function change it, but unfortunately, the correct date will automatic change to incorrect result.

    Eg,
    1/3/2015(read as 1 march 2015-original correct) after change become 3/1/2015)
    3/7/2015(should read as 7 march 2015-original wrong)after change bcm 3/7/2015)

    Any solution to change all one shot into same date format as dd/mm/yy?

  39. abhishek says:

    thank you madam i follow all formulas
    12-2-2010 =datevalue(this date)

    result # value

    plese help me

  40. kahina says:

    hello,
    can you help me please .
    I have my dates in this formats : 2,01201E+13 ( 20120123151151)

    how can i solute this problem .
    thank you

  41. Kris says:

    Help Please! I'm trying to find a formula that work for what I'm needing. I have two due dates 10/1 & 4/1 (Column B contains 1st pmt due date), (Column C contains 2nd pmt due date). I'm trying to find a formula for Column A that will show which pmt either 1st or 2nd pmt is due next.

  42. EXCEL HELP says:

    I have a field in a CSV file that is a combination of text and date and time. I first save the file as excel, then I use the MID formula to extract the date. However, I'm not able to convert it to an actual date value (so it can be used in calculations) tried all the above, my cell continues to read #VALUE! Pl help!

  43. william says:

    im trying to convert a date like 01/14/14 to a number like 20140114

    • Hi William,

      Try the following formula, where A1 is the cell with a date.

      =TEXT(A1, "yyyymmdd")

      Please keep in mind that the result of the TEXT function is always a text string, and therefore if you want to output a number, wrap it in the NUMBERVALUE function:

      =NUMBERVALUE(TEXT(A1, "yyyymmdd"))

  44. Michael says:

    how to convert text 14,06,2017 to date 14-june-17

  45. Michael says:

    Much obliged very helpful

  46. Karen says:

    I have a full row that needs to look like this 01/06/2016 HELP!!!

    Policy End Date
    20160106
    20160228
    20160308
    20160311
    20160326
    20160425
    20160507
    20160523
    20160527
    20160602
    20160602
    20160608
    20160611
    20160626
    20160705
    20160706
    20160710
    20160801
    20160802
    20160806
    20160811
    20160812
    20160910
    20161105

  47. gt says:

    i want to convert month year e.g. Mar 15 to 3/1/2015. The output or result should always be the first day of the month and should be in MM/DD/YYYY format. Thank you

    • Hello!

      You can use the following formula to return the first day of the month of the date in A1:
      =DATE(YEAR(A1), MONTH(A1), 1)

      After that, press Ctrl+1 to open the Format Cells dialog, on the left-side pane under 'Category' choose Custom, and type the following format in the Type box: mm/dd/yyyy

  48. Stella says:

    I have a date 25/03/2019 which i'd like to convert to 2019/03/25. How do I do that?

  49. Joyce says:

    Hi,

    I need help to convert a string of text to date format (ddmmyy).
    Wednesday, October 21, 2015 02:10:03 AM . Thank you

  50. Ali Akber says:

    Hello,

    please guide how to change this date 15-Mar-08 2203 (YY-MMM-DD HHMM) into 08/03/15 22:03 (DD/MM/YY HH:MM).

    currently i am doing manually one by one and i have around two hundred thousand rows.

    please guide ho to do this in excel.

  51. Raghu Jangamakote says:

    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

  52. Ram says:

    Hi,

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

    Thanks in Advance.

  53. Nora says:

    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!

  54. Usman says:

    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

  55. Manoj says:

    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

  56. Jay says:

    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?

  57. Suma says:

    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

  58. sammy says:

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

  59. nikhil says:

    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?

  60. Matt says:

    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?

  61. Pallavi says:

    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.

  62. Sandeep kumar says:

    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

  63. Tim M says:

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

    • 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

  64. Nicky says:

    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!

  65. Rohit Kamath says:

    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.

  66. Socheata says:

    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

  67. Ramesh says:

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

  68. Abdul Qadir says:

    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

  69. ABDULSAMAD says:

    bingo, you are great ablebits.

  70. Gowtham says:

    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.

  71. NARENDRA says:

    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.

  72. Ankur says:

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

  73. ANKUR says:

    Please help me use date function to entire row

  74. Indra says:

    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

  75. draqe says:

    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!

  76. jeremy says:

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

    Regards,
    Jeremmy.

  77. jeremy says:

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

  78. SATINDER says:

    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

  79. khalid says:

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

  80. Lorraine says:

    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?

  81. Doaa says:

    Thank you!

  82. DJ says:

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

  83. himanshu says:

    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

  84. Wajid Babar says:

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

  85. Basavaraj says:

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

  86. tan says:

    ifound this helpfull

  87. joanne says:

    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

  88. naren says:

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

    please resolve my query.

  89. shaikh A. M says:

    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

  90. MADHU says:

    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

  91. keneilwe says:

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

  92. Nitin says:

    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 ?

  93. Casper says:

    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?

  94. Manikandan says:

    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?

  95. rohit says:

    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

  96. Rajesh says:

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

  97. Ali says:

    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

  98. solomon says:

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

  99. chitti says:

    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 ?

  100. Benny says:

    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.

  101. Izzy says:

    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?

  102. Joshua says:

    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

  103. Joshua says:

    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)

  104. Jenny says:

    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.

  105. sam says:

    all failed
    i had to retype dates
    best way

  106. Shaun says:

    Thanks for the help, great job!

  107. Aleandro says:

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

Post a comment



Unfortunately, due to the volume of comments received we cannot guarantee that we will be able to give you a timely response. When posting a question, please be very clear and concise. We thank you for understanding!
Ultimate Suite for Excel Professionals
 
 
50+ professional tools for Excel 2016-2007 to do your daily work
Incredible product, even better tech support…AbleBits totally delivers!
Deborah Bryant
Anyone who works with Excel is sure to find their work made easier
Jackie Lee
The best spent money on software I've ever spent!
Patrick Raugh
Ablebits is a fantastic product - easy to use and so efficient.
Debra Celmer
Excel is at its best now
Annie C.
I don't know how to thank you enough for your Excel add-ins
Jennifer Morningstar
Anybody who experiences it, is bound to love it!
Kumar Nepa
AbleBits suite has really helped me when I was in a crunch!
Nelda Fink
I have enjoyed every bit of it and time am using it
Christian Onyekachi Nwosu
It's the best $100 we've ever spent!
Mike Cavanagh
I love the program, and I can't imagine using Excel without it!
Robert Madsen
One word… WOW!
Dave Brown
Love the products!
David Johnston
It is like having an expert at my shoulder helping me…
Linda Shakespeare
Your software really helps make my job easier
Jeannie C.
Thanks for a terrific product that is worth every single cent!
Dianne Young
I love your product
Brad Gibson
Awesome!!!
Sheila Blanchard