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


--
steviec334567
------------------------------------------------------------------------
steviec334567's Profile: http://www.excelforum.com/member.php...o&userid=33852
View this thread: http://www.excelforum.com/showthread...hreadid=545415

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

"steviec334567" wrote:


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


--
steviec334567
------------------------------------------------------------------------
steviec334567's Profile: http://www.excelforum.com/member.php...o&userid=33852
View this thread: http://www.excelforum.com/showthread...hreadid=545415

On Thu, 25 May 2006 04:01:42 -0500, steviec334567
wrote:


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

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

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

Any ideas?


--
steviec334567
------------------------------------------------------------------------
steviec334567's Profile: http://www.excelforum.com/member.php...o&userid=33852
View this thread: http://www.excelforum.com/showthread...hreadid=545415

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

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!!!

On 25 May 2006 06:48:20 -0700, "
wrote:

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!!!

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