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?

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?

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?

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?

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?

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?

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?