Date Format as *3/14/2008

[Daren]

Hello,

If the format for date is *3/14/2008, I get the message at the bottom of the
Format Cells Box that "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." What does this mean?

Thanks!

 

[Bernard Liengme]

I use Canadian date format so I see
* 14/03/2008
and
14 March 2008

If I use the Windows Regional Setting to change to US dates then the first
will become
* 3/14/2008 and all dates in cell will no have the month first
But cell formatted as 14 March 2008 will not be affected by the Regional
Setting

I guess only non-US buyers of new PCs ever have to play with Regional
Setting other than to set the time zone

best wishes

 

[Daren]

Ok, thanks. If the cell is formatted as 3/14/2008 then as I understand,
there would be no change in how dates are shown. Is that correct?

 

[David Biddulph]

Try this:
Put the formula =TODAY() in 9 or so cells.
Format each of these in turn with the various options from the Format Cells/
Number/ Date options.
In my case the first 2 of these options are the ones that had an asterisk
beside them, with the explanation footnote you quoted.

Now go to the windows Control Panel, Regional Settings, and either change to
a different option, such as changing English US to English UK, or just
change the options for display of long date and short date to some different
formats.
Now go back to your excel spreadsheet and you'll see that the formats which
had the asterisk alongside them have changed to match the new regional
settings options for short & long dates, but the rest of the formats
displayed are unaltered.

Now remember to go back to Regional Settings and put them back to your
normal settings.

 

[Dave Peterson]

Another difference...

Say we have a workbook that we have to share. Both of us use the mdy order for
our dates.

But you have your windows shortdate settings to use m/d/yy (1 or 2 digits, 1 or
2 digits, 2 digits).

And I have that setting for mm/dd/yyyy (2 digits, 2 digits, 4 digits).

You enter a bunch of dates in column A and autofit the column to make it pretty.

You send the workbook to me and I open it up and I see #######'s in column A.
That columnwidth isn't wide enough for all 10 of my characters!

So I have to widen the column or change the fontsize or something to see those
dates.

It's not a big deal until you protect that worksheet with a password -- and I
don't know the password!

Then it becomes a real pain for the sender (you!). You'll be getting phone
calls about why you broke the computer!

 

[Bob I]

See Regional and Language Options in the Control Panel. You may change
the format there.

 

[Bernard Liengme]

No. If the format is 3/14/2008 and you change your Regional Setting to
Canada, UK, France or nearly anywhere else then the cell will show 14/3/2008
best wishes

 

[Daren]

Thanks.

See Regional and Language Options in the Control Panel. You may change
the format there.

 

[Daren]

Thanks.

Try this:
Put the formula =TODAY() in 9 or so cells.
Format each of these in turn with the various options from the Format Cells/
Number/ Date options.
In my case the first 2 of these options are the ones that had an asterisk
beside them, with the explanation footnote you quoted.

Now go to the windows Control Panel, Regional Settings, and either change to
a different option, such as changing English US to English UK, or just
change the options for display of long date and short date to some different
formats.
Now go back to your excel spreadsheet and you'll see that the formats which
had the asterisk alongside them have changed to match the new regional
settings options for short & long dates, but the rest of the formats
displayed are unaltered.

Now remember to go back to Regional Settings and put them back to your
normal settings.

 

[Daren]

Thanks.

Another difference...

Say we have a workbook that we have to share. Both of us use the mdy order for
our dates.

But you have your windows shortdate settings to use m/d/yy (1 or 2 digits, 1 or
2 digits, 2 digits).

And I have that setting for mm/dd/yyyy (2 digits, 2 digits, 4 digits).

You enter a bunch of dates in column A and autofit the column to make it pretty.

You send the workbook to me and I open it up and I see #######'s in column A.
That columnwidth isn't wide enough for all 10 of my characters!

So I have to widen the column or change the fontsize or something to see those
dates.

It's not a big deal until you protect that worksheet with a password -- and I
don't know the password!

Then it becomes a real pain for the sender (you!). You'll be getting phone
calls about why you broke the computer!