excel 2000 help

[Guest]

how to I change the dates from american to australian
2/1/07 is comes up as 1-Feb-07 but if I want to change it goes Feb-1-07 if I
open up Format/Cells and change it. I want it to come up as 2-Jan-07 so how
do do this how do I change it.

 

[Guest]

format cell : custom

DD MMM YY

 

[David Biddulph]

I don't think you read the OP's question, Rich. If when he formats the cell
it comes up as 1-Feb-07 then the contents of the cell must be 39114, while
he intended 39084, so your custom format will not give the result he wants.
This may have occurred because of the Windows Regional settings.
The easiest cure for the existing data is Data/ Text to Columns/ Delimited/
through to last stage:
Date format: DMY.

 

[Guest]

dave ,,

but since his date is already showing as a date format 1-Feb-07 all he
needs to do is alter the custom settings to show the date in the order he
requries,,

simply put both ways will work ,, depending on what the problem is, the way
you would fix the problem and i would are both effective ,,

 

[David Biddulph]

Obviously one or other of us is confused, Rich. My understanding was that
custom format can alter the way the date is displayed, but not alter the
date itself. If he is seeing 1-Feb-07 then your custom format of DD MMM YY
will change the display to 01 Feb 2007. The *format* won't change his date
to 2-Jan-07. We'll wait to see whether your suggestion works for the OP,
but I would doubt it.

 

[Gord Dibben]

I'm with you David.

Change the Regional settings for future and clean up existing dates with your
Text to Columns method.


Gord Dibben MS Excel MVP

 

[Guest]

Thank You David Biddulph, Rich Mcc I had already tried format cell : custom

 

[Guest]

not a probem, confused is a permanant state of mind so no real suprise their