Finding difference between two times

May 28, 2010 at 15:57:25
Specs: Windows 7
I am trying to create a spreadsheet that tracks increases or decreases in reps, times and scores for the Army Physical Fitness Test. The only problem I am having is finding the difference between two, 2 mile run times. I want to input the two times as mm:ss and get a result of the difference in the same format whether it is an increase or decrease in time.

See More: Finding difference between two times

Report •

May 28, 2010 at 17:17:55
Doing Date & Time math can be tricky, some examples of your expected input and results would be helpful.

But as a general rule, if your using just mm:ss,
enter a leading zero, so for 10 min 30 sec you should enter 0:10:30 and the result will be 10:30.
To get a result in the same format you use the format [mm]:ss in your totals.


Report •

May 28, 2010 at 20:14:07
Lets say I have a Soldier that runs 2 miles in 15:34 (mm:ss), a month later he runs 2 miles in 14:32. I want to be able to track the difference in time, but I want the outcome to be in minutes and seconds. I have been trying to find a solution for a few days now. It looks like I might have to leave the solution as a manual input. I was just hoping excel would do it for me but it handles times as a time of day rather than just an amount of time.

Report •

May 29, 2010 at 06:54:55
Try this:

Format Cell A1 & B1 as mm:ss
Format Cell C1 as [mm]:ss, the brackets are necessary.

In cell A1 enter the first time as 0:15:34
In cell B1 enter the second time as 0:14:32

The leading zeros are necessary even thought they will not be used.

In cell C1 enter the formula =A1-B1, you should get the result of 01:02.

If your not sure which time will be the higher, then something like this formula should work:



Report •

Related Solutions

June 1, 2010 at 14:57:08

Here is a macro that allows you to enter times as (using a decimal point) and no need to enter 0 for hours.

The macro is driven by the cell change event, so that when you enter a decimal value it converts the value to minutes and seconds (only in the selected time entry columns).

The macro is written on the basis that the first time and the second time are in adjacent columns, and the difference is in the third column.

The column used for the first time can be changed in the macro at this line:

'set column for first time entry (A=1, B=2 etc.)
intFirst = 2

The macro returns an error value (#VALUE) if either the minutes or the seconds exceed 59

When the second time is entered, assuming that the first time is already present, the difference is calculated.
As Excel will not allow negative times, the time difference is stored as text, either mm:ss or -mm:ss

To use the macro, go to the worksheet used for time entries. Right-click the worksheet name Tab and select 'View code'
Enter the following in the Visual Basic window that opens:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
'stop changes made by this code, re-triggering it
Application.EnableEvents = False

On Error GoTo ErrHnd

Dim intFirst As Integer
Dim intSecond As Integer
Dim dblTime As Double
Dim dblTime1 As Double

'set column for first time entry (A=1, B=2 etc.)
intFirst = 2

'second time column
intSecond = intFirst + 1

'test if data entry is into selected columns
If Target.Column = intFirst Or Target.Column = intSecond Then
    'max values allowed=59.59
    If Int(Target.Value) > 59 Or _
            Round((Target.Value - Int(Target.Value)), 2) > 0.59 Then
        'return #Value error
        Target.Value = CVErr(xlErrValue)
        're-enable events
        Application.EnableEvents = True
        Exit Sub
    End If
    'convert decimal to time format (hh:mm:ss)
    Target.Value = "00:" & CStr(Int(Target.Value)) & ":" & _
                CStr(100 * (Target.Value - Int(Target.Value)))
    'format the cell to show minutes and seconds only
    Target.NumberFormat = "mm:ss"
    'calculate the difference
    If Target.Column = intSecond Then
        'check that first time entry isn't empty
        If Target.Offset(0, -1) <> "" Then
            'calculate difference - always positive
            dblTime = Abs(Target.Offset(0, -1).Value - Target.Value)
            'put difference in next column
            Target.Offset(0, 1).Value = dblTime
            'format as minutes and seconds
            Target.Offset(0, 1).NumberFormat = "mm:ss"
            'convert result into text with minus sign if necessary
            If Target.Offset(0, -1) < Target.Value Then
                Target.Offset(0, 1).Value = _
                    "'" & Target.Offset(0, 1).Text
                Target.Offset(0, 1).Value = _
                    "'-" & Target.Offset(0, 1).Text
            End If
        End If
    End If
End If
're-enable events
Application.EnableEvents = True
Exit Sub

'error handler
're-enable events
Application.EnableEvents = True
End Sub

Change the first time column if necessary (the above example uses column B for the first time entry) and save the macro from the VB File menu. Use Alt+f11 to return to the main Excel window.

Note that entering a first time when a second time is already present does not result in the time difference being recalculated - this could be added if required.

Here is a sample of the output:

	B		C		D
	1st time	2nd time	Difference
2	19:01		18:45		-00:16
3	18:22		17:39		-00:43
4	18:31		19:30		00:59


Report •

Ask Question