Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculating Hours
I need to compute the difference between to times to the nearest quarter of
an hour. For example, if someone enters 12:30 PM and 1:40 PM, I need to return the number 5 (because there are 5 - 15 min increments). How do I do this? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculating Hours
=(End Time - Start time)*96
an hour is 1/24th So, subtracting a time from another time will provide .041667 for each hour. You'd multiply by 24 to get 1 for each hour. Sicne you want by the 15 minute increment, multiply the 24 by 4, getting 96... ta da! "Ruthie" wrote: I need to compute the difference between to times to the nearest quarter of an hour. For example, if someone enters 12:30 PM and 1:40 PM, I need to return the number 5 (because there are 5 - 15 min increments). How do I do this? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculating Hours
Try this:
=INT((B1-A1)*96)+1 where A1 contains the start time and B1 the finish time in Excel time format. Hope this helps. Pete On Oct 1, 6:26*pm, Ruthie wrote: I need to compute the difference between to times to the nearest quarter of an hour. For example, if someone enters 12:30 PM and 1:40 PM, I need to return the number 5 (because there are 5 - 15 min increments). *How do I do this? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculating Hours
.... or more likely =ROUND((B1-A1)*96,0) ?
Pete's formula would return 5 for a 1:00 or 1:05 difference, instead of 4 which I would regard as being "to the nearest quarter of an hour". -- David "Pete_UK" wrote in message ... Try this: =INT((B1-A1)*96)+1 where A1 contains the start time and B1 the finish time in Excel time format. Hope this helps. Pete On Oct 1, 6:26 pm, Ruthie wrote: I need to compute the difference between to times to the nearest quarter of an hour. For example, if someone enters 12:30 PM and 1:40 PM, I need to return the number 5 (because there are 5 - 15 min increments). How do I do this? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculating hours | Excel Worksheet Functions | |||
Calculating hours | Excel Discussion (Misc queries) | |||
Calculating hours | Excel Discussion (Misc queries) | |||
Calculating hours | Excel Worksheet Functions | |||
calculating hours | Excel Worksheet Functions |