 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
Mac Forum / Applications / Excel / May 2008How to set the default date format to D/M/Y?
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? 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... 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 On 3/2/08 9:48 PM, in article ee8f798.-1@webcrossing.caR9absDaxw, > 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. [quoted text clipped - 11 lines] > Excel to default to accepting dates in the D/M/Y format, and displaying them > that way in the formula bar? 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! On Mar 14, 3:13 pm, Dr...@officeformac.com wrote: > 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! 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. 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. > 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 [quoted text clipped - 22 lines] > > Excel to default to accepting dates in the D/M/Y format, and displaying them > > that way in the formula bar? 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: > I have mine set to Australia with no warning whatsoever. > [quoted text clipped - 39 lines] >>> Excel to default to accepting dates in the D/M/Y format, and displaying them >>> that way in the formula bar? 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. 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 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  Signature------------------------------------------------------------------------ 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 |pjones@kimbanet.com, ICQ11269732, AIM pjonescet ------------------------------------------------------------------------ If it's "fixed", don't "break it"! mailto:pjones@kimbanet.com <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> > check Date and time in system preferences and then International in > System preferences. [quoted text clipped - 5 lines] > 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. 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. >> check Date and time in system preferences and then International in >> System preferences. [quoted text clipped - 49 lines] > > _x. Signature------------------------------------------------------------------------ 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 |pjones@kimbanet.com, ICQ11269732, AIM pjonescet ------------------------------------------------------------------------ If it's "fixed", don't "break it"! mailto:pjones@kimbanet.com <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> > 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. 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 |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.