Excel 2003 Dates - Strange behaviour

[Sagu]

Hello all,

I am not sure, if any one else has faced the same problem. Please
advise.

Issue:
I have formatted cell "D1" as Date with Category as "14/03/01" and
locale as " English(Australia)"
I have a few calculations based on the date entered in cell "D1"

When I enter the date as "23/05/05" , the numerical calculations throw
an "#VALUE!" error.
But, when I enter the date as "05/23/05", the cell "D1" reverts it to
"23/05/05" and the numerical calculations give me the correct answer.

My question is, when the cell is formatted in the "dd/mm/yy" format
and if i enter the date in the same format, why do the calculations
fail? When i enter the dates in the "mm/dd/yy" format, the cell format
in D1, converts them into "dd/mm/yy" format and the calculations run
through without any issue.

My machine's regional settings is set to location : United States.
Could this be the source of the problem?

Please advise.

Thanks,
Sagar

 

[Dave Peterson]

The order of the mdy that you enter has to match the order of your short date
format in windows control panel (regional settings applet).

Excel's number format just tells excel how to display that value.