Divide date into separate columns

[Althea]

Hi
Need to get year of a date entry into a separate column, whats the formula?
eg 2/5/1900..want 1900 in separte column

Thanks
Althea

 

[Peo Sjoblom]

=YEAR(A1)

where A1 holds the date

--
Regards,

Peo Sjoblom

(No private emails please)

 

[Althea]

Many thanks
is there any way to get this to work on pre1900 years
Thanks
Althea

 

[Peo Sjoblom]

There are no pre 1900 years when it comes to excel, it is text but you can
get it through a text function

=RIGHT(B1,4)


(excel year 0 is Jan 0 1900 thus anything before that will just be numbers
but seen as text by excel)

--
Regards,

Peo Sjoblom

(No private emails please)

 

[Peo Sjoblom]

You might want to combine those into one formula like

=IF(ISERR(YEAR(A1)),--RIGHT(A1,4),YEAR(A1))

--
Regards,

Peo Sjoblom

(No private emails please)

 

[Althea]

Thankyou for all help. Problem solved

There are no pre 1900 years when it comes to excel, it is text but you can
get it through a text function

=RIGHT(B1,4)


(excel year 0 is Jan 0 1900 thus anything before that will just be numbers
but seen as text by excel)

--
Regards,

Peo Sjoblom

(No private emails please)

 

[wjohnson]

easiest way is to got Menu Bar - Select "DATA" - "TEXT TO COLUMNS" an
then put two dividers where you want the text to split and follow th
direction on the pop-up men

 

[Peo Sjoblom]

It depends on if you want to keep the original date and just parse the year
from it, if so it won't work since text to columns will split it into 3
columns, the OP's subject maybe sounds like that is what she wanted but the
body of here message might be interpreted to get the year
--
Regards,

Peo Sjoblom

(No private emails please)