Since your input data contains dates before 1900, things get a little trickier. The simplest way to deal with this is to exclusively work with the text representations of the dates.
If you don't actually need the full date, but just require the year, enter this basic formula in any cell in row
For the full date, the formula is a bit more complicated:
The prettified version of the full date formula is as follows:
It is pretty straightforward. The only slightly tricky thing is the first argument of the
(LEN(A2)-4)/3+1. This maps the lengths of the input date strings to 1-based indexes, i.e.,
[yyyy, yyyy-mm, yyyy-mm-dd, dd-mm-yyyy] →
[4, 7, 10, 10] →
[1, 2, 3, 3].
- The prettified formula actually works if it is entered.
- The formulas assume that all the input dates are stored as text. (The formulas are easily modifiable to cater for dates stored as serial numbers.)
- If the input dates contain leading/trailing spaces, the above formulas may not work correctly. They will definitely not work with spaces (or other characters) elsewhere in the date strings. Replacing all the
A2s in the formulas with
TRIM(A2), or better yet with
SUBSTITUTE(A2," ",""), will solve this issue.