Convert date format (only year)

Question

I have a range of dates in format dd/mm/yyyy which is recognised as date by excel. Some of the dates are not identified in excel which are in dd/mm/yy.

For example

23/03/2015 -> recognised as date
23/03/15 -> recognised as text format

when i use filter in date column, all the dates with dd/mm/yyyy are recognised as date under yyyy. where as dates with dd/mm/yy are recognised differently.

I need a formula to convert all the dd/mm/yy dates format to dd/mm/yyyy. FYI All the dates are in 2015 and 2016 years

Try wrapping the value in VALUE function. If dates are column C, D2=VALUE(C2). Format column D as date. — eshwar

lorgan lorgan · Accepted Answer · 2016-11-08T09:37:21

Not the most elegant solution but this should get the job done:

=IFERROR(DATE(RIGHT(G3,LEN(G3)-5),MID(G3,3,2),LEFT(G3,2)),G3)

Assumes you have leading zeros for single digit daya i.e. 02/03/15 rather than 2/03/15

Convert date format (only year)

2 Answers