# Rounding Variable Time Value Up Or Down

#### Ark68

##### Well-known Member
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.

Code:
``````        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.

### Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

#### skywriter

##### Well-known Member
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:

Code:
``````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

##### Well-known Member
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

##### Board Regular
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

##### Well-known Member
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

##### MrExcel MVP
Round the time first and then break it apart.

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

Replies
6
Views
56
Replies
1
Views
56
Replies
10
Views
197
Replies
78
Views
1K
Replies
5
Views
518