Averaging Time

[Jim]

Hello:

I have data that looks like the following:
3:09:15
22:45:07
17:39:10
17:39:10
18:52:47
8:39:35

I would like to learn a formula that will round the time to the top and
bottom of the hour using the 15 and 45 as the break point. For example
3:09:15 to 3:00. 17:39:10 to 17:30.

Thanks for the hedlp.
Jim

 

[David Biddulph]

=MROUND(A18,TIME(,30,)) and format the cell accordingly.

If you have problems with the MROUND function, look it up in Excel help.

 

[bapeltzer]

If your time is iin cell A1, use the formula =round(a1*48,0)/48
The reasoning is that you're asking to round to the nearest 1/2 hour. To
Excel, 1 is a day, so a 1/2 hour is 1/48.