Rounding Variable Time Value Up Or Down

[Ark68]

Suppose a user enters a time value in cell C11, 8:25 PM.

In my VBA code, I use the following code to determine the hour and minute components of the entry.

        hr1 = Hour(Range("$C$11"))
        mn1 = Minute(Range("$C$11"))
        MsgBox "Hour: " & hr1 & "  Minute: " & mn1

The message box is displaying the hour as 20, which makes sense. The minute is being displayed as 25.

How do I go about rounding mn1 (minutes) up OR down to the nearest quarter hour? In this case, mn1 would change to 30.

 

[skywriter]

How about a series of select statements.

You could start out at the lowest value you want to look at that you would round down to 0 minute then go up progressively from there, I would think it would work fine.

For arguments sake:

Select Case mn1

Case Is <= 6

mn1 = 0

Case Is <= 21

mn1 = 15

Case Is <= 36

mn1 = 30

Case Is <= 51
mn1 = 45

Case Else
mn1 = 0

End Select

 

[Ark68]

Thanks so much for your suggestion. Although I didn't try it, it does look like it would work.
I opted for this after a bunch of trial and error.

mn2 = Minute(Application.WorksheetFunction.Round(Range("C$11") * 96, 0) / 96)

 

[ITWare2008]

Hi Ark68,

I'm still not comfortable with vba but you could adapt this formula to your code

=MINUTE(CEILING(MINUTE(G21)*(1/(24*60))/((1/(24*60))*15);1)*((1/(24*60))*15))

if your time is, for instance, 16:24 the above formula will retrieve 30

Hope this help.

Vândalo

 

[skywriter]

Thanks so much for your suggestion. Although I didn't try it, it does look like it would work.
I opted for this after a bunch of trial and error.

mn2 = Minute(Application.WorksheetFunction.Round(Range("C$11") * 96, 0) / 96)

Thanks for the feedback.
Did you want to round the hours up if say the minutes were 50 something?
Maybe round the minutes to 0 and add 1 to the hour?

 

[Scott Huish]

Round the time first and then break it apart.

Sub test()
Dim tm As Double
tm = Application.WorksheetFunction.Round(Range("C11") * 96, 0) / 96
MsgBox "Hour: " & Hour(tm) & " Minute: " & Minute(tm)
End Sub