Formatting Times (durations) in Excel

Question

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.

Answer 1

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.

Answer 2

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

Answer 3

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