How to convert date to number string or text format in excel?

Excel for mocmien.info 365 Excel for the website Excel 2021 Excel 2019 Excel năm nhâm thìn Excel 2013 Excel 2010 Excel 2007 More...Less

Occasionally, dates may become formatted và 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 sầu been imported or pasted from an external data source as text.

Bạn đang xem: How to convert date to number string or text format in excel?

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:

*
.

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


Convert text dates with two-digit years by using Error Checking

If you import data into lớn 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.

*

You can use the Error Indicator khổng lồ convert dates from text to date format.


Notes: First, ensure that Error Checking is enabled in Excel. To vày that:

Cliông xã File > Options > Formulas.

In Excel 2007, cliông chồng the mocmien.info Office button

*
, then clichồng Excel Options > Formulas.

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

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


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

Cliông chồng the error button that appears near the selected cell(s).

*
 

On the menu, cliông chồng either Convert XX khổng lồ 20XX or Convert XX to 19XX. If you want to lớn dismiss the error indicator without converting the number, cliông xã Ignore Error.

*
 

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

*
 

Once you have sầu 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 over up looking lượt thích a series of numbers like in the picture below, you probably would want khổng lồ 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.

*

Select the cell, cell range, or column that you want to lớn reformat.

Xem thêm: Hôn Nhân Viên Mãn Của Lưu Đào Chương Trình Truyền Hình, Lưu Đào Chỉ Có Có Hai Người Bạn Thân

Cliông chồng Number Format & piông chồng the date format you want.

The Short Date format looks lượt thích this:

*

The Long Date includes more information lượt thích in this picture:

*


Convert text dates by using the DATEVALUE function

To convert a text date in a cell lớn a serial number, use the DATEVALUE function. Then copy the formula, select the cells that contain the text dates, và use Paste Special khổng lồ apply a date format to them.

Follow these steps:

Select a blank cell và verify that its number format is General.

In the blank cell:

Enter =DATEVALUE(

Cliông xã the cell that contains the text-formatted date that you want lớn convert.

Enter )

Press ENTER, và 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 mặc định, 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 inlớn a range of contiguous cells, select the cell containing the formula that you entered, & then drag the fill handle

*
across a range of empty cells that matches in form size the range of cells that contain text dates.

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

Select the cell or range of cells that contains the serial numbers, & then on the Home tab, in the Clipboard group, cliông xã Copy.

Keyboard shortcut: You can also press CTRL+C.

Select the cell or range of cells that contains the text dates, & then on the Home tab, in the Clipboard group, cliông xã the arrow below Paste, & then click Paste Special.

In the Paste Special dialog box, under Paste, select Values, & then cliông xã OK.

On the Home tab, cliông chồng the popup window launcher next khổng lồ Number.

In the Category box, click Date, and then clichồng the date format that you want in the Type danh sách.

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


Need more help?

You can always ask an expert in the Excel Tech Community or get tư vấn in the Answers community.