Entering Dates Into Cells Part II

I’m sure you all remember Entering Dates Into Cells from five years ago. It was a simpler time. Brittney Spears was telling us about her prerogative, the Olympics returned to Athens, and my date entering needs reflected the simplicity of the times. Now I need more.

Here are some things I discovered about myself:

I almost never need to enter the time into a cell
I don’t need to continue editing the cell after I press Cntl+; to enter the date
I often have to enter yesterday or tomorrow

Time to bloat the PMW again. I’m going to take over the Control+; (control plus semicolon) shortcut and the Control+Shift+; shortcut. The former will put the date into the activecell unless a date already exists there, in which case it will increment the date by one day. The latter will do the same except that it will decrement the date by one day.

You know what’s great about a video demonstrating keyboard shortcuts? Anyway, here’s what selecting C4, pressing Cntl+; three times, and pressing Cntl+Shift+; three times looks like:

In my Auto_Open and Auto_Close macros, respectively, I have these OnKey assignments

Application.OnKey “^;”, “IncrementDate”
Application.OnKey “^+;”, “DecrementDate”

Application.OnKey “^;”
Application.OnKey “^+;”

I should have made the following all one macro, but I’m a little lazy like that.

Sub IncrementDate()

If IsDate(ActiveCell.Value) Then
ActiveCell.Value = ActiveCell.Value + 1
Else
ActiveCell.Value = Date
ActiveCell.NumberFormat = “m/d/yyyy”
End If

End Sub

Sub DecrementDate()

If IsDate(ActiveCell.Value) Then
ActiveCell.Value = ActiveCell.Value – 1
Else
ActiveCell.Value = Date
ActiveCell.NumberFormat = “m/d/yyyy”
End If

End Sub

4 thoughts on “Entering Dates Into Cells Part II”

Nice hint. Thanks. I’ve never realized that not even I do NOT actually need to continue editing the cell after entering the date. :)

I must be a complete doofus but I cannot get this working. I created a macro called Auto_Open in ThisWorkbook of PERSONAL.XLSB. Excel 2007 does not run this macro. I have macros disabled except for digitally signed and I signed it with a personal certificate. Any ideas?