Date format defaults to Custom


J

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?
 
Ad

Advertisements

S

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.
 
J

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
 
S

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.
 
D

David Biddulph

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

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
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top