mm/dd/YYYY to just YYYY? Formula?

Something tells me this may be simple, but who knows. I've got a sheet with dates listed in a column as mm/dd/YYYY OR YYYY format, but need those dates to be constrained to just the YYYY portion. Is there a formula I can use for this? I know excel doesn't natively include just a YYYY format.

Thanks in advance!

Format, on the top menu
Cells...on the drop down menu
Choose the Number tab, and then choose 'Custom'
Click on a choice in the list that is a date, and then type into the "type:" box:- YYYY
Click:- OK

When you enter any date in that cell it will only display the YYYY year.


Dfire

This only works for those numbers that are in a format other than YYYY. If I have a row of mixed mm/dd/YYYY and YYYY numbers, all the YYYY get turned to "1905" for some reason using the above suggestions. Any others?

Thanks

Originally Posted by rkmase

This only works for those numbers that are in a format other than YYYY. If I have a row of mixed mm/dd/YYYY and YYYY numbers, all the YYYY get turned to "1905" for some reason using the above suggestions. Any others?

Thanks

If you format 2006 as YYYY you get 1905 because Excel starts coutning dates at 1st January 1900 so 2000 days after that date is within 1905....

anyway....if your "dates" are in A2 down try this formula in B2 copied down, to give the year only, whether A2 contains just the year - 2006 - or a date 12/12/2006

=IF(A2<3000,A2,YEAR(A2))

format as general