Date issue format

[fl9805]

Hello Everyone,

I am having troubles converting excel cells into a year format.
Can someone please help? I have attached the file.
Basically, I am trying to get the "year" (date format) for the year of incorporation but unfortunately it seems like column A has two types of format.

Thanks in advance for your help!

[bmouse]

You have to write the full date in the first column, otherwise there is no sense for the YEAR formula. Also The YEAR formula column has to be in GENERAL format. See attached file.

[samba_ravi]

=IF(A2*1<=2020,A2,YEAR(A2))
Try this formula

[fl9805]

Column A comes from a database (download) so I can't manually change the dates. My download has 10K+ dates ...

[satputenandkumar0]

try below

Please Login or Register  to view this content.

[Punnam]

Hi,
try this in b2
=IF(CELL("format",A2)="G",VALUE(A2),YEAR(A2))

Punnam

[fl9805]

Siva: it works! Thanks a million!

[fl9805]

All the formulas (3) work. Thanks so much for your help. Much appreciated!

[Vikas_Gautam]

Got the answer ..
Custom formatting is the solution..

Select the range..
Right click, and select Format Cells and click Custom and type yyyy

Don't forget to click *

[fl9805]

Thanks Siva. I think the formula works better because when you subtract two date fields, you get the correct number of years.
Try to subtract two "date" cells based on your latest answer (format change only) and you get huge numbers.