Excel 2011 Date Format issue

[DianeK]

Hello. I am new to this forum.
Yesterday I upgraded from Snow Leopard to Mountain Lion on my iMac. I have Office 2011. All my excel spreadsheets have a date column that were formatted to display as: dd-mmm-yy. So today's date should display as 4-Jun-13 if I type in June 4, 2013. However, since yesterday's upgrade, when I typed in June 3, 2013 it remain as such and will not convert to my date formatting. See first image on left.

I have done the following:
1. checked that the format is still there - yes it is. And if I use autofill from the last cell that was entered prior to the ML upgrade, the dates autofill in the correct format. See second image on left

2. changed my region (Canada) to a custom one where I changed the short date format to dd-mmm-yy and changed the long date format to how I normally type dates. See image on right.

Is this a known conflict between ML and Office for Mac?

Any resolution possible? I use Excel a lot and this is going to be a real pain if I can't fix it.

Thank you
Diane

 

[MisterMe]

As long as you enter the data in valid date format, Excel will recognize it as a date and perform calculations correctly. Have you tried to restore your preferred format using the Format>Cells... menu item?

 

[DianeK]

As long as you enter the data in valid date format, Excel will recognize it as a date and perform calculations correctly. Have you tried to restore your preferred format using the Format>Cells... menu item?

Thanks for your suggestions however formatting through the menu is no different. Excel simply will not recognize the input characters of June 4, 2013 as a date. It will recognize 4 June, 2013 input and display it as 4-Jun-13. This is definitely a result of the upgrade to ML because before the ML install, I could still input my dates my usual way (e.g. last entry in my screenshot was input by typing May 31, 2013 and it properly displayed 31-May-13. Do you know if ML changed the default date options for some regions (e.g. Canada) over what SL had? I now don't have access to a SL computer to see what the Canada defaults were.

Also Excel does not recognize my "Custom" region and I can't find a way to add that Custom region to Excel. Do you know of a way? When I go to format cells - date in Excel I have to choose a language at the bottom of the window and "Custom" does not appear there so once I choose English (Canada) I have to type 4 June, 2013 and not June 4, 2013 for it to be recognized.

Sorry, I know I am rambling on here and you are probably thinking "just change your input technique to what Excel wants and be done with it". But when you have been typing dates a certain way daily for many years, it will be very difficult to reprogram my muscle memory.

 

[Pharmscott]

How about not using Canada or a custom region? I just tested this on my computer (ML, Office 2011) and it works as you desire:
1) Type in "03/14/13" and the cell displays 14-Jun-2013
2) Type in "June 14, 2013" and the cell displays 14-Jun-2013 (also works with 2 digit year).

Here are my cell format settings:

 

[Luxsparks]

Yosemite Date issue

Hi DianeK
I'm having the same problem albeit within some VBA code

I have had success changing my Mac to region USA and date work but i need region UK

Did you solve your question

Thank you