Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 26
Default Difference in needed and delivered time with workdays only

A report was needed on 10/01/06 8:28:46. The delivery is on 10/30/06
16:17:20. Need to calculate the difference in days, hours and minutes, but
cannot take into weekends and holidays. Is there a way to do this?
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,726
Default Difference in needed and delivered time with workdays only

=NETWORKDAYS(A1,A2,holidays)-(MOD(A2,1)-MOD(A1,1)<0)&" days,
"&TEXT(MOD(MOD(A2,1)-MOD(A1,1),1),"hh:mm:ss")

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"Prema" wrote in message
...
A report was needed on 10/01/06 8:28:46. The delivery is on 10/30/06
16:17:20. Need to calculate the difference in days, hours and minutes, but
cannot take into weekends and holidays. Is there a way to do this?



  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 26
Default Difference in needed and delivered time with workdays only

I tried the suggested formula. It works when the the diference is more than
one day. But, if the difference in the dates is less than one day, it still
shows 1 day and the difference in hours. The hours calculated are accurate
though. I would expect the formula to calculate the difference as 0 days,
followed by the hours.

Thanks for the help


"Bob Phillips" wrote:

=NETWORKDAYS(A1,A2,holidays)-(MOD(A2,1)-MOD(A1,1)<0)&" days,
"&TEXT(MOD(MOD(A2,1)-MOD(A1,1),1),"hh:mm:ss")

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"Prema" wrote in message
...
A report was needed on 10/01/06 8:28:46. The delivery is on 10/30/06
16:17:20. Need to calculate the difference in days, hours and minutes, but
cannot take into weekends and holidays. Is there a way to do this?




  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 174
Default Difference in needed and delivered time with workdays only

You just need to tweak Bob's formula slightly, i.e.

=NETWORKDAYS(A1,A2,holidays)-1-(MOD(A1,1)MOD(A2,1))&" days,
"&TEXT(MOD(MOD(A2,1)-MOD(A1,1),1),"hh:mm:ss")

assumes A2 is later than A1 and that both of these are during workdays

"Prema" wrote:

I tried the suggested formula. It works when the the diference is more than
one day. But, if the difference in the dates is less than one day, it still
shows 1 day and the difference in hours. The hours calculated are accurate
though. I would expect the formula to calculate the difference as 0 days,
followed by the hours.

Thanks for the help


"Bob Phillips" wrote:

=NETWORKDAYS(A1,A2,holidays)-(MOD(A2,1)-MOD(A1,1)<0)&" days,
"&TEXT(MOD(MOD(A2,1)-MOD(A1,1),1),"hh:mm:ss")

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"Prema" wrote in message
...
A report was needed on 10/01/06 8:28:46. The delivery is on 10/30/06
16:17:20. Need to calculate the difference in days, hours and minutes, but
cannot take into weekends and holidays. Is there a way to do this?




  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 174
Default Difference in needed and delivered time with workdays only

In fact you could simplify a little too......

=NETWORKDAYS(A1,A2,holidays)-1-(MOD(A1,1)MOD(A2,1))&" days,
"&TEXT(MOD(A2-A1,1),"hh:mm")

"daddylonglegs" wrote:

You just need to tweak Bob's formula slightly, i.e.

=NETWORKDAYS(A1,A2,holidays)-1-(MOD(A1,1)MOD(A2,1))&" days,
"&TEXT(MOD(MOD(A2,1)-MOD(A1,1),1),"hh:mm:ss")

assumes A2 is later than A1 and that both of these are during workdays

"Prema" wrote:

I tried the suggested formula. It works when the the diference is more than
one day. But, if the difference in the dates is less than one day, it still
shows 1 day and the difference in hours. The hours calculated are accurate
though. I would expect the formula to calculate the difference as 0 days,
followed by the hours.

Thanks for the help


"Bob Phillips" wrote:

=NETWORKDAYS(A1,A2,holidays)-(MOD(A2,1)-MOD(A1,1)<0)&" days,
"&TEXT(MOD(MOD(A2,1)-MOD(A1,1),1),"hh:mm:ss")

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"Prema" wrote in message
...
A report was needed on 10/01/06 8:28:46. The delivery is on 10/30/06
16:17:20. Need to calculate the difference in days, hours and minutes, but
cannot take into weekends and holidays. Is there a way to do this?





  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default Difference in needed and delivered time with workdays only

How would you change the output to just show the total hours and seconds.

Example:
1 day, 8:00 would show 32:00

Thanks,

RN

"daddylonglegs" wrote:

In fact you could simplify a little too......

=NETWORKDAYS(A1,A2,holidays)-1-(MOD(A1,1)MOD(A2,1))&" days,
"&TEXT(MOD(A2-A1,1),"hh:mm")

