Hi all,

I have two problems with the format of two different columns of dates i have
coppied onto an excel sheet. I need to be able to have the dates in a
standard date format in order to be able to work with them. The problems are
as follows:

1)The first column of dates are written as numbers with no dividers, eg.
05/10/07 is written as 51007. Is there any easy way to just format this as a
date? Or will i just have to use a formula to convert it?

2)The second column of dates look like they're in date format - eg.
26/10/2007 but dont seem to be in a numerical date format as i am unable to
change the format of how the date is displayed or change it to a number??

any ideas would be much appreciated

thankyou

patrick

"Patrick Bateman" wrote in
message ...
Hi all,

I have two problems with the format of two different columns of dates i
have
coppied onto an excel sheet. I need to be able to have the dates in a
standard date format in order to be able to work with them. The problems
are
as follows:

1)The first column of dates are written as numbers with no dividers, eg.
05/10/07 is written as 51007. Is there any easy way to just format this as
a
date? Or will i just have to use a formula to convert it?

2)The second column of dates look like they're in date format - eg.
26/10/2007 but dont seem to be in a numerical date format as i am unable
to
change the format of how the date is displayed or change it to a number??

any ideas would be much appreciated

thankyou

patrick


The numbers such as 51007 are just numbers, not dates, so you will have to
use a formula to convert them.

The ones in your second question are probably text. In this case, use
Data Text to Columns (and just go with the defaults)
to convert them.

First, the easy one, the second: To translate these text values dates
INSIDE the cell:
Copy a black cell. Select the "date" data, EditPaste Special... and
choose Add. Subsequently format to some Date format.
To perform the same task in a different column
=DATEVALUE(A1)

For the first task the problem is with a number like 11107. Is it Jan
11 or Nov 1? If you are guaranteed that month will always be two
digits (e.g. April 1 2006 will appear as 10406) then you can use:

=DATE(2000+RIGHT(A1,2),--MID(A1,LEN(A1)-3,2),--LEFT(A1,LEN(A1)-4))

Does this help?
Kostis Vezerides

On Nov 23, 11:09 am, Patrick Bateman
wrote:
Hi all,

I have two problems with the format of two different columns of dates i have
coppied onto an excel sheet. I need to be able to have the dates in a
standard date format in order to be able to work with them. The problems are
as follows:

1)The first column of dates are written as numbers with no dividers, eg.
05/10/07 is written as 51007. Is there any easy way to just format this as a
date? Or will i just have to use a formula to convert it?

2)The second column of dates look like they're in date format - eg.
26/10/2007 but dont seem to be in a numerical date format as i am unable to
change the format of how the date is displayed or change it to a number??

any ideas would be much appreciated

thankyou

patrick