Rounding Date and Time in VBA

Question

How can I round off date and time in excel using VBA?

For example, the user selects the value from the calendar which is copied in Cell A6 = "08/25/2016 09:02:00"

I am pulling the data in 15 minutes interval so I want it to be A6 = "08/25/2016 09:00:00"

So if the user selects any date and time that is not in multiple of 15 minutes, it should go back to the previous 15 minute interval value and pull the data.

Answer 1

Pull out the minutes, floor the date portion to get rid of the time, then add it back by building it with TimeSerial:

Private Sub Example()
    Dim foo As Date
    foo = CDate("08/25/2016 09:02:00")

    Dim minutes As Long
    minutes = Minute(foo)
    minutes = 15 * (minutes \ 15)  'Round down by 15 minute increments

    foo = Int(foo) + TimeSerial(Hour(foo), minutes, 0)
    Debug.Print foo
End Sub

Edit: Like @Pekka mentions, this can be done with a worksheet formula too - this is the equivalent to the code VBA above:

=INT(A6)+TIME(HOUR(A6),INT(MINUTE(A6) / 15) * 15, 0)

Answer 2

VBA is not necessary. This can be done directly in Excel. =FLOOR(A6,TIME(0,15,0)) will truncate a date time value to the previous 15 minute value.

Excel represents date values as a floating point value since an initial date (around 1900, depending on version) with the time as the fractional portion of the value.

You could, of course, use the same expression in VBA code in the same way.

As Jeeped comments, this is a more self-documenting alternative to the more direct expression =int(A6*24*4)/4/24 initially suggested.