| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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). |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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). |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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). |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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). |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Excel should be able to format 12-hour times without am/pm | Excel Discussion (Misc queries) | |||
| Excel is not rounding properly. | Excel Discussion (Misc queries) | |||
| Rounding off numbers in Excel 2003 | Excel Worksheet Functions | |||
| How do i enter negative times in Excel & how can they be 'fiddled. | Excel Discussion (Misc queries) | |||
| Rounding numbers to the nearest thousand | Excel Discussion (Misc queries) | |||
Linear Mode
