Rounding times to the nearest 15 minutes in Excel

[Guest]

How do I round times to the nearest 15 minutes. I have a time in cell A1
(7:53) and would like the rounded time to be in B2 (rounded to 8:00).

 

[Dav]

=ROUND(A1*96,0)/96

Should work

Regards

Dav

 

[Ardus Petus]

=ROUND(((A1)/"0:15"),0)*"0:15"

Cheers

 

[tony h]

try
=INT((A1+0.005208)/0.01041666666667)*0.01041666666667

note that this rounds up or down based on above or below a seven minute
interval

 

[Guest]

Try one of these:

For a time in A1

This one rounds to the NEAREST multiple of 15 minutes
B1: =MROUND(A1,1/24/4)

If this function is not available, and returns the #NAME? error, install and
load the Analysis ToolPak add-in.

or
This one does the same thing as the MROUND function, but doesn't need the
ATP installed:
B1: =ROUND(A1*(24*4),0)/(24*4)

or
This one rounds UP to the NEXT multiple of 15 minutes
B1: =CEILING(A1,1/24/4)

Note:
24*4 equals the number of 15 min intervals in a day
1/24/4 equals on fourth of one 24th of a day

Does that help?
***********
Regards,
Ron

XL2002, WinXP

 

[Guest]

=MROUND(A1,1/24/4)

Vaya con Dios,
Chuck, CABGx3

 

[tony h]

One of the great things about this site is having your preconcieve
ideas challenged. Even with apparantly simple tasks

 

[David Biddulph]

How do I round times to the nearest 15 minutes. I have a time in cell A1
(7:53) and would like the rounded time to be in B2 (rounded to 8:00).

=MROUND(A11,1/(24*4))
and format as h:mm

Note that MROUND needs the Analysis ToolPak

 

[Guest]

Great answer. This is my first day on these boards and I have learned so
much. Killer response.