I have a half-dozen things in my UIHelpers.xlam add-in that I really miss when I’m using someone else’s machine. One of them is this little gem from 2009: Entering Dates. When I have to actually type a date in a cell, my fingers get very angry with me.
I don’t work with times that often, but I’m doing a technical proofread of a book and it means I’m entering stop and start times several times a day (to keep track of how little I’m getting paid on an hourly basis). I enter times like 17:00 and that works well enough. But it’s not keyboard-sexy like my date entry is. So I set about fixing that.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 |
Sub IncrementDate() On Error Resume Next If IsDate(ActiveCell.Value) Then ActiveCell.Value = ActiveCell.Value + 1 ElseIf IsTextTime(ActiveCell.text) Then ActiveCell.Value = ActiveCell.Value + 1 / 24 / 60 Else ActiveCell.Value = Date ActiveCell.NumberFormat = "m/d/yyyy" End If End Sub Sub DecrementDate() On Error Resume Next If IsDate(ActiveCell.Value) Then ActiveCell.Value = ActiveCell.Value - 1 ElseIf IsTextTime(ActiveCell.text) Then ActiveCell.Value = ActiveCell.Value - 1 / 24 / 60 Else ActiveCell.Value = Now - Int(Now) ActiveCell.NumberFormat = "h:mm" End If End Sub Public Function IsTextTime(sInput As String) As Boolean IsTextTime = IsDate(sInput) End Function |
My first problem was determining if the cell contained a time. The internet says IsDate works equally well for dates and times, and that’s probably true for VB6, but it’s not the case for VBA in Excel.
I read a post somewhere by Andy Pope that said IsDate will return True on the Text property of cell. If I was going to make an IsTime function, it would look like this:
|
1 2 3 4 5 |
Public Function IsTime(rCell As Range) As Boolean IsTime = IsDate(rCell.Text) And Not IsDate(rCell.Value) End Function |
I haven’t tested that extensively but I think it works. I generally don’t like passing ranges into functions that should only require a value. But since I need both Text and Value, it can’t be helped. In the code above, I made a special purpose function that takes a String because I already test for IsDate(.Value) in the calling procedure. It’s a cop out and I don’t like it, but here we are.
In the end, I’m able to control times similarly to how I control dates. Here’s how it works.
| Cell is blank | Cell is not a date | Cell is a time | Cell is a date | |
| Ctrl+; | Enter today | Replace contents with today | Increase time by 1 minute | Increase date by one day |
| Ctrl+Shft+; | Enter current time | Replace contents with current time | Descrease time by 1 minute | Decreate date by one day |
Interesting. The sheet data parser converts time entries to vbDouble and date entries to vbDate.
I think it’s a bug….but has probably been that way for quite some time.