Numbers to dates

S

steviec334567

Hi,

I am new to all this excel help forum.
Can you help me i have a column that is formatted as numbers but is
actually dates so 1st Jauary 2006 is stored as 112006, and the 24th
April 2004 is stored as 2442004 so if i sort on this column the 1st
January 2006 comes first but in reality the 24th Spril 2004 comes
first.

How do i get excel to store it as a date in the formatdd/mm/yyyy.

Please help
 
T

Tom

Hi Stevie,
You can change the format of cells by selecting FORMAT > CELLS, then click
on DATE and select whatever format you would like them to have.

Regards,
Tom
 
R

Ron Rosenfeld

Hi,

I am new to all this excel help forum.
Can you help me i have a column that is formatted as numbers but is
actually dates so 1st Jauary 2006 is stored as 112006, and the 24th
April 2004 is stored as 2442004 so if i sort on this column the 1st
January 2006 comes first but in reality the 24th Spril 2004 comes
first.

How do i get excel to store it as a date in the formatdd/mm/yyyy.

Please help
Click to expand...

In order to reliably accomplish this, an unambiguous method of date
representation is required.

If I understand what you have written, your date representation is a string of
digits in the form dmy (without leading 0's at the d or m position)

That being the case,

1122004 could be either 11 Feb 2004 or 1 Dec 2004.

And there are many other ambiguous date representations in your system.

You will need to resolve the ambiguities before an appropriate solution can be
offered.

For example, a format of ddmmyyyy would be unambiguous.
11022004 --> 11 Feb 2004
01122004 --> 1 Dec 2004

and could be converted using the formula:

=DATE(MOD(A1,10^4),MOD(INT(A1/10^4),100),INT(A1/10^6))


--ron
 
S

steviec334567

Yeah i have tried that put it puts the year format into a serial numbe
like 3456 or something so i must need a formula to do something li
this.

Any ideas
 
A

aidan.heritage

Tom, I think you've missed the actual question here, which is that the
dates are entered incorrectly onto the sheet - you could use a macro or
formulas to get the relevent parts of the date and convert them INTO a
date - do this with formulas in a blank column, then copy the values
over the originals - if not clear, you can get me at
[email protected]
 
A

aidan.heritage

I've seen the data, it's has no leading zeros, so we have 112005 for
January 1st 2005 and 1112005 COULD be 11 January or 1 November!!!
 
R

Ron Rosenfeld

I've seen the data, it's has no leading zeros, so we have 112005 for
January 1st 2005 and 1112005 COULD be 11 January or 1 November!!!
Click to expand...

Well, stevie is going to have to decide what to do with the ambiguities.
--ron
 
Top