VBA round up & down by 7 minutes on time

[Martines91]

Hi all,

I tried to google search this macro but I cannot find anything close to what I want:

So I have a workbook for my clock-ins and outs, and from next month we're changing format and instead of rounding up&down by 15 minutes as it was until now, it will be rounded by 7. It was easy to calculate but now I just don't wanna bother with it each day.

So clock-ins and outs are going to be rounded the same way:

If I clock-in / out @ 7:07 or 6:53 it will go to 7:00, but if I clock @ 6:52 that will go to 6:45 and if @ 7:08 that will go to 7:15

Hope this is clear enough,

Cell range is: B3:C33

Thanks

[JieJenn]

I broke down the the process in 3 steps. Get the minutes value first, then get the rounding, and finally, get the difference. See attached since it is a bit hard to explain the math.

[Martines91]

Yes that's exactly what I want, but instead of having multiple cells I just want to type the value to the cell and have it automatically corrected via VBA macro

[MrShorty]

Since VBA's only built in rounding function is the Round() function (which does basic bankers rounding to the nearest decimal place), I would probably use Excel's MROUND() function for this.

What role does VBA play in the project? Since this is a fairly simple built in Excel function, I would probably do the whole thing in Excel and not even use VBA. If my date/time value (not text) is in A1, an MROUND() function like =MROUND(A1,7/60/24) would round that time to the nearest 7 minute increment (always rounding 3.5 minute up). The 7/60/24 converts 7 minutes to fraction of a day (Excel's base unit for storing date/time values).

If VBA is essential to the task, the MROUND() function can be called from VBA: https://docs.microsoft.com/en-us/off...n-visual-basic

[JieJenn]

I already laid out the logic for you, you should be able to take the logic put in the VBA code. Can you show us what have you done with VBA so far?

[Olly]

With unadjusted time in B3, rounded time:

Please Login or Register  to view this content.

[davsth]

=MROUND(b3,1/96)

round to a 96th of a day = 15 minutes, which is actually what you are rounding to (the nearest 15 minutes)

[Olly]

As a VBA function:

Please Login or Register  to view this content.

And to change as you type:

Please Login or Register  to view this content.

[Martines91]

As a VBA function:

Please Login or Register  to view this content.

And to change as you type:

Please Login or Register  to view this content.

This works perfectly! Thank you