In short: when you type 12/14/2009 you are getting text not a date!
Firstly, I am also in Canada and the official format for dates is dd/mm/yyyy
regardless of language. Check with a gov of Canada webpage. Some Canadian
institutions use the USA format because that is where the software comes
from. I have Regional Setting as dd/mm/yyyy.
However, it seems you want to use USA format. The trouble is that Excel
expects you to be typing a date that is in accord with the Regional
Settings. So it I type 1/2/2009, Excel thinks I am typing 1-Feb-2009
regardless of the format of the cell. I formatted column A with USA date
format mm/dd/yyyy and in A1 I typed 1/2/2009, the cell displayed 2/1/2009..
In B1 I entered =A1 and I formatted column B with custom format mmm to show
just the month abbreviation. So B1 displays Feb because, according to
Regional Setting I typed a February date. And it does not matter if I use
1/2/2009 or 01/02/2009.
Now if I type 12/14/2009 it may look as if the cell has a USA date but noit
really has only text! the B column shows 12/14/2009, not Dec.
Try your worksheet: in A1 enter a date with like 6/6/2009 and in B1 enter
=A1+1 and you should see 7/6/2009 if you have Canadian format or 6/7/2009 if
you have USA format for that cell.
Now type 12/14/2009 and B1 will just show a #VALUE! error
What a shame we cannot all use the logical format of yyyy/mm/dd. In all
other measurements we go from big to small ( yards/feet/inches)
best wishes
If I understand what you're saying, the Regional Settings determines
how the date is processed at the data entry point. Meaning that if the
Regional Settings are set to mm/dd/yy then Windows assumes that the
first two digits corresponds to the month and so on. The date format
settings in Excel assumes the date entered is valid and simply
shuffles the digits around to match the format selected.
Now, if that's the case, doesn't Excel do any data validation as the
date is entered? I ask this because I just set my Regional Settings
date format to mm/dd/yy and then formatted my date column in my
spreadsheet to match it using custom date. So both date format are
set to mm/dd/yy. So why is it that I can still enter 14/02/09 when
both Windows and Excel should be expecting a month as the first two
digits, therefore, a value of between 1 and 12. Is there no way to
validate the date that's been entered?
