Date Questions

[Patrick Bateman]

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

 

[Stephen]

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.

 

[vezerid]

First, the easy one, the second: To translate these text values dates
INSIDE the cell:
Copy a black cell. Select the "date" data, Edit>Paste 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