Custom number format problem

  • Thread starter Thread starter Peter Montano
  • Start date Start date
P

Peter Montano

Using Excel 97, I've created a custom number format mm/dd/yyyy to display dates to look like 01/04/2004, for instance. I've applied that format to a number of ranges within the worksheet. The dates get displayed correctly.

The worksheet gets posted to a network drive so others users can access it as well. If I then open the worksheet on the network drive from the PC that created it, the custom date format displays correctly.

If I open the worksheet on the network drive from a PC other than the one that originally created it, the custom dates revert to the date number format 1/4/04, for instance. The custom date format that I initially created with the worksheet isn't even listed under the custom number settings. All other users are also using Excel 97.

Any clues as to why the custom date format isn't displaying correctly or listed under the custom number formats?
 
'Cause there are some custom date formats that don't belong to you.

You happened to use the same format that excel uses to tell it to pick up the
format from the windows regional settings (under control panel).

xl2002 is more honest with the way it deals with dates. (I think it's new with
xl2002, but maybe it's in xl2k, too.)

When I do Format|cells|Number Tab and click on the date category, I get a
message in the dialog:

Date formats display date and time serial numbers as date values. Except for
items that have an asterisk (*), applied formats do not switch date orders with
the operating system.

Maybe you could use a different (one that windows doesn't "own") format.

My favorite date format:
mm/dd/yyyy* dddd
(it'll be justified right and left and show both the date and day.)
 
ps. Could you post in plain text? It's easier to read for some of us old(er)
folks.
 
Dave;
Thank you, thank you, thank you!

For years (xl97 and now 2k) I have been frustrated as h*ll trying to
figure out date formatting. Got to the point where I figured I was just
plain stupid (which I very well may be)!
I knew it was something to do with the regional settings/options but
couldn't figure out the connection. The Help seems to just talk about a
four vs. two digit year.

Your note about Windows "owning" some formats makes it fall into place.

BTW my 2k does not display the "..except for *..." message, just
"Date formats display date and time serial numbers as date values" (then
a blurb about time).
Are the dates listed in the regional settings short date area the ones
that Windows owns?

Again, thanks.
 
Thanks for the clarification about xl2k. I use it at work, but I don't trust my
memory/eyesight!

In xl2002, I have these formats with asterisks:

*03/13/2001
*Wednesday, March 14, 2001

Both correspond to my regional settings:
mm/dd/yyyy (short date style)
dddd, MMMM dd, yyyy (long date style)

(I think I've changed my short date for 4 digit years and I don't think my long
date style is the standard. Check your windows regional settings to see what
corresponds to yours.)

(And I'd bet more than a few were frustrated--that's why xl2002 got the note!)
 
Back
Top