| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Round time to nearest 15 minutes in decimal format
Trying to calculate time and round to nearest 15 minutes. Example: C2 11:46 AM D2 4:30 PM E2=D2-C2 this gives me hours and minutes format of 4:44 I would like the result to be rounded to the nearest 15 minutes in decimal format. (not rounded up or down each time, but to the nearest 15 minute interval) In this example I would like the result to be 4.75 Any help is appreciated. Thank you |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Round time to nearest 15 minutes in decimal format
Try this:
=ROUND((D2-C2)*96,0)/96*24 Or, if the times might span past midnight: C2 = 7:00 PM D2 = 1:00 AM =ROUND(MOD(D2-C2,1)*96,0)/96*24 Format as General or Number -- Biff Microsoft Excel MVP "dalmom" wrote in message ... Trying to calculate time and round to nearest 15 minutes. Example: C2 11:46 AM D2 4:30 PM E2=D2-C2 this gives me hours and minutes format of 4:44 I would like the result to be rounded to the nearest 15 minutes in decimal format. (not rounded up or down each time, but to the nearest 15 minute interval) In this example I would like the result to be 4.75 Any help is appreciated. Thank you |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Round time to nearest 15 minutes in decimal format
Perfect!
Thank you very much! "T. Valko" wrote: Try this: =ROUND((D2-C2)*96,0)/96*24 Or, if the times might span past midnight: C2 = 7:00 PM D2 = 1:00 AM =ROUND(MOD(D2-C2,1)*96,0)/96*24 Format as General or Number -- Biff Microsoft Excel MVP "dalmom" wrote in message ... Trying to calculate time and round to nearest 15 minutes. Example: C2 11:46 AM D2 4:30 PM E2=D2-C2 this gives me hours and minutes format of 4:44 I would like the result to be rounded to the nearest 15 minutes in decimal format. (not rounded up or down each time, but to the nearest 15 minute interval) In this example I would like the result to be 4.75 Any help is appreciated. Thank you |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Round time to nearest 15 minutes in decimal format
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "dalmom" wrote in message ... Perfect! Thank you very much! "T. Valko" wrote: Try this: =ROUND((D2-C2)*96,0)/96*24 Or, if the times might span past midnight: C2 = 7:00 PM D2 = 1:00 AM =ROUND(MOD(D2-C2,1)*96,0)/96*24 Format as General or Number -- Biff Microsoft Excel MVP "dalmom" wrote in message ... Trying to calculate time and round to nearest 15 minutes. Example: C2 11:46 AM D2 4:30 PM E2=D2-C2 this gives me hours and minutes format of 4:44 I would like the result to be rounded to the nearest 15 minutes in decimal format. (not rounded up or down each time, but to the nearest 15 minute interval) In this example I would like the result to be 4.75 Any help is appreciated. Thank you |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Round time to nearest 15 minutes in decimal format
Ironically, I had the exact same question today - one day after your posts.
Thank you! "T. Valko" wrote: Try this: =ROUND((D2-C2)*96,0)/96*24 Or, if the times might span past midnight: C2 = 7:00 PM D2 = 1:00 AM =ROUND(MOD(D2-C2,1)*96,0)/96*24 Format as General or Number -- Biff Microsoft Excel MVP "dalmom" wrote in message ... Trying to calculate time and round to nearest 15 minutes. Example: C2 11:46 AM D2 4:30 PM E2=D2-C2 this gives me hours and minutes format of 4:44 I would like the result to be rounded to the nearest 15 minutes in decimal format. (not rounded up or down each time, but to the nearest 15 minute interval) In this example I would like the result to be 4.75 Any help is appreciated. Thank you |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do I round time down to the nearest half hour? | Excel Worksheet Functions | |||
| Round/Display (HR:MN:SEC) data in decimal HR format for Time C | Excel Discussion (Misc queries) | |||
| Round time up to nearest :15 | Excel Discussion (Misc queries) | |||
| How do I round time to the nearest quarter of an hour | New Users to Excel | |||
| Round time to nearest quarter hr | Excel Worksheet Functions | |||
Linear Mode
