Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculating business hours between start and stop time
I am trying to calculate the difference between a start time and completion
time and i want to be able to consider business days and business hours. ie: start time: 1/16/08 10:24 AM complete time: 1/18/08 12:40 PM my time between is complete-start = 2.09 my netbusiness days is 3 but i want to also calculate how many hours but also consider the business day clock starts at 9am and ends at 5pm. How do i write a formula to accomplish this? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculating business hours between start and stop time
Hello Belinda,
If you have start time/date in A2 and end time/date in B2 then try this formula =(NETWORKDAYS(A2,B2)-1)*(17:00-09:00)+MOD(B2,1)-MOD(A2,1) format result cell as [h]:mm for your example this will give 18:16 Note: only works if both start and end time/dates are within business hours - post back if that might not be the case "Belinda7237" wrote: I am trying to calculate the difference between a start time and completion time and i want to be able to consider business days and business hours. ie: start time: 1/16/08 10:24 AM complete time: 1/18/08 12:40 PM my time between is complete-start = 2.09 my netbusiness days is 3 but i want to also calculate how many hours but also consider the business day clock starts at 9am and ends at 5pm. How do i write a formula to accomplish this? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculating business hours between start and stop time
=(NETWORKDAYS(A2,B2)-1)*(17:00-09:00)+MOD(B2,1)-MOD(A2,1)
your formula contain error should be: =(NETWORKDAYS(A2,B2)-1)*("17:00"-"09:00")+MOD(B2,1)-MOD(A2,1) "daddylonglegs" wrote: Hello Belinda, If you have start time/date in A2 and end time/date in B2 then try this formula =(NETWORKDAYS(A2,B2)-1)*(17:00-09:00)+MOD(B2,1)-MOD(A2,1) format result cell as [h]:mm for your example this will give 18:16 Note: only works if both start and end time/dates are within business hours - post back if that might not be the case "Belinda7237" wrote: I am trying to calculate the difference between a start time and completion time and i want to be able to consider business days and business hours. ie: start time: 1/16/08 10:24 AM complete time: 1/18/08 12:40 PM my time between is complete-start = 2.09 my netbusiness days is 3 but i want to also calculate how many hours but also consider the business day clock starts at 9am and ends at 5pm. How do i write a formula to accomplish this? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculating business hours between start and stop time
Thank you Teethless mama, you are, of course, correct
"Teethless mama" wrote: =(NETWORKDAYS(A2,B2)-1)*(17:00-09:00)+MOD(B2,1)-MOD(A2,1) your formula contain error should be: =(NETWORKDAYS(A2,B2)-1)*("17:00"-"09:00")+MOD(B2,1)-MOD(A2,1) "daddylonglegs" wrote: Hello Belinda, If you have start time/date in A2 and end time/date in B2 then try this formula =(NETWORKDAYS(A2,B2)-1)*(17:00-09:00)+MOD(B2,1)-MOD(A2,1) format result cell as [h]:mm for your example this will give 18:16 Note: only works if both start and end time/dates are within business hours - post back if that might not be the case "Belinda7237" wrote: I am trying to calculate the difference between a start time and completion time and i want to be able to consider business days and business hours. ie: start time: 1/16/08 10:24 AM complete time: 1/18/08 12:40 PM my time between is complete-start = 2.09 my netbusiness days is 3 but i want to also calculate how many hours but also consider the business day clock starts at 9am and ends at 5pm. How do i write a formula to accomplish this? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculating Networkdays and Business Hours | Excel Worksheet Functions | |||
Date and Time Response- before/during/after Business hours | Excel Discussion (Misc queries) | |||
Add 12 business hours to Time | Excel Worksheet Functions | |||
Calculating Business Hours Between 2 Dates | Excel Discussion (Misc queries) | |||
Time and Business Hours | Excel Worksheet Functions |