Date Format Problem.


L

lukus2005

Being in Canada, I have to deal with two different date format...
English (mm/dd/yy) and French (dd/mm/yy).

Vista's date format is set as dd/mm/yyyy for short dates and mmmm/dd/
yy for long date format.

I've set my date column in my spreadsheet using a date format of mm/dd/
yy.

Problem is... it gets the month and day mixed up if both are 12 or
less. Meaning if I enter 02/14/09... it goes in correctly however if
I enter 02/12/09... it will show up as 12/02/09. All the cells have
the same date format.

How do I force it to use the first digit(s) as the month, whether I
enter 2/ or 02/ ?
 
Ad

Advertisements

B

Bernard Liengme

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 no it
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
 
Ad

Advertisements

L

Lukus

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?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top