Default date format for dates entered without a year

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

DWalker - 11 Jan 2008 20:14 GMT

Excel 2007: Most online docs claim that Excel's default date settings are
controlled by the Regional and Language Settings (Windows XP) in the
Control Panel.

I have my short date format set to MM/dd/yyyy.

What I don't like, is that when I leave out the year, and enter something
like 1/15 in a cell (which didn't previously have any formatting) in Excel
2007, it gets formatted as dd-mmm and shows as "15-Jan". (The full date
01/15/2008 correctly shows in the Formula bar.)

I don't *ever* want Excel to show "15-Jan". I *hate* that format. I would
like to banish it from Excel's knowledge forever. But, I can't delete that
format apparently.

Is there any way to tell Excel that the default date format for all numbers
that look like dates is "MM/dd/yyyy", regardless of how they are entered,
with or without the year?

(The Long date format in the Regional Settings control panel is "dddd, MMMM
dd, yyyy", or "Tuesday, January 15, 2008" so that's not what's being used
here.)

I don't see this issue discussed anywhere.

Thanks.

David Walker

Reply to this Message

Jim Rech - 11 Jan 2008 22:14 GMT

I feel much like you but I've never found a way to change Excel's native
behavior.

For a given workbook if you copy this into the ThisWorkbook module you'll
get what you want:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Target.NumberFormat = "d-mmm" Then
Target.NumberFormat = "mm/dd/yyyy"
End If
End Sub

Signature

Jim

> Excel 2007: Most online docs claim that Excel's default date settings are
> controlled by the Regional and Language Settings (Windows XP) in the
[quoted text clipped - 28 lines]
>
> David Walker

Reply to this Message

DWalker - 11 Jan 2008 23:34 GMT

> I feel much like you but I've never found a way to change Excel's
> native behavior.
[quoted text clipped - 8 lines]
> End If
> End Sub

OK, thanks. I don't really want a macro that is triggered on every cell
change... but if you have looked and never found any other way, that's a
weird deficiency. I wonder where this format came from...

David

Reply to this Message