Rounding times to the nearest 15 minutes in Excel
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). |
Rounding times to the nearest 15 minutes in Excel
=ROUND(A1*96,0)/96 Should work Regards Dav -- Dav ------------------------------------------------------------------------ Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107 View this thread: http://www.excelforum.com/showthread...hreadid=544669 |
Rounding times to the nearest 15 minutes in Excel
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 -- tony h ------------------------------------------------------------------------ tony h's Profile: http://www.excelforum.com/member.php...o&userid=21074 View this thread: http://www.excelforum.com/showthread...hreadid=544669 |
Rounding times to the nearest 15 minutes in Excel
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 "BuckeyeWMV" wrote: 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). |
Rounding times to the nearest 15 minutes in Excel
=MROUND(A1,1/24/4)
Vaya con Dios, Chuck, CABGx3 "BuckeyeWMV" wrote: 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). |
Rounding times to the nearest 15 minutes in Excel
One of the great things about this site is having your preconcieved ideas challenged. Even with apparantly simple tasks. -- tony h ------------------------------------------------------------------------ tony h's Profile: http://www.excelforum.com/member.php...o&userid=21074 View this thread: http://www.excelforum.com/showthread...hreadid=544669 |
Rounding times to the nearest 15 minutes in Excel
"BuckeyeWMV" wrote in message
... 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 -- David Biddulph |
Rounding times to the nearest 15 minutes in Excel
Great answer. This is my first day on these boards and I have learned so
much. Killer response. -- Clark "Ron Coderre" wrote: 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 "BuckeyeWMV" wrote: 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). |
All times are GMT +1. The time now is 01:11 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com