"daddylonglegs" wrote:

You just need to tweak Bob's formula slightly, i.e.

=NETWORKDAYS(A1,A2,holidays)-1-(MOD(A1,1)MOD(A2,1))&" days,
"&TEXT(MOD(MOD(A2,1)-MOD(A1,1),1),"hh:mm:ss")

assumes A2 is later than A1 and that both of these are during workdays

"Prema" wrote:

I tried the suggested formula. It works when the the diference is more than
one day. But, if the difference in the dates is less than one day, it still
shows 1 day and the difference in hours. The hours calculated are accurate
though. I would expect the formula to calculate the difference as 0 days,
followed by the hours.

Thanks for the help


"Bob Phillips" wrote:

=NETWORKDAYS(A1,A2,holidays)-(MOD(A2,1)-MOD(A1,1)<0)&" days,
"&TEXT(MOD(MOD(A2,1)-MOD(A1,1),1),"hh:mm:ss")

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"Prema" wrote in message
...
A report was needed on 10/01/06 8:28:46. The delivery is on 10/30/06
16:17:20. Need to calculate the difference in days, hours and minutes, but
cannot take into weekends and holidays. Is there a way to do this?



  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,480
Default Difference in needed and delivered time with workdays only

Hi

Use
=NETWORKDAYS(A1,A2,holidays)-1+MOD(A2-A1,1)
format the cell as [hh]:mm
--
Regards
Roger Govier



"rnunley" wrote in message
...
How would you change the output to just show the total hours and seconds.

Example:
1 day, 8:00 would show 32:00

Thanks,

RN

"daddylonglegs" wrote:

In fact you could simplify a little too......

=NETWORKDAYS(A1,A2,holidays)-1-(MOD(A1,1)MOD(A2,1))&" days,
"&TEXT(MOD(A2-A1,1),"hh:mm")

"daddylonglegs" wrote:

You just need to tweak Bob's formula slightly, i.e.

=NETWORKDAYS(A1,A2,holidays)-1-(MOD(A1,1)MOD(A2,1))&" days,
"&TEXT(MOD(MOD(A2,1)-MOD(A1,1),1),"hh:mm:ss")

assumes A2 is later than A1 and that both of these are during workdays

"Prema" wrote:

I tried the suggested formula. It works when the the diference is
more than
one day. But, if the difference in the dates is less than one day, it
still
shows 1 day and the difference in hours. The hours calculated are
accurate
though. I would expect the formula to calculate the difference as 0
days,
followed by the hours.

Thanks for the help


"Bob Phillips" wrote:

=NETWORKDAYS(A1,A2,holidays)-(MOD(A2,1)-MOD(A1,1)<0)&" days,
"&TEXT(MOD(MOD(A2,1)-MOD(A1,1),1),"hh:mm:ss")

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"Prema" wrote in message
...
A report was needed on 10/01/06 8:28:46. The delivery is on
10/30/06
16:17:20. Need to calculate the difference in days, hours and
minutes, but
cannot take into weekends and holidays. Is there a way to do
this?





  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 205
Default Difference in needed and delivered time with workdays only

The answer I get is not correct
A1 10/10/08 16:30
b1 10/14/08 12:00 PM
The answer I get with this formula is 0 days,19:30:00 what am I doing wrong?
--
Linda


"Bob Phillips" wrote:

=NETWORKDAYS(A1,A2,holidays)-(MOD(A2,1)-MOD(A1,1)<0)&" days,
"&TEXT(MOD(MOD(A2,1)-MOD(A1,1),1),"hh:mm:ss")

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"Prema" wrote in message
...
A report was needed on 10/01/06 8:28:46. The delivery is on 10/30/06
16:17:20. Need to calculate the difference in days, hours and minutes, but
cannot take into weekends and holidays. Is there a way to do this?




  #9   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,240
Default Difference in needed and delivered time with workdays only

What do you have for "holidays"?

Linda wrote:
The answer I get is not correct
A1 10/10/08 16:30
b1 10/14/08 12:00 PM
The answer I get with this formula is 0 days,19:30:00 what am I doing wrong?


"Bob Phillips" wrote:
=NETWORKDAYS(A1,A2,holidays)-(MOD(A2,1)-MOD(A1,1)<0)&" days,
"&TEXT(MOD(MOD(A2,1)-MOD(A1,1),1),"hh:mm:ss")

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"Prema" wrote in message
...
A report was needed on 10/01/06 8:28:46. The delivery is on 10/30/06
16:17:20. Need to calculate the difference in days, hours and minutes, but
cannot take into weekends and holidays. Is there a way to do this?




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



All times are GMT +1. The time now is 06:56 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"