Difference between date & time calculation
 

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

Difference between date & time calculation
 

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" wrote in message
...
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

Difference between date & time calculation
 

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" wrote:

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" wrote in message
...
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

Difference between date & time calculation
 

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" wrote in message
...
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" wrote:

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" wrote in
message
...
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

Difference between date & time calculation
 

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" wrote:

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" wrote in message
...
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" wrote:

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" wrote in
message
...
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

Difference between date & time calculation
 

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" wrote in message
...
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" wrote:

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" wrote in
message
...
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" wrote:

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" wrote in
message
...
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

Difference between date & time calculation
 

Awesome!!!! Thank you sooooo much

"Bob Phillips" wrote:

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" wrote in message
...
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" wrote:

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" wrote in
message
...
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" wrote:

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" wrote in
message
...
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