Sign in with Microsoft
Sign in or create an account.
Hello,
Select a different account.
You have multiple accounts
Choose the account you want to sign in with.

Occasionally, dates may become formatted and stored in cells as text. For example, you may have entered a date in a cell that was formatted as text, or the data might have been imported or pasted from an external data source as text.

Dates that are formatted as text are left-aligned in a cell (instead of right-aligned). When Error Checking is enabled, text dates with two-digit years might also be marked with an error indicator: Cell with a formula error .

Because Error Checking in Excel can identify text-formatted dates with two-digit years, you can use the automatic correction options to convert them to date-formatted dates. You can use the DATEVALUE function to convert most other types of text dates to dates.

If you import data into Excel from another source, or if you enter dates with two-digit years into cells that were previously formatted as text, you may see a small green triangle in the upper-left corner of the cell. This error indicator tells you that the date is stored as text, as shown in this example.

Cells with green error indicator in upper-left corner

You can use the Error Indicator to convert dates from text to date format.

Notes: First, ensure that Error Checking is enabled in Excel. To do that:

  1. Click File > Options > Formulas.

    In Excel 2007, click the Microsoft Office button Office button image , then click Excel Options > Formulas.

  2. In Error Checking, check Enable background error checking. Any error that is found, will be marked with a triangle in the top-left corner of the cell.

  3. Under Error checking rules, select Cells containing years represented as 2 digits.

Follow this procedure to convert the text-formatted date to a normal date:

  1. On the worksheet, select any single cell or range of adjacent cells that has an error indicator in the upper-left corner. For more information, see Select cells, ranges, rows, or columns on a worksheet.

    Tip: To cancel a selection of cells, click any cell on the worksheet.

  2. Click the error button that appears near the selected cell(s).

    Error button
     

  3. On the menu, click either Convert XX to 20XX or Convert XX to 19XX. If you want to dismiss the error indicator without converting the number, click Ignore Error.

    Commands for converting dates
     

    The text dates with two-digit years convert to standard dates with four-digit years.

    Converted dates
     

    Once you have converted the cells from text-formatted dates, you can change the way the dates appear in the cells by applying date formatting.

If your worksheet has dates that were perhaps imported or pasted that end up looking like a series of numbers like in the picture below, you probably would want to reformat them so they appear as either short or long dates. The date format will also be more useful if you want to filter, sort, or use it in date calculations.

column of dates in text format

  1. Select the cell, cell range, or column that you want to reformat.

  2. Click Number Format and pick the date format you want.

The Short Date format looks like this:

change data to short date format from ribbon

The Long Date includes more information like in this picture:

button on the ribbon to change to long date format

To convert a text date in a cell to a serial number, use the DATEVALUE function. Then copy the formula, select the cells that contain the text dates, and use Paste Special to apply a date format to them.

Follow these steps:

  1. Select a blank cell and verify that its number format is General.

  2. In the blank cell:

    1. Enter =DATEVALUE(

    2. Click the cell that contains the text-formatted date that you want to convert.

    3. Enter )

    4. Press ENTER, and the DATEVALUE function returns the serial number of the date that is represented by the text date.
       

      What is an Excel serial number?

      Excel stores dates as sequential serial numbers so that they can be used in calculations. By default, January 1, 1900, is serial number 1, and January 1, 2008, is serial number 39448 because it is 39,448 days after January 1, 1900.To copy the conversion formula into a range of contiguous cells, select the cell containing the formula that you entered, and then drag the fill handle across a range of empty cells that matches in size the range of cells that contain text dates.

  3. After you drag the fill handle, you should have a range of cells with serial numbers that corresponds to the range of cells that contain text dates.

  4. Select the cell or range of cells that contains the serial numbers, and then on the Home tab, in the Clipboard group, click Copy.

    Keyboard shortcut:  You can also press CTRL+C.

  5. Select the cell or range of cells that contains the text dates, and then on the Home tab, in the Clipboard group, click the arrow below Paste, and then click Paste Special.

  6. In the Paste Special dialog box, under Paste, select Values, and then click OK.

  7. On the Home tab, click the popup window launcher next to Number.

  8. In the Category box, click Date, and then click the date format that you want in the Type list.

  9. To delete the serial numbers after all of the dates are converted successfully, select the cells that contain them, and then press DELETE.

Need more help?

You can always ask an expert in the Excel Tech Community or get support in Communities.

Need more help?

Want more options?

Explore subscription benefits, browse training courses, learn how to secure your device, and more.

Communities help you ask and answer questions, give feedback, and hear from experts with rich knowledge.

Was this information helpful?

What affected your experience?
By pressing submit, your feedback will be used to improve Microsoft products and services. Your IT admin will be able to collect this data. Privacy Statement.

Thank you for your feedback!

×