Extract Year from Short Date

[segran]

Hi,

I have date in this format: 2011/07/15 - cell formatted as DATE.
I used the left function to extract just the year, but I got 4037 and the cell gets converted to DATE format.

How can I extract just the year - just as 2011 for this example?

Thank you

 

[Arul.rajesh]

Hi,

I have date in this format: 2011/07/15 - cell formatted as DATE.
I used the left function to extract just the year, but I got 4037 and the cell gets converted to DATE format.

How can I extract just the year - just as 2011 for this example?

Thank you

If you have the date in cell "A1" use "=Text(a1,"YYYY")"

 

[VoG]

Try

=YEAR(A1)

 

[Derek Brown]

Dates are held as numbers (the number of days since the beginning of 1900).
If your date is in A1 then
=YEAR(A1)
is what you need.

 

[Richard Schollar]

or:

=YEAR(A1)

 

[Silvrhelm]

If it is truly formatted as a date you can just use (say date is in A1) enter in A2 =year(A1)

Hope this helps

 

[mmmarks]

As per your formula,

Just format that cell to GENERAL. so that You will get Year

 

[Arul.rajesh]

As per your formula,

Just format that cell to GENERAL. so that You will get Year

If you format the cell to general you will see the serial number of the Date Excel stores.
Segran,
If you wanted only the year to be displayed, while keeping the Date in the cell intact you can use set the "custom" format "Type:" to "YYYY" in the "Number" tab in the "Format cells" Dialog. To Open it right click the cell and select "Format Cells".

 

[segran]

Thank you . It works great