Divide date into separate columns
 

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

Divide date into separate columns
 

=YEAR(A1)

where A1 holds the date

--
Regards,

Peo Sjoblom

(No private emails please)


"Althea" wrote in message
...
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

Divide date into separate columns
 

Many thanks
is there any way to get this to work on pre1900 years
Thanks
Althea
"Peo Sjoblom" wrote in message
...
=YEAR(A1)

where A1 holds the date

--
Regards,

Peo Sjoblom

(No private emails please)


"Althea" wrote in message
...
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

Divide date into separate columns
 

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)


"Althea" wrote in message
...
Many thanks
is there any way to get this to work on pre1900 years
Thanks
Althea
"Peo Sjoblom" wrote in message
...
=YEAR(A1)

where A1 holds the date

--
Regards,

Peo Sjoblom

(No private emails please)


"Althea" wrote in message
...
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

Divide date into separate columns
 

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)


"Peo Sjoblom" wrote in message
...
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)


"Althea" wrote in message
...
Many thanks
is there any way to get this to work on pre1900 years
Thanks
Althea
"Peo Sjoblom" wrote in message
...
=YEAR(A1)

where A1 holds the date

--
Regards,

Peo Sjoblom

(No private emails please)


"Althea" wrote in message
...
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

Divide date into separate columns
 

Thankyou for all help. Problem solved
:)
"Peo Sjoblom" wrote in message
...
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)


"Althea" wrote in message
...
Many thanks
is there any way to get this to work on pre1900 years
Thanks
Althea
"Peo Sjoblom" wrote in message
...
=YEAR(A1)

where A1 holds the date

--
Regards,

Peo Sjoblom

(No private emails please)


"Althea" wrote in message
...
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

Divide date into separate columns
 

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


--
wjohnson
------------------------------------------------------------------------
wjohnson's Profile: http://www.hightechtalks.com/m390
View this thread: http://www.hightechtalks.com/t2306725

Divide date into separate columns
 

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)


"wjohnson" wrote in message
...

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


--
wjohnson
------------------------------------------------------------------------
wjohnson's Profile: http://www.hightechtalks.com/m390
View this thread: http://www.hightechtalks.com/t2306725