Custom number formats converting to Special formats

Occasionally I use custom number formats in Excel that happen to match one of Excel’s built-in Special formats. For example, if I custom format a cell as:

00000

the next time I view the cell format, Excel has merrily decided that this is a US zip code. Most of the time, I don’t care because the format still does what I need (although it can be annoying if, for example, the special format uses a different locale – e.g. using 000 as a format changes to a Chinese locale, making it difficult to read the dialog! For this case, you can specify your own locale such as [$-809]000 – 809 being for UK).

 

However, this can occasionally be an issue. Consider the following situation (which, of course, occurred in a forum thread recently):

As the developer, your PC settings use dd/mm/yy as the default Short Date format. You specifically assign the custom format:

dd/mm/yy

to some cells because you have limited space on your dashboard and don’t need 4 digit years. Everything seems fine – to you anyway.

 

Unfortunately, behind the scenes, Excel has actually decided that what you meant to do was assign a Short Date format because the custom format you chose matches your Short Date format. So when you distribute this tool proudly to your users, whose short date formats are set to use dd/mm/yyyy by default, all they see is ##### because the dates are too wide for the column widths. And you look like a numpty who doesn’t know what they’re doing. 😉

 

Luckily, the solution is pretty simple (for most situations) – include a text part in the custom format. If you, our intrepid developer, apply a custom format of:

dd/mm/yy;@

to the cells instead, this does not match the Short Date format, so Excel leaves it alone.

Since most (all?) built-in special number formats do not include a text part, this should work for most situations (unless you need all 4 custom format sections for some purpose; then you’re SOL.)

 

I hope that saves someone some time/hair/patience/money.

3 thoughts on “Custom number formats converting to Special formats

  1. Pingback: Format Cells occasionally showing Chinese (Taiwan) locale

  2. Trisha says:

    Hi I am trying to keep a custom format as 6 digits, so I select custom 000000 and click ok, it then reverts to special format rather than staying at custom as I selected. I have tried what you have suggested above but when I save and close the file and later open it the 6 digits are gone
    For example 001234 becomes 1234 any suggestions on how to make this stop happening as I need to load these figures into a programm as 6 digits and if its not it won’t load, this is driving me insane

    Any help appreciated

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.