column with dates and numbers

  • Thread starter Thread starter Luís Miguel Ramires Vieira Reis RAM
  • Start date Start date
L

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
 
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 -
Click to expand...

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).
 
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 -
Click to expand...

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).
 
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 -
Click to expand...

problem solved

thanks all

i made a =datevalue and copy paste special.
 
Back
Top