Date format defaults to Custom

[JohnL]

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?

 

[ShaneDevenshire]

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.

 

[JohnL]

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 Format>Cells>Number>Date 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]

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.

 

[David Biddulph]

Have you looked at the default date formats set up in your Windows Regional
Options?

 

[JohnL]

Hi David,
I hadn't thought of that. So I checked and it is set at M/d/yyyy.

Thanks for the suggestion.

John

 

[JohnL]

Thanks for the code, Shane. It appears to be an easy enough work around.

John