How to set the default date format to D/M/Y?

[Drdul]

It looks like Excel is hard-wired to accept dates input as M/D/Y, which is the American format. Here in Canada (as in the rest of the world), we write D/M/Y. I can't find anything to change this in Preferences, and Excel is apparently not picking up the system date settings from OS X.

This is *not* a cell formatting problem. I have the cells formatted to display the date as DD-MMM-YY, and dates are displaying correctly (once I figure out how to enter them correctly!). Even though the dates display correctly in the spreadsheet, they display in the American M/D/Y format in the formula bar. I am wasting a lot of time with this, as I keep entering dates wrong (40+ years of doing it one way is difficult to unlearn!).

Is there some secret setting that I am missing that would allow me to set Excel to default to accepting dates in the D/M/Y format, and displaying them that way in the formula bar?

 

[Martin Seiner]

You are still lucky. My dates in Excel 2008 are all displayed with either "YY" or "YYYY" instead of the actual number for that year...

 

[Todd Aton]

When you go to your International system settings panel, what do you have
selected in the Region list in the Formats tab? "Canada"? If so, does it
have a warning underneath that says "This region can only be used by Unicode
applications. Worldscript applications will use the last compatible
region."?

Todd Aton
Microsoft Corporation

 

[Drdul]

Hi Todd:

The message reads: "This region is incompatible with some older applications. Such applications will use the most recent compatible region." If this is the cause of the problem, then that's surprising, as I would have expected that Excel 2008 would not be considered an "older application" (it's only two months old!), and would be able to pick up the info from the system prefs.

Cheers!

 

[warnek]

Hi Todd:

The message reads: "This region is incompatible with some older applications. Such applications will use the most recent compatible region." If this is the cause of the problem, then that's surprising, as I would have expectedthat Excel 2008 would not be considered an "older application" (it's only two months old!), and would be able to pick up the info from the system prefs..

Cheers!

I have the same problem, but my region is Ireland and there is no
message/incompatibility. I have my system prefs set for Ireland (dd/mm/
yyyy), but Excel displays all dates in the American format.

 

[H. LIEW]

I have mine set to Australia with no warning whatsoever.

Excel 2008 still defaults to mm/dd/yy format even though the global system
setting is set to Australia with the following formats:-

Short --> d/mm/yy
Medium --> dd/mm/yyyy
Long --> d mmm yyyy
Full --> dddd, d mmm yyyy

Its real annoying for something so trivial.

 

[Laroche J]

