Numbers to dates

[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

[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

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

[steviec334567]

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?

[Ron Rosenfeld]

On Thu, 25 May 2006 04:01:42 -0500, steviec334567
<[email protected]> 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

[[email protected]]

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]

[[email protected]]

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

[Ron Rosenfeld]

On 25 May 2006 06:48:20 -0700, "[email protected]"
<[email protected]> 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