Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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? |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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" wrote in message ... 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 |
#6
|
|||
|
|||
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 |
#7
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Changing Cell formats to date fields automatically | Excel Worksheet Functions | |||
excel date formats changed | Excel Worksheet Functions | |||
US vs UK date and time formats | Excel Discussion (Misc queries) | |||
Seed date formats to different year in different cells | Excel Discussion (Misc queries) | |||
Excel Default date for date format | Excel Discussion (Misc queries) |