Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Display Modes | |
|
|