Jinshan WPS Table Uniform date Format tutorial

There are many formats for dates, so different people may enter different formats when filling out a form. What do you do if you want to change the format of the table to a uniform display of dates? Below, small set to the following this Golden Hill WPS form (see Figure 1) as an example, for everyone to explain the Jinshan WPS table Uniform Date Format method.

Figure 1: Jinshan wps Form

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: Jinshan wps Form

2. Convert 09.2.3

For text dates that are 78.2.3, 2009.1.25, and so on, just enter the formula =value in E2 (Substitute (D2, ". ","-"), 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 fill, you can convert the date in column D to the standard date displayed in column E (Figure 3). The function formula represents the D2 content. Replace with-, and then converted 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: Jinshan wps Form

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: Jinshan wps Form

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,value (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.