Change date format in Numbers

Hey,

As a massive excel user at work I assumed this would be aligned with the system settings as well - turns out not so much.

I've just created a custom date format, whereby I make the date appear like it *should* (yes, read into that what you will incorrect Americans).

All you do is;

1. select a/all cells you want to hold a date
2. click on the 'Cell' tab of the format side bar
3. Drop Down Menu under 'data format' - change to Custom Format
4. Amend order so day is first, month is second, year is third with '/' between them, click ok

Done. Dates will appear like they should.

yeeeeeees I have got it!!! after a lot of frustration shoulders getting tight and head getting sore and a lot if precious work time wasted I have managed to change the dates to the UK format. Can I first say that it is very arrogant of numbers to assume that everybody lives in the US!!!

This is not ideal but if you put a the symboll ' before you type in your date, the cell keeps the date exactly as you have inputted it and I beleive the ' goes away.

I hope this helps and apple I know you have the best products but please don't force your way on to the user once they have bought your product. I bought the new I Mac 5K and I have had some serious issues that I will discuss on aother forum!!

I have the region setting in the System set to Israel...
The Automatic attribute let me have the dates shown as entered (DD/MM/YYYY) but Numbers treats these dates as text and NOT as dates (i.e. it can't calculate the time gap between two dates)... The date formats check box shows only US date formats and the format DD/MM/YYYY is not listed there at all.

Yellowbox wrote:

Date & Time format in Numbers can be either tricky or simple 😕.

Curiouser and curiouser ...

With Region : United States.

Enter a date as d/m. There's no option for d mmm yy.

Now in another cell type in 5 Jan 15 , e.g. d mmm yy.

That now appears as an option in the menu. Nice!

But if I want to enter a new date, say 5 Jan 15, I either have to type that out, or enter 1/5, not 5/1.

Why can't everybody just use the same date format, drive on the same side of the road... and have drains that whirl in the same direction?🙂

SG

So please suggest me the way to get the dates to be displayed in the uk format. If not tell me a better solution than the one i used

I agree with your comment that posters here should be providing you with a better solution rather than suggest that you are somehow doing something wrong, including making a mistake by using Numbers!

If you merely want to display dates in your Numbers tables as you want them to appear, then your solution (the leading apostrophe) is the simplest. Just bear in mind that Numbers will still be confused about which is month and which is day if you actually want to do date calculations. It will display them as you want, though.

If you need to do date calculations, then I've found the easiest solution is to pick the yyyy-mm-dd format. That's at least logical (and I'm speaking as an American used to the "strange" mm-dd-yyyy order) and Numbers will interpret it correctly. To make data entry easier you can just type the apostrophe first. The "dates" will align left as "text" (unless you go to Data Format and change from Automatic to Date & Time) but Numbers is smart enough to know that the "text" there is a date and will allow you to do calculations on it.

If you are adventurous you can also go into the "plist" (as described in the thread linked to above) and add the format you need. I haven't felt a need for that.

Kinky and somewhat mysterious date problems crop up in other spreadsheet apps too, including Excel, which has wreaked havoc with my date formats on several occasions. So using "anything else" is not necessarily useful advice to follow if you like the other features in Numbers.

Having said that, by all means go to Numbers > Provide Numbers Feedback in your menu and ask them to allow data entry using the dd/mm/yyyy format. I doubt that it's a question of cultural arrogance (note that dozens of languages now included under Help > Numbers Help). It's probably more a question of prioritizing software engineering resources. Your feedback may help this issue bubble up toward the top.

SG

Thanks for posting the tip. Though American I find the American format odd too.🙂 I tend to use yyyy-mm-dd, on my machine one of the "built-in" choices, but the Custom Format you've illustrated is quite handy.

SG

That will work for cells that you pre-format using that format. It does not solve the underlying problem of why Numbers is not using the date format specified in System Preferences but it is a reasonable workaround.

Taking a jab at "incorrect Americans" in your post is rude and uncalled for. If it bothers you so much to use American-developed software, quit using it.

Making no attempt at being politically correct or gentle or anything else, there have been plenty of times in the past where someone said they did something or said they set something a certain way and it turns out they didn't or they didn't do it correctly. There have been plenty of times where the description of the problem puzzled everyone until, over a series of questions (and sometimes a required screenshot or two), the details slowly emerged and it turned out to be something totally different or user error. It happens. There is no judgement, just an attempt to fix the problem. But if someone gets defensive, it gets really hard to help them. If they get belligerent, there is no desire to help them. If they get insulting, forget them.

Hi Badunit,

I thought cameron.s.hill1's post was a helpful and nicely presented illustration of the use of Custom Format in Numbers 3, and hope cameron.s.hill1 keeps posting here.

It must be admitted that Americans are in fact strange with their dates. At least this American thinks so. In any case this American takes no offense at a little ribbing, and hopes others don't either.

Wishing you and all others here a Merry Christmas or whatever other holiday you observe!

SG

Hi SG,

Why can't everybody just use the same date format, drive on the same side of the road... and have drains that whirl in the same direction?

I tend to use yyyy-mm-dd

Now that makes sense! Year Month Day (decreasing magnitude from left to right, just like a decimal number).

Extend that concept to an instant in time:

Year Month Day Hour Minute Second Millisecond.

When I become world dictator, that will be my first decree. My second decree shall be that we all drive on the left 👿.

Happy Boxing Day from Australia.

Regards,

Ian.

Hi Cameron,

If I just type my preferred date style into a blank cell (formatted as Automatic), Numbers is clever enough to convert that to a date.

For example, if I type 24 dec 2015 or 1 jan 2016 (yes, lower case for the month works too 😉) Numbers converts them to D or DD MMM YYYY (see the ticked format at the bottom of this list:

Date & Time format in Numbers can be either tricky or simple 😕.

Regards,

Ian.

"when I type over the date, it automatically changes to US date format."

Have you set a specific date and time format for the cells into which you are typing? Left on Automatic, they'll adopt the format you see.

I've also found that if I change the format of the date part of a D&T value's cell, it will interpret the month and day as if it were written in the US format, even when it wa correctly displayed prior to the change.

I'm using a custom setting in System Preferences for the all-numbers form: ccyy-mm-dd, but otherwise, my Region is set to Canada.

Regards,

Barry

Is this still the case that numbers doesn't allow you to change the date format of your cells?

Surely this is a fundamental flaw in Numbers?

Isn't this supposed to be a spreadsheet program?

Why have the options when they don't actually do anything?

RAYtheMAN wrote:

yeeeeeees I have got it!!!

Nooooooo you haven't. All you have done is changed the entry from a real date to a text string. So now it's useless as a date.

The preceding comma is a (very) old Excel function that Numbers supports.(Actually it existed in Lotus 123, 25 years ago)

The problem is that no matter what I do I cannot get the date changed in numbers to the UK date format although I had already put my region in as UK etc in the system preferences. So please suggest me the way to get the dates to be displayed in the uk format. If not tell me a better solution than the one i used