date formatting changes

[FJDx]

I have a VB macro that changes all date formats to dd/mm/yy. This works
fine. However, sometimes when I go back into my files at a later date, I
find that the dates convert back to dd/mm/yyyy. Why does this happen
when I have specifically set the format to dd/mm/yy?

 

[Dave Peterson]

There are some custom date formats that don't belong to you.

I'm betting that 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.

Or you could change your windows short date format.
windows start button|settings|control panel|regional settings applet
Date Tab.

and you didn't ask...

My favorite date format:
mm/dd/yyyy* dddd
(it'll be justified right and left and show both the date and day.)

 

[Mark]

Use Cells > Format > Custom
type in the edit box dd/mm/yy
it shows how the text will look as you type it.
and format the cells you want.