Need help calculating time

[tech1NJ]

I have a problem displaying the time (minutes) correctly beyond 60 minutes.
Fox example:

B29 = 3/17/09 4:10 PM - cell formated as DATE and TYPE 3/17/09 4:10 PM
C29 = 3/19/09 10:05 PM - cell formated as DATE and TYPE 3/19/09 10:05 PM
D29 will display 24 hours 1075 minutes.
Formula in D29 =INT(C29-B29)*24& "hours "&INT(MOD((C29-B29),1) & minutes"

2 things that I would like to have display in D29
a) is X days X hours X minutes.
b) Total time to exclude the weekends as it is not a business day for us.
I know that I'm mssing something here but do not know what exactly. Can
anyone help.

 

[Luke M]

Make sure the Anaylsis ToolPak Add-in is activated (Tools-addins)

=NETWORKDAYS(B29,C29)-1&" days "&INT(MOD(C29-B29,1)*24)&" hours
"&INT(MOD((C29-B29)*24,1)*60)&" minutes"

 

[David Biddulph]

or =NETWORKDAYS(B29,C29)-1&" days "&TEXT(C29-B29,"h \h\o\u\r\s mm
\m\i\n\u\t\e\s")

 

[tech1NJ]

Dear Luke,

This works great for the exception of one minor problem. It is now
calculating the hours past hour normal work day (8:30 to %PM M-Fri). What can
you help with to fix this additional problem.

Sorry that I did not include that minor detail in the original posting.

 

[Luke M]

With those time limits, how do you have an ending time of 10:05 PM?

OR

Are you wanting a straight subtraction, but then have that translated into
business days & hours (like a time ticket) i.e., I worked 12 hrs, which
equals 1 day 3.5 hours?

 

[tech1NJ]

The 10:05 PM was a mistype on my part. It actually is 10:05 AM. The formula
you provided me works fine except that It also adds the time before 8:30AM
and 5:00PM M-Fri. Is there a way to calculate the time based on the business
hours mentioned above?

 

[Luke M]

I believe this works:

=NETWORKDAYS(B29,C29)-IF(MOD(C29,1)-MOD(B29,1)<0,2,1)&" days
"&IF(MOD(C29,1)<MOD(B29,1),INT(MOD(C29-B29,1)*24-15.5),INT(MOD(C29-B29,1)*24))&"
hours
"&IF(MOD(C29*24,1)<MOD(B29*24,1),INT(MOD(MOD(C29-B29,1)*24-15.5,1)*60),INT(MOD((C29-B29)*24,1)*60))&" minutes"

 

[tech1NJ]

That worked. Thanks. Pretty complicated.......

 

[Luke M]

Indeed. The half hour further complicated things. Actually, there is an
error. Final check on minutes should convert to hours after collecting
decimals, not before.

=NETWORKDAYS(B29,C29)-IF(MOD(C29,1)-MOD(B29,1)<0,2,1)&" days
"&IF(MOD(C29,1)<MOD(B29,1),INT(MOD(C29-B29,1)*24-15.5),INT(MOD(C29-B29,1)*24))&"
hours
"&IF(MOD(C29,1)*24<MOD(B29,1)*24,INT(MOD(MOD(C29-B29,1)*24-15.5,1)*60),INT(MOD((C29-B29)*24,1)*60))&" minutes"