Level 4

1,030 points

Change date format to yyyy-mm-dd

I have some dates currently formatted as "2016 May 20, Friday". I would like to change the date format to "2016-05-20". How can I do this for 200 cells at once? I have tried selecting the cells and going to Inspector -> Cell -> Data Format -> Date & Time, but after choosing my date format, nothing changes. Help?

I would also like the menu bar command Insert -> Date & Time to use the yyyy-mm-dd format by default, so I don't have to reformat the date every time I insert it.

Posted on May 20, 2016 4:02 PM

25 replies

ffiti Author

Level 4

1,030 points

May 23, 2016 1:39 AM in response to SGIII

I'm very familiar with the Language & Region preference pane. I've already customized all that stuff. However when I use Insert -> Date & Time, it doesn't mean I always want the "Full" date.

SGIII

Level 8

37,930 points

May 23, 2016 3:46 AM in response to ffiti

If you customized "all that stuff" then that's very likely the root of your problem. Numbers can recognize a lot of different formats but apparently not your custom format. Either recustomize 'Full:' to something that Numbers succeeds in recognizing as a valid date-time string (just a little experimentation should do the trick) or fix things each time after inserting. Or use a Service that outputs a string that Numbers can handle.

ffiti Author

Level 4

1,030 points

May 20, 2016 6:22 PM in response to SGIII

ffiti Author

Level 4

1,030 points

May 20, 2016 6:29 PM in response to SGIII

When I click on each cell, it says "text". Note that the Data Format says Automatic. When I change it to Date & Time, nothing happens...

SGIII

Level 8

37,930 points

May 20, 2016 6:32 PM in response to ffiti

Ok, if it's the same for all the cells that means Numbers doesn't recognize those cell contents as a date. I'll post a script that you can try to convert to YYYY-MM-DD format.

ffiti Author

Level 4

1,030 points

May 20, 2016 6:34 PM in response to SGIII

I inserted these dates with the menu bar Insert -> Date & Time command. Maybe that has to do with it?

ffiti Author

Level 4

1,030 points

May 20, 2016 6:38 PM in response to SGIII

I just want to fix my date column in my spreadsheet. I have other spreadsheets that work just fine; it's just this one that's problematic.

SGIII

Level 8

37,930 points

May 20, 2016 6:40 PM in response to ffiti

I'll post a script.

ffiti Author

Level 4

1,030 points

May 20, 2016 10:11 PM in response to Barry

I know that Numbers automatically aligns cells depending on their contents. But in my case, every cell had Data Format "Automatic", and every cell was centered.

ffiti Author

Level 4

1,030 points

May 21, 2016 2:28 AM in response to Barry

I know what the difference is. Whatever gave you the idea I was confusing the two?

This thread has been closed by the system or the community team. You may vote for any posts you find helpful, or search the Community for additional answers.

Change date format to yyyy-mm-dd