Calculating business hours between start and stop time

B

Belinda7237

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?
 
D

daddylonglegs

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
 
T

Teethless mama

=(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 said:
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 said:
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?
Click to expand...
Click to expand...
 
D

daddylonglegs

Thank you Teethless mama, you are, of course, correct

Teethless mama said:
=(NETWORKDAYS(A2,B2)-1)*(17:00-09:00)+MOD(B2,1)-MOD(A2,1)
Click to expand...
your formula contain error

should be:
=(NETWORKDAYS(A2,B2)-1)*("17:00"-"09:00")+MOD(B2,1)-MOD(A2,1)


daddylonglegs said:
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 said:
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?
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Calculating time for 3rd shift 4
Calculating Networkdays and Business Hours 3
Calculate the duration between the end time and start time of anot 4
Time and Business Hours 1
Calculating networkdays & working hours 4
Calculating working hours 3
Calculate the total overlapping time of multiple tasks, excluding non-working times 1
Calculating Time elapsed 1

Top