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

# 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
1 Solution

Commented:
0

Commented:
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

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

Author Commented:
Thanks shanesuebsahakarn,

That worked like a charm!
0

## Featured Post

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