Excel date issues

Question

Level 1

15 points

Excel date issues

Hi all,

I recently bought a MacBook running Leopard. I had been using a PowerBook with Panther for about 3 years.

Currently I am having trouble with dates inside Excel 2004. I am Mexican and I input dates in the format dd/mm/yy. I have customized this under International in System Prefs. This used to be a fix so that any other apps (like Excel) will recognize this as the default format for date input.

However this is not the case, Excel under Leopard doesn't recognize 16/11/07 as a valid input for November 16 2007. This worked perfectly under Panther. But does not work anymore.

Any help will be greatly appreciated.

MacBook Pro, Mac OS X (10.5.1)

Posted on Nov 20, 2007 3:59 PM

Reply

Answer 1

HK

Level 2

286 points

Nov 21, 2007 9:27 AM in response to Juan Mier

I understand, totally different indeed- I must have read too quickly and from what I grasped, it sounded like the issue I had had with the 1904 date system 🙂

Sadly, I do not know how to fix this though. Perhaps you could drop Apple a line with a link to this thread? Apple Feedback Page

Reply

Answer 2

Ashka

Level 4

3,891 points

Nov 20, 2007 4:21 PM in response to Juan Mier

All you can really do is reset all the dates in System Prefs > Date & Time & Time Zone
Then check System Prefs > International > Formats.
If you click on Customise by the dates the next window has a drop down box at the top with Full / Short / Med & Long. I'd check that all of them are correct.
Does excel have a default date setting ? Otherwise hit the Microsoft forums for a fix 🙂
Best

Reply

Answer 3

HK

Level 2

286 points

Nov 21, 2007 9:00 AM in response to Juan Mier

No, no this issue has to do with the difference between Excel for Windows and Excel for Mac OS. 1904 date system is checked by default in one but not the other. So if you don't know about this and create Excel files containing dates in one and open them in the other, your dates are off.

Very annoying: Stupid Microcrap.

Solution:
In Excel, go to Tools > Options (under the Calculation tab on Excel WIndows) and make sure the "1904 date system" is checked on both Excel Windows and Excel Mac.

Reply

Answer 4

Juan Mier Author

Level 1

15 points

Nov 21, 2007 9:12 AM in response to HK

No, no, this has nothing to do with 1900 date system vs 1904 date system. I am aware of this issue as I have been using Excel 2004 for 3 years.

What is going on is the way you input a date into a cell. If I type 21/11/07 (dd/mm/yy spanish format) it should be recognized as a date but it is only recognized as text. If I input 11/21/07 (US format mm/dd/yy) it is now taken as a date.

This used to be fixed in Panther by going into International in System Prefs and customizing date formats as dd/mm/yy or any other you like. That way Excel knows which is your preferred format for inputing dates. Later you can change it inside Excel, but the issue here is how you type into the cell which for speed issues it is a big thing as each part of the world is used to thinking and typing dates a certain way.

I have done this in Leopard but Excel refuses to work correctly. I will try to reinstall Office see what goes.

Anyways it is really annoying to be using a "state of the art" OS and simple issues like this take away do not work.

Reply

Answer 5

Juan Mier Author

Level 1

15 points

Nov 21, 2007 3:13 PM in response to HK

This issue is getting weirder:

If I input a date using letter for the month: 21-nov-07, Then it is taken as a date! However, 21/11/07 is not!!

I have also tried to set yy/mm/dd as an format in International, but this is not recognized by Excel. I do not know if its Apple's or MS's fault, but this worked perfectly under Panther (I never used Tiger, but I guess it worked fine there too).

I will reinstall office and let you know if it fixes something. (I guess it wont worked as I installed Office after the Leopard update

Reply

Answer 6

Juan Mier Author

Level 1

15 points

Nov 21, 2007 7:29 AM in response to Ashka

Time Zone and Custom formats for dates (short, med, long, full) are correctly set for day/month/year.

I have posted this on Microsoft forums but don't have a reply.

What else can I try

Reply