| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date format defaults to Custom
Whenever I enter a date into a cell, such as 1/2, for Jan 2, 2008, (appearing
as 2-Jan in the spreadsheet, but showing as 1/2/2008) the cell format is a "Custom" format. Where can I change the setting (??) so that whenever I enter 1/2, the format is a "Date" format, not a Custom one? |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Date format defaults to Custom
Hi John,
How do you want the date to appear in the cell? The Custom format you are getting is a custom date format, so it is a date format. If what you want is that date format should appear in the Date category of the Format Cells dialog box rather than Custom, that's a problem. -- Thanks, Shane Devenshire "JohnL" wrote: Whenever I enter a date into a cell, such as 1/2, for Jan 2, 2008, (appearing as 2-Jan in the spreadsheet, but showing as 1/2/2008) the cell format is a "Custom" format. Where can I change the setting (??) so that whenever I enter 1/2, the format is a "Date" format, not a Custom one? |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Date format defaults to Custom
Thanks for responding, Shane.
I understand that even tho it is a "custom" format, it is still a date format, just not the one I want. What I end up doing is FormatCellsNumberDate and choosing 3/14/01. I do a lot of uploading of spreadsheets to Access tables and this is a date format that seems to work well. In EXCEL can I have this format (3/14/01) as the default date format so that when I enter 1/3 into the cell it returns as 1/3/08 and not 3-Jan? Thanks John "ShaneDevenshire" wrote: Hi John, How do you want the date to appear in the cell? The Custom format you are getting is a custom date format, so it is a date format. If what you want is that date format should appear in the Date category of the Format Cells dialog box rather than Custom, that's a problem. -- Thanks, Shane Devenshire "JohnL" wrote: Whenever I enter a date into a cell, such as 1/2, for Jan 2, 2008, (appearing as 2-Jan in the spreadsheet, but showing as 1/2/2008) the cell format is a "Custom" format. Where can I change the setting (??) so that whenever I enter 1/2, the format is a "Date" format, not a Custom one? |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Date format defaults to Custom
Hi John,
I don't believe you can set the default date format just for Excel. You might be able to do it in Windows, but I'm not sure what impact that would have on Excel. Alternatively you could add the following code to your workbook Sub myDate() Selection.NumberFormat = "m/d/yy;@" End Sub And assign a shortcut key to it or assign it to a toolbar button. Then at least the process would be a single step. A separate point - Of course once a cell has been formatted with the desired date format you can quickly copy and paste that format to many other cells using the Format Painter. -- Cheers, Shane Devenshire "JohnL" wrote: Thanks for responding, Shane. I understand that even tho it is a "custom" format, it is still a date format, just not the one I want. What I end up doing is FormatCellsNumberDate and choosing 3/14/01. I do a lot of uploading of spreadsheets to Access tables and this is a date format that seems to work well. In EXCEL can I have this format (3/14/01) as the default date format so that when I enter 1/3 into the cell it returns as 1/3/08 and not 3-Jan? Thanks John "ShaneDevenshire" wrote: Hi John, How do you want the date to appear in the cell? The Custom format you are getting is a custom date format, so it is a date format. If what you want is that date format should appear in the Date category of the Format Cells dialog box rather than Custom, that's a problem. -- Thanks, Shane Devenshire "JohnL" wrote: Whenever I enter a date into a cell, such as 1/2, for Jan 2, 2008, (appearing as 2-Jan in the spreadsheet, but showing as 1/2/2008) the cell format is a "Custom" format. Where can I change the setting (??) so that whenever I enter 1/2, the format is a "Date" format, not a Custom one? |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Date format defaults to Custom
Have you looked at the default date formats set up in your Windows Regional
Options? -- David Biddulph "JohnL" wrote in message ... Thanks for responding, Shane. I understand that even tho it is a "custom" format, it is still a date format, just not the one I want. What I end up doing is FormatCellsNumberDate and choosing 3/14/01. I do a lot of uploading of spreadsheets to Access tables and this is a date format that seems to work well. In EXCEL can I have this format (3/14/01) as the default date format so that when I enter 1/3 into the cell it returns as 1/3/08 and not 3-Jan? Thanks John "ShaneDevenshire" wrote: Hi John, How do you want the date to appear in the cell? The Custom format you are getting is a custom date format, so it is a date format. If what you want is that date format should appear in the Date category of the Format Cells dialog box rather than Custom, that's a problem. -- Thanks, Shane Devenshire "JohnL" wrote: Whenever I enter a date into a cell, such as 1/2, for Jan 2, 2008, (appearing as 2-Jan in the spreadsheet, but showing as 1/2/2008) the cell format is a "Custom" format. Where can I change the setting (??) so that whenever I enter 1/2, the format is a "Date" format, not a Custom one? |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Date format defaults to Custom
Hi David,
I hadn't thought of that. So I checked and it is set at M/d/yyyy. Thanks for the suggestion. John "David Biddulph" wrote: Have you looked at the default date formats set up in your Windows Regional Options? -- David Biddulph "JohnL" wrote in message ... Thanks for responding, Shane. I understand that even tho it is a "custom" format, it is still a date format, just not the one I want. What I end up doing is FormatCellsNumberDate and choosing 3/14/01. I do a lot of uploading of spreadsheets to Access tables and this is a date format that seems to work well. In EXCEL can I have this format (3/14/01) as the default date format so that when I enter 1/3 into the cell it returns as 1/3/08 and not 3-Jan? Thanks John "ShaneDevenshire" wrote: Hi John, How do you want the date to appear in the cell? The Custom format you are getting is a custom date format, so it is a date format. If what you want is that date format should appear in the Date category of the Format Cells dialog box rather than Custom, that's a problem. -- Thanks, Shane Devenshire "JohnL" wrote: Whenever I enter a date into a cell, such as 1/2, for Jan 2, 2008, (appearing as 2-Jan in the spreadsheet, but showing as 1/2/2008) the cell format is a "Custom" format. Where can I change the setting (??) so that whenever I enter 1/2, the format is a "Date" format, not a Custom one? |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Date format defaults to Custom
Thanks for the code, Shane. It appears to be an easy enough work around.
John "ShaneDevenshire" wrote: Hi John, I don't believe you can set the default date format just for Excel. You might be able to do it in Windows, but I'm not sure what impact that would have on Excel. Alternatively you could add the following code to your workbook Sub myDate() Selection.NumberFormat = "m/d/yy;@" End Sub And assign a shortcut key to it or assign it to a toolbar button. Then at least the process would be a single step. A separate point - Of course once a cell has been formatted with the desired date format you can quickly copy and paste that format to many other cells using the Format Painter. -- Cheers, Shane Devenshire "JohnL" wrote: Thanks for responding, Shane. I understand that even tho it is a "custom" format, it is still a date format, just not the one I want. What I end up doing is FormatCellsNumberDate and choosing 3/14/01. I do a lot of uploading of spreadsheets to Access tables and this is a date format that seems to work well. In EXCEL can I have this format (3/14/01) as the default date format so that when I enter 1/3 into the cell it returns as 1/3/08 and not 3-Jan? Thanks John "ShaneDevenshire" wrote: Hi John, How do you want the date to appear in the cell? The Custom format you are getting is a custom date format, so it is a date format. If what you want is that date format should appear in the Date category of the Format Cells dialog box rather than Custom, that's a problem. -- Thanks, Shane Devenshire "JohnL" wrote: Whenever I enter a date into a cell, such as 1/2, for Jan 2, 2008, (appearing as 2-Jan in the spreadsheet, but showing as 1/2/2008) the cell format is a "Custom" format. Where can I change the setting (??) so that whenever I enter 1/2, the format is a "Date" format, not a Custom one? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Looking for custom date format | Excel Worksheet Functions | |||
| Date Format in Custom Header | Excel Discussion (Misc queries) | |||
| Custom format date | New Users to Excel | |||
| Custom number format always defaults last number to 0. | Excel Discussion (Misc queries) | |||
| Custom Date format ie. 01.01.05 W1 (W1 is week 1) | Excel Discussion (Misc queries) | |||
Linear Mode
