Excel 2010: Change default format of today's date shortcut format

0
votes

Question: How do I change the default of the date format using the Excel shortcut [Ctrl] + [;]? In other words, a "insert today's date as dd-MMM-yyyy at cursor placement" solution.

Currently, it is set to dd-mm-yy.

I use Windows 7 Enterprise (SP1).

Reason: I want it to be dd-MMM-yyyy, as this is corporate standard. I am often using this shortcut in a cell with other text, so a cell date formatting isn't feasible.

Examples:

Tried so far: I've have the "short date" in Control Panel -> Region and Language set to dd-MMM-yyyy, so this is obviously not where the formatting is defined.

PS: This is my first post on stackoverflow. Please let me know, in a respectful way, if I should have written/structured this post differently.

2
exceldateexcel-2010keyboard-shortcutsdate-formatting
You could write vba code that use the same shortcut to override the standard. - Scott Craner
What version of Windows are you using ?? - Gary's Student
I'm using Windows 7. Sorry for leaving that out. - Michael Kristensen
You need to look into regex to pull out and replace the text in the format you want. Try writing it on your own. Your question originally was specific enough that a one line code would answer it, now you are asking someone to write a fairly complex code for you. That is not what Stack Overflow is for. - Scott Craner
Thanks for hinting the direction to go. My question was always the same, I simply elaborated on it as several people misunderstood my question. I was not aware that it was such a complex code, as I my programming knowledge is quite low. I know that Stack Overflow is not for requesting big complex codes. - Michael Kristensen

2 Answers

0
votes

You need to fix your Language and Regions settings from the Control Panel:

enter image description here

Getting there varies with the version of Windows.

0
votes

Scott's suggestion is good. This worked for me:

EDIT

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Len(Target) <= 10 Then
    Target.NumberFormat = "dd-mmm-yyyy"
Else
    Target = Replace(Target, Date, Format(Date, "dd-mmm-yyyy"))
End If
Application.EnableEvents = True
End Sub

Place the above code in the Worksheet object