Subtract a time from a time and get the number of hours different 1

[KirkJewell]

I have a StartTime and EndTime and I need to know the difference in hours (preferable 6 1/2 hours as 6.5

 

[sixsmithc]

you can use the datediff() function

 

[Paladyr]

I need to know how to do this and it doesn't appear as if it is possible to use the datediff() function. Can anyone provide code for how to do this?

 

[strongm]

Dim StartTime As Date
Dim EndTime As Date

' Just some example times
StartTime = "13:30"
EndTime = "20:00"

' Work in minutes and divide by 60 to get x.y result
Debug.Print DateDiff("n", StartTime, EndTime) / 60

 

[Paladyr]

Thanks! I figured out what was wrong, I had an h instead of "h" as the first arguement .

 

[MichaelRed]

I think a half of a loaf is best here. The sample returns hours and minutes. You could do the other half of the loaf (make the minutes part a decimal of the hour). If you really want to learn and contribute, the student exercise would be to include another calling parameter to designate the return format to have the minutes as a 'decimal part' of the hour(s). And to then post your resullts back here.

? basDbl2HrsMin(#11:27 AM# - #8:12 AM#)
3Hrs 15Mins

Public Function basDbl2HrsMin(AccumTime As Double) As String

    Dim tmpDays As Double
    Dim tmpHrs As Double
    Dim tmpMins As Double
    Dim tmpTime As Double
    Dim tmpStr As String

    tmpDays = Int(AccumTime)
    tmpTime = (AccumTime - tmpDays) * 1440      'Fraction to Mins

    tmpMins = tmpTime Mod 60
    tmpTime = tmpTime - Int(tmpMins)
    tmpHrs = tmpTime \ 60

    'formatting for Day(s)
    If (tmpDays) Then
        tmpStr = tmpDays & "Day"
        If (tmpDays > 1) Then
            tmpStr = tmpStr & "s"
        End If
    End If

    'formatting for Hour(s)
    If (tmpHrs) Then
        tmpStr = tmpStr & " " & tmpHrs & "Hr"
        If (tmpHrs > 1) Then
            tmpStr = tmpStr & "s"
        End If
    End If

    'formatting for Minutes(s)
    If (tmpMins) Then
        tmpStr = tmpStr & " " & tmpMins & "Min"
        If (tmpMins > 1) Then
            tmpStr = tmpStr & "s"
        End If
    End If


    basDbl2HrsMin = tmpStr
    

End Function
[code] MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[strongm]

Michael,

Try your routine with #8:12:00 AM# - #11:27:00 AM# as a parameter, and see if you agree that the result is what you'd expect.

Of course there are a variety of ways of trying to achieve what you set out to do. Here's one (which I'm almost certain works, but it's 02:00 in the morning here and I've been drinking beer again):
[color][tt]
Public Function Shorter(StartDate, EndDate) As String
Dim Days As Long
Dim Hours As Long
Dim Mins As Long
Dim Secs As Long

Days = DateDiff("d", StartDate, EndDate)
StartDate = DateAdd("d", Days, StartDate)
Hours = DateDiff("h", StartDate, EndDate)
StartDate = DateAdd("h", Hours, StartDate)
Mins = DateDiff("n", StartDate, EndDate)
StartDate = DateAdd("n", Mins, StartDate)
Secs = DateDiff("s", StartDate, EndDate)
Shorter = Days & "Days " & Hours & "Hrs " & Mins & "Mins " & Secs & "Secs"
End Function
[/color][/tt]

 

[MichaelRed]

strongm,

WOW. I guess it is my turn to learn. The GROSS problem is 'easy' - if somewhat disconcerting, as seen here:
'____________________________________________________

? int(AccumTime)
-1

? CInt(AccumTime)
0

? AccumTime
-0.135416666666667

'____________________________________________________


Subtitled "Oh what a difference a day makes!"

This is behaviour which I have certainly "Learned" anew this day. This is 'documented' in the VB help, but as usual, one needs to READ carefully to get he specific behaviour - and even then it is not immediatly obvious to me. Int and Fix both TRUNCATE the value to an integer, whereas CInt ROUNDS the value. Int and Fix are ~'equivalent' for positive values, but opposite for negative values in which way they truncate? Int returns the next negative integer less than or equal to the value, while Fix retrns the negative integer Greater than or equal to the value, CInt ROUNDS the value!

Thanks for pointing out the error. Modified code to "correct" this is below. If there are additional errors, let me know.

Your approach is not one I would generally use, as the DateDiff function (used 6 times) is somewhat time consuming, which I would generally avoid, so 'fixing' any problems in the arithmitic soloution is useful for me.

Public Function basDbl2HrsMin(AccumTime As Double) As String

    Dim tmpDays As Double
    Dim tmpHrs As Double
    Dim tmpMins As Double
    Dim tmpTime As Double
    Dim tmpStr As String
    Dim SignOfTheTimes As Integer

    SignOfTheTimes = Sgn(AccumTime)
    tmpDays = Int(Abs(AccumTime))
    tmpTime = Abs((AccumTime - tmpDays)) * 1440      'Fraction to Mins

    tmpMins = tmpTime Mod 60
    tmpTime = tmpTime - Int(tmpMins)
    tmpHrs = tmpTime \ 60

    'formatting for Day(s)
    If (tmpDays) Then
        tmpStr = tmpDays & "Day"
        If (tmpDays > 1) Then
            tmpStr = tmpStr & "s"
        End If
    End If

    'formatting for Hour(s)
    If (tmpHrs) Then
        tmpStr = tmpStr & " " & tmpHrs & "Hr"
        If (tmpHrs > 1) Then
            tmpStr = tmpStr & "s"
        End If
    End If

    'formatting for Minutes(s)
    If (tmpMins) Then
        tmpStr = tmpStr & " " & tmpMins & "Min"
        If (tmpMins > 1) Then
            tmpStr = tmpStr & "s"
        End If
    End If

    'Accounting for the possibility of negative time
    If (SignOfTheTimes <> 0) Then
        tmpStr = &quot;Minus &quot; & tmpStr
    End If

    basDbl2HrsMin = tmpStr

End Function

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[MichaelRed]

strongm,

Almost forgot. Star to you.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over