• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 473
  • Last Modified:

Round up to the half hour and full hour

I have three fields in my form:

Arrival_Time
Departure_Time
Billable_Time

I want to automatically populate Billable_Time with a rounded value.

If the elapsed time is more than 5 minutes past the half hour mark, I want to round to the next hour.
example:
1 hour and 4 minutes is billed at 1 hour
1 hour and 5 minutes is billed at 1.5 hours
1 hour and 30 minutes is billed at 1.5 hours
1 hour and 33 minutes is billed at 1.5 hours
1 hour and 35 minutes is billed at 2 hours
1 hour and 50 minutes is billed at 2 hours

I've tried some very wild equations, but never get what I want in all scenarios.
Right now I'm staring at:

Private Sub Billable_time_Enter()
If ([Departure_Time] > [Arrival_Time]) Then
   n = (DateDiff("n", [Arrival_Time], [Departure_Time]) / 60)
    If n < 1 Then
       Billable_time.Value = 1
    Else
       Billable_time.Value = Round(n * 4) / 4
    End If
Else
   Billable_time.Value = (DateDiff("n", [Arrival_Time], [Departure_Time]) + (24 * 60)) / 60
End If

End Sub


This being the line that is making my head swim:
Billable_time.Value = Round(n * 4) / 4






0
jasgot
Asked:
jasgot
1 Solution
 
ecimsCommented:
0
 
IanWorcesterCommented:
Try creating 2 variables:

Hours and Minutes using datediff function

intTotalMinutes=datediff( "n",#11:30#,#12:36#)

This will give intHours=66, the number of minutes elapsed between the times(dates entered).

From this you can use:

intHours=int(intTotalMinutes/60)
intMinutes=intTotalMinutes-(intHours*60)

from this you can see if the minute variable is greater or less than 0 and less that 5, greater than 5 and less than 30, etc

And from that you should be able to conditionally add half hour/hour chunks  to the hour part.

Hop this helps
0
 
shanesuebsahakarnCommented:
Try:
 n = (DateDiff("n", [Arrival_Time], [Departure_Time])-5
 If n<1 Then n=1
 Billable_time.Value = (((n-1)\30)+1)/2
0
 
jasgotAuthor Commented:
Thanks shanesuebsahakarn,

That worked like a charm!
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now