As Bob says, Excel picks it up from the system each time it launches.
Please do what he said first, otherwise none of this stuff will work:
Check in System Preferences>Language & Text>Formats that your Region is
correct, and everything else should flow from there.
Now, there are some things you need to know about dates in Excel when you're
working: I can't believe how inadequate Excel's help is on this very
important subject
1) Excel does not store dates as years, months, and days. It stores that
as large decimal numbers: "The number of days since the epoch, plus the
fraction of 24 hours to the current time, expressed as a decimal number. So
15th December 2009 at 13:39 is stored as 38700.56875
2) Before you can do anything with Excel dates, you must first ensure that
they have been stored as DATES and not as text or numbers. In an
otherwise-unformatted cell, if Excel has stored it as a date, the display
will justify to align with the right margin of the cell. If it is stored as
Text, the display will appear against the left margin of the cell.
3) To force it to get British dates correct, type 3 letters for the month
and four digits for the year: 15 Dec 2009 will always be interpreted
correctly if your System Preferences are set to British dates, as will Dec
15 2009 if you are set to American.
4) Once a Workbook is created, the Epoch and date format are stored in it.
The "Epoch" is the date of "Day 0" from which Excel counts dates. To
understand it, read here:
http://support.microsoft.com/kb/214330
5) The Cell Format controls only how the content is DISPLAYED. It has no
effect at all on what Excel is actually storing in the cell. When you copy
and paste, Excel will paste either the value (a number) or text, depending
on what you paste into. If you paste dates from Excel to Excel, you paste a
NUMBER, but you do not paste the Epoch. This can lead to dates being out by
four years if you paste from a sheet created on the Mac to a sheet created
in Windows, even if you do it on a Mac. If you paste into most other
applications, you paste TEXT, so what you see Excel displaying is what you
paste.
You need to find out whether your "other application" is expecting dates as
text, or dates as numbers.
In Mac Excel, the 1904 date system is enabled by default. If you are
working with "other software" that was not designed for the Mac, it is best
to set the Workbook into the 1900 date system.
You will know immediately if the setting is wrong: dates will be out by four
years.
Once you have created a Workbook, you cannot change its date system.
As a "Sanity Check" select some cells that are displaying dates, and change
the Format to "General". You should see something like 38700.56875 in each
cell. If you still see "15/12/09", the cell is being stored as Text.
To convert text dates to real dates, create a column alongside them, then
enter the formula =DATEVALUE(E10) (where the date text is in cell E10).
To convert real dates to text, use the formula =TEXT(E7, "d/m/yyyy") where
E7 contains the date number, and the bit in quotes controls the format of
the text string you want to store.
Note that the Region settings specify not only the sequence of the date
parts, but also the separator used. You will get mixed results if you try
to use a separator other than "/" in British date formats.
Hope this helps
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel For
the life of me, cannot get Excel to accept British date formats...
Have changed my international preferences on the mac, and cannot find where to
work (even on just converting the american format MM/DD/YYYY to DD/MM/YYYY
.... need it to work on fixed departures to enter into a british designed
software...
manual entries would be too tiring! sigh!
--
The email below is my business email -- Please do not email me about forum
matters unless I ask you to; or unless you intend to pay!
John McGhie, Microsoft MVP (Word, Mac Word), Consultant Technical Writer,
McGhie Information Engineering Pty Ltd
Sydney, Australia. | Ph: +61 (0)4 1209 1410
+61 4 1209 1410, mailto:
[email protected]