Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to calculate no. of hours under different circumstances.
3 situations would be as follow, i set the formular like this is =IF(B1<A1,
24/24-A6+B6,B6-A6), both A & B using time format and C using number format. A B C Start Time End Time No. of hours 10:30 15:00 4.5 00:00 08:00 8.0 08:00 23:59 16.0 **Problem** I can get the result for the first 2, but not the last one, any one can help, many thanks! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to calculate no. of hours under different circumstances.
What's the problem?
4.5 8 15.98333 is what I get using =(B1-A1+(A1B1))*24 are you saying that you want to round, if so what's the rounding criteria? Nearest .25 using decimal hours? If so =ROUND((B1-A1+(A1B1))*24/0.25,0)*0.25 will return 16 -- Regards, Peo Sjoblom Northwest Excel Solutions www.nwexcelsolutions.com (remove ^^ from email address) Portland, Oregon "evon" wrote in message ... 3 situations would be as follow, i set the formular like this is =IF(B1<A1, 24/24-A6+B6,B6-A6), both A & B using time format and C using number format. A B C Start Time End Time No. of hours 10:30 15:00 4.5 00:00 08:00 8.0 08:00 23:59 16.0 **Problem** I can get the result for the first 2, but not the last one, any one can help, many thanks! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to calculate no. of hours under different circumstances.
Hi!
Try this: =(B6-A6+(B6<A6))*24 Copy down as needed. Returns: 4.5 8 15.9833333333333 Biff "evon" wrote in message ... 3 situations would be as follow, i set the formular like this is =IF(B1<A1, 24/24-A6+B6,B6-A6), both A & B using time format and C using number format. A B C Start Time End Time No. of hours 10:30 15:00 4.5 00:00 08:00 8.0 08:00 23:59 16.0 **Problem** I can get the result for the first 2, but not the last one, any one can help, many thanks! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to calculate no. of hours under different circumstances.
Yup, that is what i am looking for the rounding problem, and it works
perfectly would you explain a little bit the logic behind, many thanks! Regards, evon "Peo Sjoblom" wrote: What's the problem? 4.5 8 15.98333 is what I get using =(B1-A1+(A1B1))*24 are you saying that you want to round, if so what's the rounding criteria? Nearest .25 using decimal hours? If so =ROUND((B1-A1+(A1B1))*24/0.25,0)*0.25 will return 16 -- Regards, Peo Sjoblom Northwest Excel Solutions www.nwexcelsolutions.com (remove ^^ from email address) Portland, Oregon "evon" wrote in message ... 3 situations would be as follow, i set the formular like this is =IF(B1<A1, 24/24-A6+B6,B6-A6), both A & B using time format and C using number format. A B C Start Time End Time No. of hours 10:30 15:00 4.5 00:00 08:00 8.0 08:00 23:59 16.0 **Problem** I can get the result for the first 2, but not the last one, any one can help, many thanks! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculate Hours and overtime by week | Excel Worksheet Functions | |||
How do I calculate hours in Excel | New Users to Excel | |||
calculate hours just can't figure it out | Excel Worksheet Functions | |||
calculate difference in time to hours | Excel Worksheet Functions | |||
calculate average hours and minutes | Excel Worksheet Functions |