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

 

[DomThePom]

Yes - change regional settings to UK or somewhere non US

 

[Sagu]

Yes - change regional settings to UK or somewhere non US












- Show quoted text -

Thanks for your help, Dave and DomThePom. It fixed the problem

Thanks,
Sagar