CODEWITHSUNDEEP
excelexcel-formulaformula
rajendra kodavaty
rajendra kodavaty

Reputation: 13

Convert date format (only year)

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

Upvotes: 0

Views: 363

Answers (2)

CallumDA
CallumDA

Reputation: 12113

Here is one of my quick fixes - firstly, format your range as dates. Then, in a separate cell just put the number 1. Copy the cell with number 1 in and highlight your dates range. Paste special values (Alt+E+S+V) and before you press OK, select multiply or press M.

This will multiply your range by 1, turning any text into numbers - which will be accordingly formatted as dates

Additional

As you asked for a formula. Here it is:

=A1*1

Assuming your dates are in column A, drag that formula down

Upvotes: 0

lorgan
lorgan

Reputation: 163

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

Upvotes: 0

Related Questions