formatting dates

[Shannan]

Hi,
I am trying to format all the date columns in my spreadsheet to show up at
Y/M/D. I've set the formatting to "Date" and then "01-03-14". However, if i
type 09-11-23, meaning november 23rd 2009, it keeps changing it to 23-11-09.
If i click on the cell, it displays 09/11/2023 in the fx bar at the top of
the screen. Is there a way to get around this without having to type in
2009-11-23?

Thanks. Shannan.

 

[Jim Thomlinson]

The format only changes how the date is displayed and not how it should be
entered. To enter Nov 23, 2009 type

11/23/2003

The formatting will flip around how the date is displayed...

 

[Shannan]

But i want it to display 09-11-23 and so i am typing in 09-11-23 but excel is
automatically changing it to 23-11-09 when i've set the formatting to "date"
and "01-03-14".

 

[Bob I]

How is it to know what year it is?

 

[Jim Thomlinson]

The display will come out correct if you type in 11/23/2009 if you have
formatted the cell Y/M/D. Your operating system is in charge of how dates are
entered and not XL. If you want to change how dates are entered then

Start -> Setting -> Control Panel -> Regional and Language -> Date | yyyy/m/d

Note that this changes date entry for your entire system and not just XL...

 

[Shannan]

So basically since this is a spreadsheet that will be used by multiple clerks
at different locations who cannot change things for their entire system...we
don't have a way around it. We just have to type the full "2009-11-23" and
not shorten it to "09-11-23"?

 

[Rick Rothstein]

First, formatting a cell only affects how the value in the cell will be
*displayed*... it does not change the functionality of Excel... dates will
still need to be entered as dates normally would be entered on your system.
Second, there is no "01-03-14" option for a Date format, at least not on my
US local regional system version of Windows.

VB code can change functionality, but we would need to know exactly the
functionality you want/need.

 

[Jim Thomlinson]

You can type in any of the following
nov 23
11/23
11/23/09
11/23/2009
2009/11/23

All of those represent Nov 23, 2009.
You can use / and - interchangeably.

 

[Rick Rothstein]

Each user should type in the date as he/she would normally enter a date on
their system (their Windows' regional settings will interpret what that date
is, not your cell formatting)... the display your cell formatting imposes
has no bearing on entering the value, only on how it will be displayed after
the entry is made.

 

[Shannan]

Sorry, i'm on the "English (Canada)" Locale, not the US one. Ok, perhaps i'm
not explaining well. This is a spreadsheet that will be used to about 20
different people who are all in different locations at their own computers.
Because we will be using the spreadsheet to collect data, we want all the
dates to be displayed the same way. However, since so many people will be
typing in data, i'm trying to set it up so that the date will always be
displayed as Year/Month/Day no matter how it is typed in, as i'm sure each
clerk is used to typing it in their own way. I was wondering if this is
possible or if i just have to tell them all the change their ways. I had
thought that by setting the formating to date and to "01-03-14", i was
solving the problem, but it appears not. Thanks.

 

[Jim Thomlinson]

It does not matter how they type it in, the format will output it as
year/month/day. But you do not enter it that way. You enter it based on how
your machine is set up. I will give you 99% chance that everyone is set up
m/d/yyyy. To that end you can not type in 09/11/23 as that will be
interpreted (by your system and not XL) as Sep 11, 2023 with will be output
formatted 2023/09/11. 2009-11-23 works only because you have removed the
ambiguity of which element is the year.

 

[Shannan]

Ok, so i will just have to let them know that they can't type in "09-11-23"
then; that they must type in it as either "2009-11-23" or as "23-11-09".
Thanks for answering my question!

 

[Fred Smith]

Shannan,

Why aren't you listening to what is being explained to you? Do you want a
solution, or do you just want to be a victim? You are making us Canadians
look bad.

As has been explained several times, entering dates into Excel is based on
the user's Regional Settings. If your users want to enter year/month/day,
all they have to do is change their Windows settings.

If they can't (or won't) change their Windows setting, then they (or their
higher-ups) have determined that must enter dates according to some other
standard. It's not for Excel to change this decision.

Regards,
Fred.