Help Calculating Time

T

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.
 
S

Sheeloo

Try
=INT(C29-B29)&" days "&INT((C29-B29-INT(C29-B29))*24) & " hours " &INT(
((C29-B29-INT(C29-B29))*24-INT((C29-B29-INT(C29-B29))*24))*60) & " minutes"

To exclude weekends/holidays, try to use NETWORKDAYS function.
 
D

David Biddulph

=NETWORKDAYS(B29,C29)-1&" days "&TEXT(C29-B29,"h \h\o\u\r\s mm
\m\i\n\u\t\e\s")
(as shown in another group where the same question was multi-posted; please
don't do that!)
 
S

Sheeloo

Thanks David,

I certainly proved that 'when you have a hammer every problem looks like a
nail.'
 
L

Luke M

As posted in other thread (please don't multi-post)

=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"
 
T

tech1NJ

Thanks. I posted the 2nd one because I did not see this one come up and
thought it did not go through. Thanks again.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Need help calculating time 8
Subtracting time...again 2
INDEX MATCH SMALL 13
separating date and time 5
Detailed Countdown 2
Calculating Elapsed Time 2
Extracting just the time portion 6
airline standby worksheet 7

Top