I have a small problem with my wife’s installation of Excel. When I enter a date in the rest-of-the-world format (d/m/y) it displays it incorrectly in the US format (m/d/y.)
Example:
I enter “8/4/7” and it displays “4-Aug-2007” rather than “8-Apr-2007.”
The cell is formatted as date - dd-mmm-yyyy and the locale is set to English (Australia.) The regional settings in the control panel are also set to Australia.
I don’t have this problem with Excel on my laptop which is set up the same way as far as I can tell.
Sure, I can tell you how not to use proper dates and use the incorrect kind they make me use here in Australia.
I have fixed this problem and found that it lies not within Excel, but within Windows.
Go Start Menu>Control Panel>Regional and Language Settings
Under the Regional Options tab, use the dropdown menu to change to Australia.
If you are in the spreadsheet, you will need to close and reopen it to make this work.
This has always broken not just Excel, but all MS Office products for me so I can use dates the same way everybody else here does.
ETA: You can also look in Excel to do something similar - right click a cell, chose Format, and look under “Custom” instead of “Date” and it should give you a backwards date option.
I mean “incorrect” as in, not how I like. I have no opinion on the relative merits of the various date formats ;).
Unfortunately the regional settings in XP are already set to English (Australia.) So that is not the problem. Also, as far as I can tell the date and custom formats allow you to change how the dates are displayed but not how the program parses inputs. In other words, the problem is not that the dates are displayed in the wrong format, but that I must input them in the wrong format to get the date I want. This is a sheet that others will be using so it needs to be intuitive.
Hmmm…got me then, that’s what I’ve had to do to all the XP installs at work lately (and there’s been lots) and its worked fine.
(I only grouse about dates cause I still do mine American style when I’m tired and forget, and my Aussie office mates make fun of me. It’s annoying. )
What version of Office are you running?
In MSOffice 2003, I have a program called MS Office 2003 Language Settings. Its not obvious, I found it not on my Start Menu but in my program files folder. There’s an option there that sets the “behaviour” of MSOffice programs by language - mine was set to US English but I changed it to Aussie English.
Maybe you have something similar? I have the full install of 2003, so…that should work, as Excel is behaving as though its installed on an American machine. I do use Excel extensively for work in a financial sense, but the language fix on Control Panel has always worked for me. But this might work for you, if you have something like it.
Other than that, I got nothing.
ETA…nothing really, I had a thought but it’s gone now…tired can’t think.
)