Convert Dates to Excel Formatted Dates
Dates imported from other programs frequently cause problems in Excel. Many people manually retype them, but there are easier ways to solve the problem.
Let's look at some of the date formats you might encounter and how to convert them to standard Excel dates.
As figure shows, the formulas in column B convert the data in column A to three results in column C, all of which follow the U.S. date format of mm/dd/yy.
Figure. Date formats converted to valid dates (U.S. format)
Following figure shows the same approach at work, except that the cells in column C were formatted with the European date format of dd/mm/yy.
Figure. Date formats converted to valid dates (European format)
Hopefully, these nifty formulas will take some of the stress out of working with imported dates.