date conversion after pasting

[DC Gringo]

I am pasting two columns into Excel...the first is supposed to be a date and
is in format YYYYMMDD, the second a simple integer...here's an example:

Date, Value

--------------------

20050120, 5
20051105, 6
20060213, 4


Each time I copy it into Excel and try to format it as a date, it gives me
only "############"

How can I convert this to show and act like a date so I can chart with it?

_____
DC G

 

[Pete_UK]

I assume that the data is in two columns and that the comma in your
example is there just to separate the two items. If this is the case
then insert a new column B and in B1 enter the formula;

=VALUE(RIGHT(A1,2)&"/"&MID(A1,5,2)&"/"&LEFT(A1,4))

Format the cell as Date (dd/mm/yyyy) then copy down for as many values
as you have in column A. Then highlight the formulae in column B,
<copy>, Edit | Paste Special | Values (check) and OK, followed by
<enter>. Then you can delete column A.

Hope this helps.

Pete

 

[Roger Govier]

Hi

One way
Mark the column of date cells.
Data>Text to Columns>Next>Next> click Date radio button>Select YMD as
format>Finish
Widen the column so that you can see the date.
Works fine with UK Regional settings.