Using a formula to extract dates in Excel finds that the format is incorrect

If you want to extract the dates in the Excel cell, you'll probably get the wrong date format, or even format the date anyway.

Here are some examples and solutions.

Suppose column A is a part of the employee's ID number, and of course, in column A, the employee's birth date is preserved.

If the ID number in the A1 is: 532621195701086015

First of all, the data format of column A is regular, which is general, not allowed to change the format.

Then, when you try to A1 the date of birth in the ID number in the B1, then we can actually use the function to intercept, generally, using the mid function is most appropriate.

The formula is naturally the following formula:

=mid (a1,7,8)

However, we get the result is: 19570108

Maybe you'll realize right away that the data in the B1 cell is not in the correct format, naturally, the format is problematic, and when you set the data format of column B to "2001-3-14" in date format, you will be surprised to find that the result is the same.

To this, I also remind you that you can set the C as "2001-3-14" date format, and then the data in the B1 copy, using the selective pasting method, paste to column C, then I tell you, the result is still the same, still 19570108

So, what is causing this problem and how to solve it?

First, the cell data type problem, first set it to "2001-3-14" date format, is no problem, but this step, you have to do.

Secondly, the problem is that there is a problem in the formula, the result of the formula mentioned above is theoretically no problem.

However, the problem is that the interception of the date of birth is problematic, and the following formula is used, and the problem can be solved comprehensively:

=date (Mid (a1,7,4), Mid (a1,11,2), Mid (a1,13,2))

The date of birth dates mentioned above is in the format of 2001-3-14 and does not take into account whether there are spaces around the cell.

If the cell before and after the space, the above formula is difficult to get the correct results, but use the following formula is more appropriate.

=date (Mid (Trim (A1), 7,4), Mid (Trim (A1), 11,2), Mid (Trim (A1), 13,2))