Excel 2011 Date Format

optimumreports
optimumreports used Ask the Experts™
on
Hi all,

I am using Excel 2011 for the Mac for the first time and have noticed that when I input a date into a cell using the UK format (dd/mm/yyyy) it always shows the US format (mm/dd/yyyy) when I exit from the cell. I have tried all the usual format changes to change it but it doesn't work.

Does anyone know how to fix this - I am new to Macs so I may need you to spell it out simply for me to find my way around.

Cheers,

Tom
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2011
Awarded 2010
Commented:
Hello,

what are "all the usual format changes"?

What are the default date settings for your system? Excel will respond to these. If you system is set to display dates as DD/MM/YYYY, then formatting a cell as date in Excel will follow that convention. If you want to change the date setting in Excel, you can either change your system date defaults, or you can use a custom format for the cells, like

MM/DD/YYYY

or

DD/MM/YYYY

whatever you want to apply.

The attached screenshot shows the two different date formats on my machine. My system defaults are DD/MM/YYYY. B2 shows the default date format. B3 shows the date with custom format MM/DD/YYYY

cheers, teylyn
scratch.png

Author

Commented:
Hi,

Where do you get to make changes/check the system date default on a MAC please?

Tom
Most Valuable Expert 2011
Awarded 2010
Commented:
Pass. I'm a Windows user and haven't touched a Mac in 20 years. But to set a custom format for a cell, open the Format dialog for the cell, click the Number tab and select Custom, then enter the formatting string of your choice for the cell, e.g.

MM/DD/YYYY

Somewhere in the basic settings for your Mac, there must be an area for regional settings. If the iPad /iPhone is anything to go by, try Settings > General > International

Or, a quick Google search on "mac OS regional settings" yielded several links, for example]

http://www.mackb.com/Uwe/Forum.aspx/apple/475/New-Mac-laptop-how-do-I-change-regional-settings

Hope that applies to your situation.

cheers, teylyn

Author

Commented:
Cheers Teylyn... That sorted it. I've heard so many people say that once you get used to Mac you wont go back! Not too sure if thats true yet from what you said!!

Thanks again...

Tom
Most Valuable Expert 2011
Awarded 2010

Commented:
Thanks for the grade, Tom.

Apart from the iPhone, which was issued to me by my employer, and the iPad, which I got as a fun tool for the kids last year, I don't have any recent Mac experience. I can see some benefits for the very basic user (like my husband), but I'm not too fond of the (lack) of customisation on offer.

Some things are very easy to do. Others are absolutely impossible, at least not the way the regular DOS/Windows person excpects them to be. Others again, are outright forbidden (tethering the iPad with an iPhone to the internet, OMG!!).

My early-career Mac experience did not go down too well, and I still have this bad taste in my memory....

My husband is soon due for a replacement computer. We've been toying with the "Mac" idea, but I'm not convinced that I would  be able to support him, given his two (or three?) left hands and propensity to run into deep trouble, especially when I'm out of reach.

I think, I'll stick with Windows for the time being.

Excuse the blurb. Sometimes, an expert needs to vent.

Hope all is well with your setup now.

cheers, teylyn

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial