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 `2:2`

:

```
=IFERROR(VALUE(LEFT(A2,4)),VALUE(RIGHT(A2,4)))
```

For the full date, the formula is a bit more complicated:

```
=IF(MID(A2,3,1)="-",A2,CHOOSE((LEN(A2)-4)/3+1,"01-01","01"&MID(A2,5,3),RIGHT(A2,2)&MID(A2,5,3))&"-"&LEFT(A2,4))
```

**Explanation:**

The prettified version of the full date formula is as follows:

```
=
IF(
MID(A2,3,1)="-",
A2,
CHOOSE(
(LEN(A2)-4)/3+1,
"01-01",
"01"&MID(A2,5,3),
RIGHT(A2,2)&MID(A2,5,3)
)
&"-"&LEFT(A2,4)
)
```

It is pretty straightforward. The only slightly tricky thing is the first argument of the `CHOOSE()`

function, `(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]`

.

**Notes:**

- The prettified formula actually works if it is entered.

**Caveats:**

- 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
`A2`

s in the formulas with `TRIM(A2)`

, or better yet with `SUBSTITUTE(A2," ","")`

, will solve this issue.

`dd-mm-yyyy`

according to the type of input in the cell.`TEXT`

can be used individually if I know the format in input. I have thousands of cells of input (in multiple formats) that I want to convert to output. However I am not able to get the output. Once I get the output, I can extract the year using`YEAR()`

3more comments