434,871 Members | 2,575 Online + Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,871 IT Pros & Developers. It's quick & easy.

Calculating Elapsed Time & then Converting to Decimal

 P: 32 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 Dec 29 '13 #1
3 Replies

 Expert 5K+ P: 8,637 Something along the lines of the following should do the trick: Expand|Select|Wrap|Line Numbers Debug.Print FormatNumber(DateDiff("h", ), 2) & " hours" Debug.Print Format\$(DateDiff("h", ), ".00") & " hours" Dec 29 '13 #2

 P: 32 I've opted to do it in two functions: Then I Call the function in my form as RoundToNearest(BookingDuration(ST, ET)) Expand|Select|Wrap|Line Numbers 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 Dec 29 '13 #3

 Expert Mod 15k+ P: 31,489 From your question, and how it is asked, the following function will do all you request : Expand|Select|Wrap|Line Numbers 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 : Expand|Select|Wrap|Line Numbers Public Function BookingDuration(ST As Date, ET As Date) As Double     BookingDuration = Round((ET - ST) * 96, 0) / 4 End Function Dec 29 '13 #4 