format a column with a function

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
=--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
 
While that would display it correctly it would also prevent the OP from
doing any date calculations using YEAR, MONTH etc


--


Regards,


Peo Sjoblom
 
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
 
You can actually convert it by using the simpler

=--TEXT(A1,"0000-00-00")


--


Regards,


Peo Sjoblom
 
Back
Top