Rounding Time - 15-Minute Increments

[Justinian]

I have a massive spreadsheet of actual time punches and I need them to be rounded to increment of 15 minutes. The rule is that if a punch has to be within 7 minutes or less of the 15-minute increment so 6:37 pm becomes 6:30 pm while 6:38 pm becomes 6:45 pm; 6:01 pm becomes 6:00 pm while 6:08 pm becomes 6:15 pm.

How do I do that?

 

[jasonb75]

Depends if you want to keep the original times for reference or overwrite them.

Keeping the original times, you can use a formula =MROUND(A2,1/96) where A2 contains a punch time.

Overwriting the original times will require the use of vba.

 

[Justinian]

Yes, that works!

Can you explain what the "1/96" does so I can understand the formula?

 

[jasonb75]

If you divide 1 day by 96, you get 15 minutes.

 

[Justinian]

I understand.

Thank you so much!