Date display

[scott]

I have a spreadsheet that I share with a co-worker that has dates in a
couple of columns. When she opens the files the dates don't fit into
the columns due to Windows date settings for mm/dd/yyyy. I set mine to
mm/dd/yy so that I can shrink those columns and make the sheet more
readable.

I changed my settings to mm/dd/yyyy and opened the spreadsheet and the
dates displayed mm/dd/yyyy and the format was changed to custom
mm/dd/yyyy. I changed the format to mm/dd/yyy and the dates displayed
fine. Does the regional setting change the Excel date format?

 

[John Wilson]

scott,

You're on the right track.
It is the regional settings that you have to set.

John

 

[scott]

So if I understand.
I set the regional settings to mm/dd/yy and I set up a spreadsheet with
a date column formatted to mm/dd/yy and a column width just wide enough
to fit the date. I then send this sheet to someone who has regional
settings of mm/dd/yyyy and their regional settings override my
formatting and theri display shows up ######?

 

[John Wilson]

scott,

Unfortunately, yes.

Try it with a blank workbook.
Enter a date in a cell, format it as mm/dd/yy
Set the column width to just fit and then change your regional
settings to mm/dd/yyyy.
You'll see the change on your worksheet.

I'm sure there are workarounds, but I usually just set all the
regional setting the same on all the PC's in my office or just
insure that my column widths can accomodate both.

John

 

[Dave Peterson]

Just to add to John's reply:

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.)

 

[RWN]

Dave;
I use xl2K and do not have any such animal, but sure wish I did!
I've always found the lack of being able to "lock" the date format a
royal pain. I also have to ensure that all the regional settings are the
same (esp. if the sheet is to be printed and it's "wall to wall").
Nice to know that I'm not the only one who couldn't understand why it
was set up this way.