By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,871 Members | 2,575 Online
Bytes IT Community
+ 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
Share this Question
Share on Google+
3 Replies


ADezii
Expert 5K+
P: 8,637
Something along the lines of the following should do the trick:
Expand|Select|Wrap|Line Numbers
  1. Debug.Print FormatNumber(DateDiff("h", <Start Date>, <End Date>), 2) & " hours"
  2. Debug.Print Format$(DateDiff("h", <Start Date>, <End Date>), ".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
  1. Public Function BookingDuration(ST As Date, ET As Date) As Double
  2.  
  3. n = DateDiff("n", CDate(ST), CDate(ET))
  4. t = n \ 60 & Format(n Mod 60, "\:00")
  5.  
  6. Dim D As Date, TB, Result As Single
  7.     'For example, it can be at a string
  8.     D = t
  9.     TB = Split(D, ":")
  10.     Result = TB(0) + ((TB(1) * 100) / 60) / 100
  11.  
  12. BookingDuration = Result
  13. End Function
  14.  
  15. Public Function RoundToNearest(Amt As Double) As Double
  16.  
  17. Dim remainder As Double
  18. remainder = Amt - Int(Amt)
  19.  
  20. Dim Temp As Double
  21.     Temp = Amt / 0.25
  22.     If Int(Temp) = Temp Then
  23.         RoundToNearest = Amt
  24.     Else
  25.         Select Case remainder
  26.         Case Is <= 0.125
  27.             remainder = 0
  28.         Case Is <= 0.375
  29.             remainder = 0.25
  30.         Case Is <= 0.625
  31.             remainder = 0.5
  32.         Case Is <= 0.875
  33.             remainder = 0.75
  34.         Case Else
  35.             remainder = 1
  36.         End Select
  37.     RoundToNearest = Int(Amt) + remainder
  38.     End If
  39. End Function
Dec 29 '13 #3

NeoPa
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
  1. Public Function BookingDuration(ST As Date, ET As Date) As Double
  2.     BookingDuration = Round((ET - ST) * 24, 2)
  3. 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
  1. Public Function BookingDuration(ST As Date, ET As Date) As Double
  2.     BookingDuration = Round((ET - ST) * 96, 0) / 4
  3. End Function
Dec 29 '13 #4

Post your reply

Sign in to post your reply or Sign up for a free account.