I created a custom format to try and format a date column to be used within categories. However, Numbers tend to keep reverting my custom format to "Automatic" and I'm unable to group the data as part of a category.

How do I set cells to use my custom date format to be used in categories?

Custom Format created

Setting the field to use the new format Set Cell to new format

Click away from the cell and go back to the field and see that the format has reverted back to "Automatic" Reverting format

  • Have you tried to set it to a 'Date and Time' format and seeing if that works?
    – Natsfan
    Sep 22, 2019 at 2:02
  • Yes I did, it didn’t detect the dates on the categories grouping which led me down this path of trying a custom format.
    – Azeworai
    Sep 22, 2019 at 3:34
  • This is only on mac, right, not possible to create a custom format such as this date format, yyyy.mm.dd on ios/ipados? Oct 20, 2020 at 18:47

1 Answer 1


You can assign you custom format to an empty cell. If you then enter a value that contains a hyphen (-) or a slash (/), such as "1/21/2019", Numbers assumes it’s a date and assigns it your custom date and time format automatically. And keeps it.

Otherwise, if you already have a number in a cell or enter value that is a number, such as "20171214", Numbers cannot recognize it as a date and assign it your custom date format.

Hope, this little video may help to understand as well: https://youtu.be/fUwRHtRLaec

  • Thank you, does this mean that I must re-type in the 7000+ records for numbers to recognise it? I've tried pasting it into a cell that I pre-format and its still having the same problem.
    – Azeworai
    Sep 22, 2019 at 12:54

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .