# Time Calculations

#### Freewish

##### New Member
I have a problem trying to figure out how to calculate time intervals. My data is as follows, dd/hrs/mins shown as 1:22:24 (which is 1 day,22 hours and 24 minutes. I need to be able to add and subtract them properly with a result that will show the same as the data given. Please help !

### Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
Can you post a couple examples of how you need it to work? I'm not exactly sure that I know what you are trying to do. Thanks and Good Luck!

If you select cells A1, A2, and A3 and go to Format->Cells, select the Custom format and use the format d/h/m and click OK. Then select A1 and type in 36:15 (36 being hours and 15 being minutes) it displays 1/12/15. If you then go to cell A2 and type in 48:05 (48 being hours and 5 minutes) it displays 2/0/5. Assuming this is what you have done, as long as you formatted cell A3, you can simply select A3 and type in =A2-A1 to get 0/11/50.

Does this help at all?

Vconfused- I don t see any DD/HRS/Min selection under the custom format, I am using Excel 2002. Your examples are exactly what Iam trying to calculate. Ex, The differnece between 5D/10H/15M & 6D/11H/25M would be 1D/1H/10M. We are tracking the movement at various time intervals of a vehicle into our facility. Time we are notified to tow it, to the time we dispatch it to come in to the time it arrives to the time we are finished with it and dispatch the tow out to the actual time it leaves our facility. Monthly reports are also needed to track overall time for other things. Thanks for everyones input so far.

Hi Cruizer, thanks for the reply, this function tracks betweeen two dates when you actually have a Start date and end Date. I only have time intervals in D/Hr/Min no date ! Tried looking at this formula for me to modify it to fit and I can't get my head around that one. Thanks for tryin. Still looking for an answer.

Freewish,

You can type your own custom formatting as well. I believe that is what the poster above is referring to in their response.

To actually do the date arithmetic you need, try the following:
Code:
``````Option Explicit

'Assumes Range2 is greater than Range1
Public Function Subtract2Values(Range1 As Variant, Range2 As Variant) As Variant
Dim Day1 As Long, Day2 As Long
Dim Time1 As Date, Time2 As Date
Dim Value1 As String, Value2 As String, ReturnValue As String
'Must be 2 ranges or 2 string values in format specified
If GetData(Range1, Value1, Time1, Day1) Then
If GetData(Range2, Value2, Time2, Day2) Then
Day2 = Day2 - Day1
If Time2 > Time1 Then
Time2 = Time2 - Time1
Else
Day2 = Day2 - 1
Time2 = Time1 - Time2
End If
'Prefix a space for next test
ReturnValue = Format(Day2, " 00:") & Format(Time2, "hh:mm")
ReturnValue = Replace\$(ReturnValue, " 0", vbNullString)
End If
End If
Debug.Print ReturnValue
Subtract2Values = ReturnValue
End Function 'Subtract2Values

Private Function GetData(aRange As Variant, _
ByRef aValue As String, _
ByRef aTime As Date, _
ByRef aDay As Long) As Boolean
Dim TempDate As Date
Dim TempText As String
'Must be range or string value in format specified
If TypeName(aRange) = "Range" Then
aValue = aRange.Text
ElseIf TypeName(aRange) = "String" Then
aValue = aRange.Text
ElseIf TypeName(aRange) = "Date" Then
aValue = aRange.Text
Else
Exit Function
End If
'Simple check for correct format
If InStr(aValue, ":") = 0 Then
Exit Function
ElseIf InStr(aValue, ":") = InStrRev(aValue, ":") Then
Exit Function
End If
TempText = Mid\$(aValue, InStr(aValue, ":") + 1)
On Error Resume Next
TempDate = TimeValue(TempText)
If Err.Number = 0 Then
aTime = TempDate
aDay = Val(Left\$(aValue, InStr(aValue, ":") - 1))
End If
If IsObject(aRange) Then
Else
End If
GetData = (Err.Number = 0)
Err.Clear
On Error GoTo 0
End Function 'GetData``````
Then paste this into your worksheet cell: =Subtract2Values(A4,A5). It returns a string you may need to right justify it.

sbendbuckeye

This appears to be way above my head, not sure how to set this up. This is obviously not a formula that is entered into a cell, what do I do with all this writing. I am a little lost now !!!

Hello Freewish,

Sorry, didn't mean to assume too much. Try this:

1. In your Excel file, click on Tools, Macro, Visual Basic Editor or Alt+F11 - this will open up the Visual Basic Editor

2. On the Insert menu, select Insert, Module - will insert a blank code module for you

3. Copy and paste everything between the code lines above into this module

4. Type the formula =Subtract2Values(A4,A5) into your Excel cell in the same manner you would type =Sum(A1:E1) - The function takes 2 cell addresses, it assumes the first one is the smaller and the second one is the larger

5. See if you can get that to work and then post again if you would like to watch it work

6. If all else fails, send an email to my signature and I will send an example to you

Thank you, I ll give it a shot and let you know.

Replies
5
Views
137
Replies
2
Views
530
Replies
11
Views
355
Replies
2
Views
164
Replies
3
Views
394

1,221,186
Messages
6,158,413
Members
451,492
Latest member
ichinisan123

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back