Difference between date & time calculation

[Chris Narowski]

Is there a way to calculate the difference between two dates only counting
working days, but also including the difference in times? I am familiar with
the NetWorkDays formula, however, I need to find the difference between the
specific times. Ex. We are trying to calculate the time it takes in days,
hours, minutes, for someone to perform a task. But we want do not want to
count the weekend days. So if someone starts a task on Friday at 8 am &
finishes the following Mon at 3 pm, can you create a formula for that?

Thanks

 

[Bob Phillips]

Try this

=NETWORKDAYS(A1,A2)-(WEEKDAY(A1,2)<6)*(1-MOD(A2,1))-(WEEKDAY(A2,2)<6)*(MOD(A1,1))

Format the result as d hh:mm. Be aware it only works if the number of days
are less than 32.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Chris Narowski]

Thanks - this gets me alot closer. It doesn't work completely (ex. A1 is 3/7
at 3 pm A2 is 3/10 9 am - using the formula, I am getting 1 18:00.

 

[Bob Phillips]

I get 0 18:00 for that. I am UK and I am assuming you mean 7th March to 10th
March. What do you want to get in this case?

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Chris Narowski]

Oops - sorry, I realized I entered 3/11 vs 3/10. So it is correct. Except
that I realize that I also want only to count hours between 8 & 5, can we add
that right in the formula without messing it up?

 

[Bob Phillips]

How about this

=NETWORKDAYS(A1,A2)
-(WEEKDAY(A1,2)<6)*(1-MIN(MAX(0,TIME(17,0,0)-MOD(A1,1)),TIME(9,0,0)))
-(WEEKDAY(A2,2)<6)*(1-MIN(MAX(MOD(A2,1)-TIME(8,0,0),0),TIME(9,0,0)))

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Chris Narowski]

Awesome!!!! Thank you sooooo much

How about this

=NETWORKDAYS(A1,A2)
-(WEEKDAY(A1,2)<6)*(1-MIN(MAX(0,TIME(17,0,0)-MOD(A1,1)),TIME(9,0,0)))
-(WEEKDAY(A2,2)<6)*(1-MIN(MAX(MOD(A2,1)-TIME(8,0,0),0),TIME(9,0,0)))

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)