Rounding Variable Time Value Up Or Down

Ark68

Well-known Member
Joined
Mar 23, 2004
Messages
3,671
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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.
 

Some videos you may like

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
Joined
Feb 15, 2014
Messages
1,642
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
Joined
Mar 23, 2004
Messages
3,671
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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
Joined
Apr 16, 2010
Messages
174
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
Joined
Feb 15, 2014
Messages
1,642
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
Joined
Mar 17, 2004
Messages
19,955
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,108,993
Messages
5,526,114
Members
409,685
Latest member
Bellybb

This Week's Hot Topics

Top