Translation of Excel Chinese date and English date

First, the general conversion method

If a is listed in the English date format, for example:

A1 19-jul-12

A2 20-jul-12

A3 21-jul-12

A4 22-jul-12

So, if you want to convert it to a date in Chinese, if you put it in column B, first you have to set the B column to the date format in Chinese, by selecting column B and then finding the menu "format" → "Cell".

In the dialog box, find the date, and in the right type, find a date format in Chinese, such as: March 14, 2001

Then, use the simple formula directly in B1: =a1 this way, the conversion to the Chinese date, the other cells directly using the fill is done.

In this way, in column B, we will get the following Chinese date

July 19, 2012

July 20, 2012

July 21, 2012

July 22, 2012

July 23, 2012

As the method above, you can use the method settings of the data type of the cell directly.

Second, other conversion methods

① species situation

If the English date form is: May 10rd,2012

It doesn't work to set the date format in cell format directly like this data

To change to a Chinese date form, first: replace the RD, Th, St, ND, after the day in the date (that is, the number in the middle) with empty

Then set the format of the cell to the date format is yyyy-m-d (also can be replaced by the date format you want), you can

② species situation

If there is such an English date data is: Feb 2008 12:00am

Such a format can be converted to a numeric type by using the following function

Let's say your date is in A1.

=text (MID (a1,5,2) & "-" &left (a1,3) & "-" &mid (a1,8,4) & "" &mid (a1,12,6), "YYYY day mm month DD day, a.m. PM h mm minute ")