Does anyone have a good quick way of entering times, specifically durations, NOT time of day, into Excel? All of the times in this case will be short, minutes and seconds. Nothing as long as an hour, so I don't need the hour displayed, and I'd rather not have to type it every time.

I've come up with some shortcuts to streamline things a bit - My format is m:ss;@, which displays what I want, but I still have to type 0:3:14 every time.

I've created a macro so that by typing .. I get a :, which is much quicker than having to constantly alternate between the shift-key being down for the colons and up for the numbers. (This also saves time since the . is right there on the number pad so I can easily type one-handed without having to move my hand over to the : and back every time.) Forget where I saw this suggestion but it's genius.

However, I still have to type 0..3..14. It requires me to type in the hour even though it's always 0. Is there any way that I can just type in 3..14. If I do that now, I end up with 3:14:00 instead of 0:03:14. May not seem like much, but when you have to do it hundreds of times, every little bit helps. (I tried cheating and pretending the hours and minutes were minutes and seconds, but where this fails is when I want to add up all the durations on the bottom - and the total will be in hours, even though the individual times are much shorter.

  • Okay, both answers look good, but I'm at a loss, because I'm not sure where in Excel you go to even write code like that. The only macros I know about are the ones that just replace what you typed with something else, a la .. => :. – Darrel Hoffman Jul 13 '13 at 15:05

You can use a change event to capture the data entry and convert the Hour:Minute value into a Minute:Second value by dividing by 60.

For example, all values entered in column A will be entered as xx:yy and the macro will convert them to be interpreted as mm:ss, instead of hh:mm. One cell in column A has the range name "totals". This cell will be excluded from the automatic conversion.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A:A")) Is Nothing Then
On Error Resume Next
    If Not Target.Name.Name = "totals" Then
        Application.EnableEvents = False
        Target = Target / 60
        Application.EnableEvents = True
    End If
End If
End Sub

Adjust the range to your needs.

| improve this answer | |
  • I think this'll do what I need, if I knew where to put it. One thing I've never done in Excel is write out code like this - I can't even find where you go to do that. Seems well-hidden. – Darrel Hoffman Jul 13 '13 at 15:09
  • @DarrelHoffman: A simpler variant of the same concept: define B1 to be =A1/60; then drag/fill down and do all your computations on the B’s. – Scott Jul 13 '13 at 20:53
  • Copy the code above. Right-click the sheet tab, select "View Code" to open the Visual Basic Editor (VBE). Paste the code into the big code window. Close the VBE. – teylyn Jul 14 '13 at 0:16
  • @teylyn That did it. I had to change it to a macro-enabled .xlsm instead of .xlsx as well. (Annoying that there's two different definitions for "macro". Makes talking about it very confusing. I had a keyboard shortcut I mentioned that's also called a "macro", but that's completely different.) – Darrel Hoffman Jul 14 '13 at 1:52
  • @Scott Simple and effective, yes, but it does require an extra column to be maintained for data entry. Actually 6 extra columns in my case, since there's 6 total columns with times in them. But it might be a good trick in another scenario. – Darrel Hoffman Jul 14 '13 at 1:54

See if the following macro works for you. It expects entries in the form:


In other words, just numbers. 59 seconds would be entered as 59, 1 min and 21 seconds would be entered as 121, etc.

When you finish the data entry, select the entered values and run the macro. It will convert the entries into time values.

Sub NumToTime()
    Dim cell As Variant
    Dim hr As Long
    Dim min As Long
    Dim sec As Long
    hr = 0
    For Each cell In Selection
        min = Int(cell.Value / 100)
        sec = cell.Value - (min * 100)
        cell.Value = TimeSerial(hr, min, sec)
End Sub
| improve this answer | |
  • Looks good, but I'll probably go with @teylyn's solution, because times will need to be continually added on a regular basis, and this code seems to be a run-once kind of thing. – Darrel Hoffman Jul 13 '13 at 15:08
  • Sounds good. Code can actually be run whenever needed (I'd set it up with a hot-key), but do what makes the most sense for you. Cheers! – chuff Jul 13 '13 at 15:24
  • Why bother with the IF-THEN-ELSE? The “ELSE” clause works in both cases. – Scott Jul 13 '13 at 20:50

'You could replace your old macro (the other type of Excel macro) with the following VBA (Visual Basic for Applications) code that handles the replacement of the double dots with colons.

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.Cells.Count <> 1 Then Exit Sub 'Only check when one cell is being changed.

   Select Case UBound(Split(Target.Formula, "..")) 'How many ".." appear in the formula?
      'UBound returns 1 less than the number of elements in an array because array indexes start at 0, eg: array(0), array(1)

      Case 1 'ie only two values entered, minutes & seconds (so add the hours)
         Target.Formula = "0:" & Replace(Target.Formula, "..", ":")
         Target.NumberFormat = "[m]:ss" 'Optional formatting to show duration in minutes

      Case 2 'ie three values entered, hours, minutes, seconds, just replace the double dots with colons
         Target.Formula = Replace(Target.Formula, "..", ":")
         Target.NumberFormat = "[h]:mm:ss" 'Optional formatting to show duration in hours
   End Select
End Sub

This code assumes one set of double dots means minutes and seconds (1..1 -> 0:01:01) instead of Excel's default hours and minutes.

It also formats the cells automatically based on what was entered. Note the [ ] around the m or h which tells Excel to display the total duration of minutes. Without the [ ], Excel only displays the minutes within the hours, or the hours within the day (ie up to a maximum of 59 minutes or 23 hours). If the value is greater than those maximums, the format will ignore anything extra. Using the [ ] forces Excel to display the total number of minutes or hours etc (eg 65 minutes would show as 5:00 with m:ss or 1:05:00 with h:mm:ss but shows as 65:00 using [m]:ss).

PS If you need this to work on ALL worksheets, you'll need to add the code to the ThisWorkbook module inside a Sub with this definition:

 Private Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

Hope this helps...

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.