Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 4
Default 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

  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 10,593
Default 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



  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 4
Default 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




  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 10,593
Default 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






  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 4
Default 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









  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 10,593
Default 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









  #7   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 4
Default 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










Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Date time difference JMB Excel Discussion (Misc queries) 5 March 12th 08 11:47 PM
Time difference calculation question Bob Wall Excel Worksheet Functions 1 January 2nd 08 06:25 AM
Time difference calculation Sasha Excel Worksheet Functions 5 July 22nd 06 05:55 AM
Date and time difference Danieljesse Excel Worksheet Functions 1 January 23rd 06 01:47 PM
Calculating Difference Between Start Date & Time And End Date & Ti Samwar Excel Discussion (Misc queries) 2 December 19th 05 01:42 PM


All times are GMT +1. The time now is 07:57 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"