Case 1: If my OS X short date is YYYY-MM-DD and I enter 14-12-10 in a cell,
Excel shows it in the formula bar as 2014-12-10 (where 12 is December).
Case 2: If my OS X short date is MM-YYYY-DD and I enter 14-12-10 in a cell,
Excel shows it in the formula bar as 14-12-2010 (where 12 is December).
Case 3: If my OS X short date is DD-MM-YYYY or DD-MM-YY and I enter 14-12-10
in a cell, Excel shows it in the formula bar as 14-12-2010 (where 12 is
December).
Case 4: If my OS X short date is MM-DD-YYYY and I enter 12-14-10 in a cell,
Excel shows it in the formula bar as 12-14-2010 (where 12 is December).
Case 5: If my OS X short date is YYYY-DD-MM and I enter 14-12-10 in a cell,
Excel shows it in the formula bar as 14-12-2010 (where 12 is December).
Case 6: If my OS X short date is DD-YYYY-MM and I enter 14-12-10 in a cell,
Excel shows it in the formula bar as 14-12-2010 (where 12 is December).
I did quit Excel between each modification of OS X date format, and
sometimes I also closed my session (but it didn't make any difference).

Another thing I noticed, is that whether I choose YY or YYYY in OS X format,
Excel always displays the year with four digits in the formula bar. I wonder
if it could be something set the very first time Excel ran, based on what my
OS X setting was at the time.

Conclusions: in some cases (1, 3 and 4), my Excel follows the OS X short
date order when entering a date; when the OS X order doesn't make sense to
Excel (cases 2, 5 and 6), Excel uses DD-MM-YYYY by default. I wasn't able to
get M/D/Y as you do unless I specifically asked for it. Maybe you could use
a personalized format instead of the standard English Canadian one, if
that's what you do.

I use Excel v.X in English, bought in Canada.

JL
Mac OS X 10.4.11, Office v.X 10.1.9


H. LIEW wrote on 2008-04-01 04:44:

 

[Xavier Llobet]

Let me join in, as I have seen an answer from somebody at Microsoft.

MacOSX 10.5.2

Custom format date settings:

International Preference, Date Format, Short Form: 13-05-2008
(dd-mm-yyyy)


Excel 2008 for Mac, 12.0.1

New document, format cells of a column as Number, Custom, dd-mm-yyyy

--- If I type <ctrl>-; (today's date), I get 04/13/2008 in the formula
bar, and 13-04-2008 in the cell.

First observation, in previous versions of Excel (X, for example) the
formula shows as 13-04-2008


--- In the following cells of the same column, I type
13-4
13-04
13-4-8
13-04-08
13-04-2008

All these give, under Excel X and 10.3.9, today's *date*. Under Excel
2008 and Mac OSX 10.5.2 I get only text.

To obtain a date I have to type 4/13, disagreeing with the Preferences
setup. It appears as if Excel ignores completely the Preferences.

Is this a Microsoft bug, or am I doing something wrong?

_x.

 

[selsrog]

Hi,

I'm living in Belgium and have the same problem.
Can Microsoft please provide any feedback whether this is a bug or a setting?

Roger

 

[Phillip Jones]

check Date and time in system preferences and then International in
System preferences.

Usually applications abide by what set in these Control Panels.

If not click on the letter for the column you want to switch so the
entire column is selected. then go to Format menu and choose cells when
that menu open choose date the select the desired format. Then close and
type a number and see What happens.

Hi,

I'm living in Belgium and have the same problem.
Can Microsoft please provide any feedback whether this is a bug or a setting?

Roger

--
------------------------------------------------------------------------
Phillip M. Jones, CET |LIFE MEMBER: VPEA ETA-I, NESDA, ISCET, Sterling
616 Liberty Street |Who's Who. PHONE:276-632-5045, FAX:276-632-0868
Martinsville Va 24112 |[email protected], ICQ11269732, AIM pjonescet
------------------------------------------------------------------------

If it's "fixed", don't "break it"!

mailto[email protected]

<http://www.kimbanet.com/~pjones/default.htm>
<http://www.kimbanet.com/~pjones/90th_Birthday/index.htm>
<http://www.kimbanet.com/~pjones/Fulcher/default.html>
<http://www.kimbanet.com/~pjones/Harris/default.htm>
<http://www.kimbanet.com/~pjones/Jones/default.htm>

<http://www.vpea.org>

 

[Xavier Llobet]

check Date and time in system preferences and then International in
System preferences.

Usually applications abide by what set in these Control Panels.

If not click on the letter for the column you want to switch so the
entire column is selected. then go to Format menu and choose cells when
that menu open choose date the select the desired format. Then close and
type a number and see What happens.

It's not so simple...

Let me repeat/rephrase my message from 13 April.

*** Environment:

MacOSX 10.5.2

System Prefrences, International, Date Format, Short Form: 28-05-2008
(dd-mm-yyyy)

*** Application:

Excel 2008 for Mac, 12.0.1

New document, format all cells of a column as Number, Custom, dd-mm-yyyy

--- If I type <ctrl>-; (today's date), I get 04/28/2008 in the formula
bar, and 28-04-2008 in the cell. The display is correct, and the cell
contains a date.

First observation, in previous versions of Excel (X, for example) the
formula shows as 28-04-2008


--- In the following cells of the same column, I type
28-4
28-04
28-4-8
28-04-08
28-04-2008

All these give, under Excel X and 10.3.9, today's *date*. Under Excel
2008 and Mac OSX 10.5.2 I get only *text*.

To obtain a date I have to type 4/13, disagreeing with the Preferences
setup. It appears as if Excel completely ignores the International
Preferences settings.

I would like to obtain dates when I type 28-4, not character strings.

Is this a Microsoft bug, or am I doing something wrong?

_x.

 

[Phillip Jones]

Try this sound similar to problem some other have had. go into
preferences and try setting the date format to 1900 (windows format).

try that. If that doesn't works someone else might be along to help.

It's not so simple...

Let me repeat/rephrase my message from 13 April.

*** Environment:

MacOSX 10.5.2

System Prefrences, International, Date Format, Short Form: 28-05-2008
(dd-mm-yyyy)

*** Application:

Excel 2008 for Mac, 12.0.1

New document, format all cells of a column as Number, Custom, dd-mm-yyyy

--- If I type <ctrl>-; (today's date), I get 04/28/2008 in the formula
bar, and 28-04-2008 in the cell. The display is correct, and the cell
contains a date.

First observation, in previous versions of Excel (X, for example) the
formula shows as 28-04-2008


--- In the following cells of the same column, I type
28-4
28-04
28-4-8
28-04-08
28-04-2008

All these give, under Excel X and 10.3.9, today's *date*. Under Excel
2008 and Mac OSX 10.5.2 I get only *text*.

To obtain a date I have to type 4/13, disagreeing with the Preferences
setup. It appears as if Excel completely ignores the International
Preferences settings.

I would like to obtain dates when I type 28-4, not character strings.

Is this a Microsoft bug, or am I doing something wrong?

_x.

--
------------------------------------------------------------------------
Phillip M. Jones, CET |LIFE MEMBER: VPEA ETA-I, NESDA, ISCET, Sterling
616 Liberty Street |Who's Who. PHONE:276-632-5045, FAX:276-632-0868
Martinsville Va 24112 |[email protected], ICQ11269732, AIM pjonescet
------------------------------------------------------------------------

If it's "fixed", don't "break it"!

mailto[email protected]

<http://www.kimbanet.com/~pjones/default.htm>
<http://www.kimbanet.com/~pjones/90th_Birthday/index.htm>
<http://www.kimbanet.com/~pjones/Fulcher/default.html>
<http://www.kimbanet.com/~pjones/Harris/default.htm>
<http://www.kimbanet.com/~pjones/Jones/default.htm>

<http://www.vpea.org>

 

[Xavier Llobet]

Try this sound similar to problem some other have had. go into
preferences and try setting the date format to 1900 (windows format).

try that. If that doesn't works someone else might be along to help.

Nope. It shouldn't be related. And it is not (I tried, just in case).

The problem remains.

_x.

 

[selsrog]

Hi,

solved my problem in Belgium (d/mm/yy) using the "Show all regions" and selecting my county.

At that moment also the message 'this is an older region which is not supported by older programs' disappeared from the preferences applet.

Cheers,
Roger