Convert Date to Another Format

  • Thread starter Thread starter Karen
  • Start date Start date
K

Karen

I have a column of data and the dates are in the format of 20090614. How can
I convert these dates so they are in the following format: 06/14/09.
ANY help would be greatly appreciated
Karen
 
Sometimes this will work.

Select the range of cells you want to convert
Goto the menu Data>Text to Columns
Click Next twice
In Step 3 of the wizard, under Column data format, select Date and YMD
Click Finish then format in the style of your choice
 
Thank you for your help. Actually I tried that before I posted this message
and it split it into 3 columns and I want it all in one column formatted
06/14/09.
Any other suggestions?
Karen
 
I don't know why it would split it into 3 cells since no delimiters were
selected.

Here's a formula method:

A1 = 20090614

B1 formula:

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

Format as Date
 
I have a column of data and the dates are in the format of 20090614.
How can I convert these dates so they are in the following format:
06/14/09.
Click to expand...

With the original data in column A, one way is to put this in column B and
copy down:
=DATE(INT(A1/10000),MOD(INT(A1/100),100),MOD(A1,100))
Use any date format for the display.

If you need to get rid of column A, select column B and use
Edit > Copy
Edit > Paste special > Values
Then delete column A.
 
Hi,

Make sure you don't select fixed width in step 1 and then don't click
anywhere in the Preview pane in step 2.

Also, is 20090614 what you see displayed in the cell or on the Formula Bar.
Maybe the formula bar shows something different than the cell and you are
splitting on a delimiter?
 
Thank you so much - This woked great! Would you be so kind as to explain the
formula in laymen's terms? What exactly are the 2 dashes used for after the
equal sign?
Thank you again, Karen
 
Thank you for your help. Yes, 20090614 is what I see in the cell and in the
formula bar. I tried what you mentioned and it didn't do anything. I may be
doing something wrong.
Karen
 
A1 = 20090614
=--TEXT(A1,"0000\/00\/00")

The TEXT function returns a *text* representation of the referenced argument
in the format that you specify. The referenced argument in this case are
string of digits in cell A1 that represent a date in yyyy/mm/dd format.

So, we need to tell the TEXT function to convert that string of digits to
this date format yyyy/mm/dd. That's what all the 0s do. 0000/00/00. The \
slash is a delimiter that tells the function to separate the 0s into the
groups of 0000 00 00.

The result of the TEXT function is the *text value* "2009/06/14" which is
not a true Excel date even though it looks like one. Dates in Excel are
really just numbers formatted to look like dates. For example, if you enter
the current date in a cell, 7/9/2009, Excel automatically formats the cell
as Date and it looks like a date. However, the true underlying value of that
date is really the number 40003. To see this format that date cell as
General.

Now, since the result of the TEXT function is a text value we need to
convert that into a numeric number so that Excel will recognize it as a true
Excel date. One way to do that is to use the double unary "--". It will
convert the text string "2009/06/14" to the numeric value 40003 then you
apply the date format of your choice and end up with a true Excel date.
 
Back
Top