| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do I concatenate two columns when one is in date format? | Excel Discussion (Misc queries) | |||
| How do I calculate if a date is in a certain time frame? | Excel Worksheet Functions | |||
| How to compare the lists in two columns & separate odd one ? | Excel Discussion (Misc queries) | |||
| split a single column into 2 separate columns | Excel Worksheet Functions | |||
| Combined date time cell to separate date & time components | Excel Discussion (Misc queries) | |||
Linear Mode
