Default date formats

In Excel 2002, dates entered in a worksheet as "dd/mm" default to a custom
format of "d-mmm". For example, 4/26 shows as 26-Apr in the worksheet cell
even though it is shown as 4/26/2005 in the Formula Bar. I want them to
default to "mm/dd/yyyy". In the Regional settings, the default short date
format is shown as "dd/mm/yyyy".

Any ideas?

J Fultz wrote:
> In Excel 2002, dates entered in a worksheet as "dd/mm" default to a custom
> format of "d-mmm". For example, 4/26 shows as 26-Apr in the worksheet cell
> even though it is shown as 4/26/2005 in the Formula Bar. I want them to
> default to "mm/dd/yyyy". In the Regional settings, the default short date
> format is shown as "dd/mm/yyyy".
>
> Any ideas?

right click on the cell select format > date > Format Cell
select the format format you would like to see. Or change your custom
format to m/d/yyyy.

gls858

Thank you for your quick response but actually I am looking for a way to
change the default. I understand how to correct the format but I would
prefer that it went directly to the "mm/dd/yyyy" format. That would allow me
to enter the date in a sort of shorthand rather than needing to include the
year when I type it in.

I have done additional searching and I have a feeling it is a lost cause.
It just seems strange to me that it defaults to the custom "d-mmm" format
when there are formats specifically listed for dates that would show the date
as "mm/dd". that would still not be what I want but at least it would make
more sense for it to default to a "date" format rather than a "custom" format
if it is clear recognizing the data as a date.

Am I being too logical?
"gls858" wrote:

> J Fultz wrote:
> > In Excel 2002, dates entered in a worksheet as "dd/mm" default to a custom
> > format of "d-mmm". For example, 4/26 shows as 26-Apr in the worksheet cell
> > even though it is shown as 4/26/2005 in the Formula Bar. I want them to
> > default to "mm/dd/yyyy". In the Regional settings, the default short date
> > format is shown as "dd/mm/yyyy".
> >
> > Any ideas?
>
> right click on the cell select format > date > Format Cell
> select the format format you would like to see. Or change your custom
> format to m/d/yyyy.
>
> gls858
>

J Fultz wrote:
> Thank you for your quick response but actually I am looking for a way to
> change the default. I understand how to correct the format but I would
> prefer that it went directly to the "mm/dd/yyyy" format. That would allow me
> to enter the date in a sort of shorthand rather than needing to include the
> year when I type it in.
>
> I have done additional searching and I have a feeling it is a lost cause.
> It just seems strange to me that it defaults to the custom "d-mmm" format
> when there are formats specifically listed for dates that would show the date
> as "mm/dd". that would still not be what I want but at least it would make
> more sense for it to default to a "date" format rather than a "custom" format
> if it is clear recognizing the data as a date.
>
> Am I being too logical?
> "gls858" wrote:
>
>
>>J Fultz wrote:
>>
>>>In Excel 2002, dates entered in a worksheet as "dd/mm" default to a custom
>>>format of "d-mmm". For example, 4/26 shows as 26-Apr in the worksheet cell
>>>even though it is shown as 4/26/2005 in the Formula Bar. I want them to
>>>default to "mm/dd/yyyy". In the Regional settings, the default short date
>>>format is shown as "dd/mm/yyyy".
>>>
>>>Any ideas?
>>
>>right click on the cell select format > date > Format Cell
>>select the format format you would like to see. Or change your custom
>>format to m/d/yyyy.
>>
>>gls858
>>

I guess when you just type 4/26 Excel is assuming that is the format you
want to display. If I type 4/26/2005 then the date is displayed in the
mm/dd/yyyy format.

gls858

Unfortunately you don't get what you want; you get a format that is not one
of your defaults and is not what you type. It gets worse if you type 5/13;
Excel displays May-13 but the formula bar shows it means May 1 2015!

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel

"gls858" <[email protected]> wrote in message
news:%[email protected]...
>J Fultz wrote:
>> Thank you for your quick response but actually I am looking for a way to
>> change the default. I understand how to correct the format but I would
>> prefer that it went directly to the "mm/dd/yyyy" format. That would
>> allow me to enter the date in a sort of shorthand rather than needing to
>> include the year when I type it in.
>>
>> I have done additional searching and I have a feeling it is a lost cause.
>> It just seems strange to me that it defaults to the custom "d-mmm" format
>> when there are formats specifically listed for dates that would show the
>> date as "mm/dd". that would still not be what I want but at least it
>> would make more sense for it to default to a "date" format rather than a
>> "custom" format if it is clear recognizing the data as a date.
>>
>> Am I being too logical?
>> "gls858" wrote:
>>
>>
>>>J Fultz wrote:
>>>
>>>>In Excel 2002, dates entered in a worksheet as "dd/mm" default to a
>>>>custom format of "d-mmm". For example, 4/26 shows as 26-Apr in the
>>>>worksheet cell even though it is shown as 4/26/2005 in the Formula Bar.
>>>>I want them to default to "mm/dd/yyyy". In the Regional settings, the
>>>>default short date format is shown as "dd/mm/yyyy".
>>>>
>>>>Any ideas?
>>>
>>>right click on the cell select format > date > Format Cell
>>>select the format format you would like to see. Or change your custom
>>>format to m/d/yyyy.
>>>
>>>gls858
>>>
>
> I guess when you just type 4/26 Excel is assuming that is the format you
> want to display. If I type 4/26/2005 then the date is displayed in the
> mm/dd/yyyy format.
>
> gls858

Niek Otten wrote:
> Unfortunately you don't get what you want; you get a format that is not one
> of your defaults and is not what you type. It gets worse if you type 5/13;
> Excel displays May-13 but the formula bar shows it means May 1 2015!
>
Not sure whyt you're getting 2015 but when I type 5/13 it displays 13-May
in the cell and 5/13/2005 in the formula bar.

gls858

Thanks again. I tapped some other sources and have concluded it IS a lost
cause. I will just need to remember to include the date if that is the
format I desire.

Regards,

"gls858" wrote:

> J Fultz wrote:
> > Thank you for your quick response but actually I am looking for a way to
> > change the default. I understand how to correct the format but I would
> > prefer that it went directly to the "mm/dd/yyyy" format. That would allow me
> > to enter the date in a sort of shorthand rather than needing to include the
> > year when I type it in.
> >
> > I have done additional searching and I have a feeling it is a lost cause.
> > It just seems strange to me that it defaults to the custom "d-mmm" format
> > when there are formats specifically listed for dates that would show the date
> > as "mm/dd". that would still not be what I want but at least it would make
> > more sense for it to default to a "date" format rather than a "custom" format
> > if it is clear recognizing the data as a date.
> >
> > Am I being too logical?
> > "gls858" wrote:
> >
> >
> >>J Fultz wrote:
> >>
> >>>In Excel 2002, dates entered in a worksheet as "dd/mm" default to a custom
> >>>format of "d-mmm". For example, 4/26 shows as 26-Apr in the worksheet cell
> >>>even though it is shown as 4/26/2005 in the Formula Bar. I want them to
> >>>default to "mm/dd/yyyy". In the Regional settings, the default short date
> >>>format is shown as "dd/mm/yyyy".
> >>>
> >>>Any ideas?
> >>
> >>right click on the cell select format > date > Format Cell
> >>select the format format you would like to see. Or change your custom
> >>format to m/d/yyyy.
> >>
> >>gls858
> >>
>
> I guess when you just type 4/26 Excel is assuming that is the format you
> want to display. If I type 4/26/2005 then the date is displayed in the
> mm/dd/yyyy format.
>
> gls858
>