Date format issues

Hi I am having problems with one column of dates whereby some are in date format and others visually appear to be but are not. I know they are not as when I create a separate column and try to extract the =YEAR(A1), only those cells which are in fact date format show the correct result. If I format the column of dates as a date, and then try to extract the year, I still get multiple failures returning "VALUE". I have noticed that the values that fail are ones with single digit months i.e. 6-06-2020 instead of 06-06-2020.

The file is extracted from a database which has thousands of lines and is formatted as an excel table, I have attached a small sample group in this file.

Any help would be greatly appreciated.
Thanks

One way:

=IFERROR(YEAR(A2),--RIGHT(A2,4))

In CH, you may need ; as the separator.

once again, post a representative sample.

Using your original sample and Power Query

Here is the Mcode and file for your review.

Please Login or Register  to view this content.

Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

- Follow this link to learn how to install Power Query in Excel 2010 / 2013.

- Follow this link for an introduction to Power Query functionality.

- Follow this link for a video which demonstrates how to use Power Query code provided.

Excel doesn't like dash - as date separators do a replace find - replace with / and the original formula works.

Try:

Please Login or Register  to view this content.