Entering Times into Cells

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.

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.

IsDate doesn't work on times 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:

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

One thought on “Entering Times into Cells

  1. 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.

Leave a Reply

Your email address will not be published. Required fields are marked *