Date format in Excel 2011 and Lion

[MCross]

Hi,

Has anyone else had a problem with the date format in excel 2011? I have a budgeting/expense tracking spreadsheet that I have been using for years in Office for macs 2003. I generally sort this spreadsheet on the date column. However, when I upgraded to Lion (which forced an upgrade to Office 2011) the date column is always treated as text (the sort is alpha-numeric vs m/d/y). I have the most up-to-date OS and office software and am running this on a 2010 MBR and a 2 week old MB Air.

I have 6 years of data (thousands of lines) in this spreadsheet so creating a new spreadsheet is not an option I want to explore. I've tried inserting a column and setting a new date format for it has not worked. As soon as I type Dec 18, 2011 it stays as text. Any thoughts?

 

[MisterMe]

Two things:

1. There is no such suite as Office for macs 2003. There is Office 2001 (the last MacOS 9-compatible version of Office), Office v.X (the first MacOS X-compatible version of Office), Office 2004, Office 2008, and Office 2011. Office 2003 is Windows-exclusive.
2. The data format of cells in a spreadsheet are determined by the spreadsheet application, not the OS. It is not at all clear that your spreadsheets ever had your date columns designated as date data. In this case, your columns would have always been plain text.

 

[MCross]

Thanks. You are correct, I was confused: my office computer has MS Office 2003 (for windows) and my home computers (Macs) had Office:Mac, student and teacher edition 2004. I understand that the OS does not dictate the date format in a spreadsheet...that is the spreadsheet application itself.

The data in the column has been formatted for dates and I have been sorting it by date since 2006. I can assure you that it is formatted for dates and not text. Otherwise, it would have sorted alphanumerically (like text) not chronologically by year, month and day.

It is only in the past few weeks, since I bought the new MacBook Air with Lion and the latest version of MS Office, has that same spreadsheet automatically changed the information I enter as a date (Dec 17, 2011) into text. I have formatted the cells as date, formatted the column as date, added a new column beside it to replace it, removed all formats from the spreadsheet and reformatted the column as dates...nothing allows the information to be read and manipulated as dates. I even opened the spreadsheet on my office computer (windows) where it identified the format of the column as text...but there I was able to reformat it as date and all was well...until I opened it on my home computer and it was back to a text field.

I even created a new spreadsheet with the same categories and entered similar information (nothing was cut and paste) and still the dates are treated as text. On any other spreadsheet I have worked on, I should be able to enter information in any form of date (dec 17, 2011; 12/17/2011; 17 december 2011; etc) and once format by date (17 Dec 2011) they should all, automatically take that format. It never does. It never sorts as dates.

Any help would be appreciated.

Thanks

 

[MisterMe]

...

Any help would be appreciated.

Thanks

There are times when we forget just how much we love Microsoft and the reasons why it stirs our hearts so. I just conducted a test of Excel 2004 and Excel 2011. Using Excel 2004, I created a spreadsheet with two columns--Column 1 had dates; Column 2 had regular floating-point numbers. After saving the spreadsheet, I used Excel 2011 to open it from another Mac on my network. Excel 2011 sees the date data as custom-formatted text. It gets the format of the text correct which is "x/xx/xxxx" in the case of my test data. It just does not see the data as dates. This being the case, the only thing that you can do is to apply the appropriate date format to your now custom-formatted text data that you intend to be dates.

 

[MCross]

Thanks. At least I know I'm not crazy! I discovered tonight that if I enter the date with the day or year first, it will be read as a date...just can't enter the month first. I will have to adapt to the system...faint hope that Microsoft will fix the bug.

Cheers

 

[Barry1949]

Changing date format for Excel:mac 2011

As several have noted, using Excel:mac 2011 with Mountain Lion (I have 10.9.5) results in a non-date form. I couldn't change an older date format 2/22/2014 (for example) to 2014-02-22 which was necessary. This meant I could not sort the dates properly as they were being read as alpha-numeric rather than numeric. I opened up a google docs spreadsheet, copied the column of dates, and reformatted. I then copied these back into my excel spread sheet and was able to sort everything by date.