Default date format for dates entered without a year

  • Thread starter Thread starter DWalker
  • Start date Start date
D

DWalker

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
 
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
 
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
Click to expand...

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
 
Back
Top