formatting dates
 

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.

formatting dates
 

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.

formatting dates
 

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.

formatting dates
 

How is it to know what year it is?

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.

formatting dates
 

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

Jim Thomlinson


"Shannan" wrote:

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.

formatting dates
 

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"?

"Jim Thomlinson" wrote:

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

Jim Thomlinson


"Shannan" wrote:

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.

formatting dates
 

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.

formatting dates
 

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.

--
HTH...

Jim Thomlinson


"Shannan" wrote:

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"?

"Jim Thomlinson" wrote:

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

Jim Thomlinson


"Shannan" wrote:

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.

formatting dates
 

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.

--
Rick (MVP - Excel)


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

"Jim Thomlinson" wrote:

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

Jim Thomlinson


"Shannan" wrote:

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.

formatting dates
 

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.

formatting dates
 

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.

formatting dates
 

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.

formatting dates
 

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.