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.

"Shannan" wrote in message
...
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!

"Jim Thomlinson" wrote:

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

Jim Thomlinson


"Shannan" wrote:

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.

"Rick Rothstein" wrote:

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.

--
Rick (MVP - Excel)


"Shannan" wrote in message
...
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".

"Jim Thomlinson" wrote:

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

Jim Thomlinson


"Shannan" wrote:

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.