If you’re writing a scheduling application, you may need to round a time to a specified number of minutes. For example, you may need to find the nearest 5-, 10-, 15-, 20-, or 30-minute interval. The solution isn’t trivial, and the code shown in Listing 2.17 takes care of this problem.
To call dhRoundTime(), pass it a date/time value and an interval to round to. (You must use any divisor of 60, but you’ll most likely use 5, 10, 15, 20, 30, or 60.) For example:
? dhRoundTime(#12:32:15#, 5)returns
12:30:00 PMand
? dhRoundTime(#12:32:35#, 5)returns
12:35:00 PMIf you pass dhRoundTime a full date and time value, it will preserve the date portion and just modify the time part.
Listing 2.17: Round Time Values to the Nearest Interval
Function dhRoundTime(dtmTime As Date, intInterval As Integer) _
As Date
' Round the time value in varTime to the nearest minute
' interval in intInterval
Dim intTime As Integer
Dim sglTime As Single
Dim intHour As Integer
Dim intMinute As Integer
Dim lngdate As Long
' Get the date portion of the date/time value
lngdate = DateValue(dtmTime)
' Get the time portion as a number like 11.5 for 11:30.
sglTime = TimeValue(dtmTime) * 24
' Get the hour and store it away. Int truncates,
' CInt rounds, so use Int.
intHour = Int(sglTime)
' Get the number of minutes, and then round to the nearest
' occurrence of the interval specified.
intMinute = CInt((sglTime - intHour) * 60)
intMinute = CInt(intMinute / intInterval) * intInterval
' Build back up the original date/time value,
' rounded to the nearest interval.
dhRoundTime = CDate(lngdate + _
((intHour + intMinute / 60) / 24))
End FunctionThis procedure is probably the most complex in this chapter, at least in terms of the calculations it performs. Its first step is to store away the date portion of the original date/time value so it can preserve the value, which will never be altered by the function:
' Get the date portion of the date/time value
lngdate = DateValue(dtmTime)Next, the procedure retrieves the time portion of the parameter and converts it into a decimal number, multiplying the value by 24:
' Get the time portion as a number like 11.5 for 11:30.
sglTime = TimeValue(dtmTime) * 24Because the time portion of a date/time value is the fraction of a full day represented by the time, taking a value representing 12:32:15 p.m. (0.522395833333333) and multiplying it by 24 will result in the value 12.5375. Once you have the time in a format like that, you can round it as needed.
Once the function knows the time, it can tuck away the hour portion, because that value will also never change.
' Get the hour and store it away. Int truncates,
' CInt rounds, so use Int.
intHour = Int(sglTime)The next step is to pull off just the fractional portion (representing the minutes) and multiply by 60 to find the number of minutes involved. Using the example of 12.5375, multiplying the fractional part by 60 and converting to an integer would return 32, which is the number of minutes involved:
intMinute = CInt((sglTime - intHour) * 60)The crucial step involves rounding the number of minutes to the correct interval:
intMinute = CInt(intMinute / intInterval) * intIntervalOnce you’ve rounded the value, the final step is to reconstruct the full date/time value. The following line of code adds the hour portion to the minute portion divided by 60, divides the entire time portion by 24 to convert to the appropriate fraction, adds the result to the preserved date value, and returns the entire value:
dhRoundTime = CDate(lngdate + ((intHour + intMinute / 60) / 24))You may find it useful to single-step through this procedure, checking the value of various variables as it runs. Try calling dhRoundTime from the Immediate window, passing in various times and divisors of 60 as intervals. Once you get the hang of what dhRoundTime is doing, you’ll find it useful in many applications that involve time and scheduling.