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.

2 thoughts on “Custom number formats converting to Special formats

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

Leave a Reply

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