column with dates and numbers

[Luís Miguel Ramires Vieira Reis RAM]

Hi,

i got a column with dates and numbers.

id like to convert all column data in date format.

plz help?

thanks

 

[Bernard Liengme]

Please give us some examples of what you have in the cells
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Luís Miguel Ramires Vieira Reis RAM" <[email protected]>
wrote in message
news:07c60737-4a14-431c-aac7-28e16334dcfa@l33g2000pri.googlegroups.com...

 

[Luís Miguel Ramires Vieira Reis RAM]

Please give us some examples of what you have in the cells
best wishes
--
Bernard V Liengme
Microsoft Excel MVPhttp://people.stfx.ca/bliengme
remove caps from email

"Luís Miguel Ramires Vieira Reis RAM" <[email protected]>
wrote in message







- Mostrar texto citado -

Example:

appears like that

column A

30-06-2008
13-11-2008
blank
30-06-2008
13-11-2008
blank
30-06-2008
13-11-2008
blank
30-06-2008
13-11-2008



but if i FORMAT all colum data cells to GENERAL some cells are numbers
and other are data.


39629
13-11-2008
blank
39629
13-11-2008
blank
39629
13-11-2008
blank
39629
13-11-2008


I would like to format cells to GENERAL and all dates become converted
in numbers "39629,p.ex".

(sorry the confusion of explanation).

 

[Bernard Liengme]

The entries like 39629 are actual dates; the others are text.
Let's say the first entry is in A1
In B1 enter
=IF(ISTEXT(A1),DATE(RIGHT(A1,4),MID(A1,4,2),LEFT(A1,2)),IF(ISNUMBER(A1),A1,""))
Copy down the column. This will convert the text to dates

If you wish you could then select all of the B values, copy and with then
still selected use Edit | Paste Special with Values specified
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Luís Miguel Ramires Vieira Reis RAM" <[email protected]>
wrote in message

Please give us some examples of what you have in the cells
best wishes
--
Bernard V Liengme
Microsoft Excel MVPhttp://people.stfx.ca/bliengme
remove caps from email

"Luís Miguel Ramires Vieira Reis RAM" <[email protected]>
wrote in
message







- Mostrar texto citado -

Example:

appears like that

column A

30-06-2008
13-11-2008
blank
30-06-2008
13-11-2008
blank
30-06-2008
13-11-2008
blank
30-06-2008
13-11-2008



but if i FORMAT all colum data cells to GENERAL some cells are numbers
and other are data.


39629
13-11-2008
blank
39629
13-11-2008
blank
39629
13-11-2008
blank
39629
13-11-2008


I would like to format cells to GENERAL and all dates become converted
in numbers "39629,p.ex".

(sorry the confusion of explanation).

 

[Luís Miguel Ramires Vieira Reis RAM]

The entries like 39629 are actual dates; the others are text.
Let's say the first entry is in A1
In B1 enter
=IF(ISTEXT(A1),DATE(RIGHT(A1,4),MID(A1,4,2),LEFT(A1,2)),IF(ISNUMBER(A1),A1,­""))
Copy down the column. This will convert the text to dates

If you wish you could then select all of the B values, copy and with then
still selected use Edit | Paste Special with Values specified
best wishes
--
Bernard V Liengme
Microsoft Excel MVPhttp://people.stfx.ca/bliengme
remove caps from email

"Luís Miguel Ramires Vieira Reis RAM" <[email protected]>
wrote in message



Example:

appears like that

column A

30-06-2008
13-11-2008
blank
30-06-2008
13-11-2008
blank
30-06-2008
13-11-2008
blank
30-06-2008
13-11-2008

but if i FORMAT all colum data cells to GENERAL some cells are numbers
and other are data.

39629
13-11-2008
blank
39629
13-11-2008
blank
39629
13-11-2008
blank
39629
13-11-2008

I would like to format cells to GENERAL and all dates become converted
in numbers "39629,p.ex".

(sorry the confusion of explanation).- Ocultar texto citado -

- Mostrar texto citado -

problem solved

thanks all

i made a =datevalue and copy paste special.