Time Calculations

Freewish

New Member
Joined
Jan 26, 2005
Messages
16
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 ! :cry:
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
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!
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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. :cry:
 
Upvote 0
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
        Debug.Print aRange.Address, aValue, aTime, aDay
    Else
        Debug.Print aRange, aValue, aTime, aDay
    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.
 
Upvote 0
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 !!! :oops:
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,532
Messages
6,114,177
Members
448,554
Latest member
Gleisner2

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top