Easily unify date formats in the WPS table

Common office often need to collect summary of subordinate departments reported data tables. Since the Department input does not enter a date in the Uniform standard format, summarizing the data often reveals that the dates in the summary data are in many formats. In addition to the standard format dates for 2009-2-3, May 6, 1978, the most common is the date 78.2.3, 2009.2.25, and number format dates 20090506, 912,232 (Figure 1) in text format.

Figure 1

The date format in Excel or number format cannot be uniformly formatted for dates, nor can you use formulas for date calculations. We can re-enter the date for small records, but it is not easy to re-enter the date when there are hundreds of thousands of records in the table. In fact, in the WPS table through the function can easily convert the text, the date of the number into a standard date, so to unify the set date lattice is very simple? The following is a description of the conversion method for the date in the D column of the diagram table.

1. Classification

Select D2 cell and click Ascending in the toolbar to have all dates grouped together by text date, numeric date, and standard date in the order of three categories. Then right-click the column in column D to select Insert, insert a column after (e column), and select the E column right click to select Format cells to set the appropriate date format as needed (Figure 2). Then you can classify the date for conversion, the standard date does not have to deal with, as long as the text and numerical data of two types of conversion can be.

Figure 2

2. Convert 09.2.3

For text dates that are 78.2.3, 2009.1.25, and so on, just enter the formula =value (substitute (D2, ".", "-") in E2, and then select the E2 cell to drag the Black small square (fill handle) in its lower right corner to copy the formula down to the fill. You can convert the date in column D to the standard date shown in column E (Figure 3). The function formula means to replace the D2 content with the., and then convert it to a numeric value. In this case, if you only use the substitute function to replace the date format, although the display is a date but Excel is processing it as text, so it is necessary to convert the value into a numeric value to be treated as a date by Excel.

Figure 3

3. Convert 20090203

The numeric date format for 20090201, 980201 is also simple, as long as you enter the formula =value (TEXT (D11, "# #00-00-00") in the E-column cell (E11) of the first numeric date. Then select the E11 cell and drag its fill handle to copy the formula down to fill, and convert the date in column D to the standard date displayed in column E (Figure 4). The function formula indicates that the D2 digital content is converted to text in the # #00-00-00 format to 2009-02-01, 98-02-01, and then converts the text to a numeric value using value to make Excel recognize the date.

Figure 4

OK, now just copy the converted e-column date (E2:e17 in this case), and then right-click the D2 cell to select paste paste in the value format. Then select the D column, right-click to select Format cells, and set a date format as needed.

In addition, the operation can be simpler if the two formulas are assembled together using the IF function. Just right-click the column in column D, select Insert, insert a column after (e column), and select the E column right click to select Format cells to format the date you want. In the E2 cell, enter the formula =if (ISERROR (TEXT (D2, # #00 -00-00)), IF (ISERROR (Substitute, ".", "-")), D2 ( Substitute (D2, ".", "-")), VALUE (TEXT (D2, "# #00-00-00")), and double-click the fill handle in its lower right corner to fill down. You can convert the date in column D to a uniform standard date displayed in column E. Then right click on the D column to select "Hide" to hide D column, directly in the e column as "Birth date" participate in the calculation can be. However, this formula is too long input inconvenient and easy to lose, if just want to unify the date format I believe you will prefer the way ahead. The biggest use of this large string of formulas is that you can instantly convert the various dates you enter in column D into standard dates.

Note: For a two-digit year, Excel can only be automatically recognized as a 1930-2029-year period, and if you enter a two-digit year that is not the year of this period, you will eventually have to make a direct change.