Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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.

share|improve this question
    
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

2 Answers 2

up vote 2 down vote accepted

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.

share|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:

314   
852   
  3   

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)
    Next
End Sub
share|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
    
Good point. Thanks, @Scott. –  chuff Jul 13 '13 at 21:18

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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