How to extract year from a excel cell containing an old date?


When I have a date like 2/12/2022 in a cell in an excel document (say in the cell A1),


gives 2022, but this does not work for a date like 2/12/1875.
Indeed it returns #Value!.

>Solution :

2/12/1875 is before the beginning of the date system in Excel.
To see the beginning, as per your system, write 1 and format as a date:

In general, using the =YEAR() formula is not possible before the beginning of the first serial number, thus you should resort to other options. Like this one:


or a more complicated one, that gives the last element in A2, separated by "/":


Leave a Reply Cancel reply