Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
format a column with a function
I am trying to convert a column of dates that come in the format "20071101"
into "2007-11-01" in an automated fashion (if possible in a formula since I will include it in another one) problem, the data is imported which means that is comes in "general" format and needs to be in either "text" or "number" format for me to use any of the functions like VALUE... how can I change to entire coulmn with a function so that it converts the data to correct format? (without using TEXT TO COLUMS) thx |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
format a column with a function
How about Formatting it as 0000-00-00
?? "spreadsheet monkey" wrote: I am trying to convert a column of dates that come in the format "20071101" into "2007-11-01" in an automated fashion (if possible in a formula since I will include it in another one) problem, the data is imported which means that is comes in "general" format and needs to be in either "text" or "number" format for me to use any of the functions like VALUE... how can I change to entire coulmn with a function so that it converts the data to correct format? (without using TEXT TO COLUMS) thx |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
format a column with a function
=--TEXT(A1,"0000\-00\-00")
then format as date (important) it works as long as a date like Feb 1 2005 comes over as 20050201 and not 200521 -- Regards, Peo Sjoblom "spreadsheet monkey" wrote in message ... I am trying to convert a column of dates that come in the format "20071101" into "2007-11-01" in an automated fashion (if possible in a formula since I will include it in another one) problem, the data is imported which means that is comes in "general" format and needs to be in either "text" or "number" format for me to use any of the functions like VALUE... how can I change to entire coulmn with a function so that it converts the data to correct format? (without using TEXT TO COLUMS) thx |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
format a column with a function
While that would display it correctly it would also prevent the OP from
doing any date calculations using YEAR, MONTH etc -- Regards, Peo Sjoblom "Bob Umlas, Excel MVP" wrote in message ... How about Formatting it as 0000-00-00 ?? "spreadsheet monkey" wrote: I am trying to convert a column of dates that come in the format "20071101" into "2007-11-01" in an automated fashion (if possible in a formula since I will include it in another one) problem, the data is imported which means that is comes in "general" format and needs to be in either "text" or "number" format for me to use any of the functions like VALUE... how can I change to entire coulmn with a function so that it converts the data to correct format? (without using TEXT TO COLUMS) thx |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
format a column with a function
If you merely wish to display the number 20071101 as 2007-11-01, you can use
the custom format 0000-00-00, or if you want to turn it into text in that format you can use =TEXT(A1,"0000-00-00") If you want to convert it to an Excel date for calculations, you could use the formula =DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2)), and format the result as yyyy-mm-dd -- David Biddulph "spreadsheet monkey" wrote in message ... I am trying to convert a column of dates that come in the format "20071101" into "2007-11-01" in an automated fashion (if possible in a formula since I will include it in another one) problem, the data is imported which means that is comes in "general" format and needs to be in either "text" or "number" format for me to use any of the functions like VALUE... how can I change to entire coulmn with a function so that it converts the data to correct format? (without using TEXT TO COLUMS) thx |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
format a column with a function
You can actually convert it by using the simpler
=--TEXT(A1,"0000-00-00") -- Regards, Peo Sjoblom "David Biddulph" <groups [at] biddulph.org.uk wrote in message ... If you merely wish to display the number 20071101 as 2007-11-01, you can use the custom format 0000-00-00, or if you want to turn it into text in that format you can use =TEXT(A1,"0000-00-00") If you want to convert it to an Excel date for calculations, you could use the formula =DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2)), and format the result as yyyy-mm-dd -- David Biddulph "spreadsheet monkey" wrote in message ... I am trying to convert a column of dates that come in the format "20071101" into "2007-11-01" in an automated fashion (if possible in a formula since I will include it in another one) problem, the data is imported which means that is comes in "general" format and needs to be in either "text" or "number" format for me to use any of the functions like VALUE... how can I change to entire coulmn with a function so that it converts the data to correct format? (without using TEXT TO COLUMS) thx |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I format the A/ B Column to be fixed like numeric column | Excel Worksheet Functions | |||
How do I format the A/ B Column to be fixed like numeric column | Excel Worksheet Functions | |||
How do I format the A/ B Column to be fixed like numeric column | Excel Worksheet Functions | |||
Label Format to Column Format | Excel Discussion (Misc queries) | |||
Format Column with Same Function | Excel Worksheet Functions |