Calculating Elapsed Time & then Converting to Decimal

[josephsimonbenn]

Hi Guys,

I'm looking for a vba code which will calculate the duration of a booking then convert it into a decimal

For Example

If i have a booking Starting on
29/12/2013 22:00

And Ending on 30/12/2013 01:00

Its 4 hours....

So I want a field to display it as 4.00 hours.

Is this possible? A Code would be wonderful. Thanks in advance

[ADezii]

Something along the lines of the following should do the trick:

Debug.Print FormatNumber(DateDiff("h", <Start Date>, <End Date>), 2) & " hours"
Debug.Print Format$(DateDiff("h", <Start Date>, <End Date>), ".00") & " hours"

[josephsimonbenn]

I've opted to do it in two functions:

Then I Call the function in my form as RoundToNearest(BookingDuration(ST, ET))

Public Function BookingDuration(ST As Date, ET As Date) As Double
 
n = DateDiff("n", CDate(ST), CDate(ET))
t = n \ 60 & Format(n Mod 60, "\:00")
 
Dim D As Date, TB, Result As Single
    'For example, it can be at a string
    D = t
    TB = Split(D, ":")
    Result = TB(0) + ((TB(1) * 100) / 60) / 100
 
BookingDuration = Result
End Function
 
Public Function RoundToNearest(Amt As Double) As Double
 
Dim remainder As Double
remainder = Amt - Int(Amt)
 
Dim Temp As Double
    Temp = Amt / 0.25
    If Int(Temp) = Temp Then
        RoundToNearest = Amt
    Else
        Select Case remainder
        Case Is <= 0.125
            remainder = 0
        Case Is <= 0.375
            remainder = 0.25
        Case Is <= 0.625
            remainder = 0.5
        Case Is <= 0.875
            remainder = 0.75
        Case Else
            remainder = 1
        End Select
    RoundToNearest = Int(Amt) + remainder
    End If
End Function

[NeoPa]

From your question, and how it is asked, the following function will do all you request :

Public Function BookingDuration(ST As Date, ET As Date) As Double
    BookingDuration = Round((ET - ST) * 24, 2)
End Function

However, looking at your own code, it seems you don't want it rounded to hundredths of an hour as indicated in the question, but rather to the nearest quarter of an hour specified in decimal fractions. That is a little (but not much) more complicated :

Public Function BookingDuration(ST As Date, ET As Date) As Double
    BookingDuration = Round((ET - ST) * 96, 0) / 4
End Function