# 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

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

Report •

#2
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 •

#3 May 29, 2010 at 06:54:55
 Try this:Format Cell A1 & B1 as mm:ssFormat Cell C1 as [mm]:ss, the brackets are necessary.In cell A1 enter the first time as 0:15:34In cell B1 enter the second time as 0:14:32The 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:=IF(A1>B1,A1-B1,B1-A1)MIKE

Report •

Related Solutions

#4 June 1, 2010 at 14:57:08
 Hi,Here is a macro that allows you to enter times as mm.ss (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 59When 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:ssTo 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 Else 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 ErrHnd: Err.Clear '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 ```Regards

Report • 