Calculating (Date and Time) differences

[Madcap]

All,
I am trying to develop a formula to calculate the difference betwee
one (date and time) and another (date and time) with the times being i
military format. I want the result to be displayed in (X hours:
minutes: X seconds). The data is, for example, a start (date and time
in cell K13 and an end (date and time) in K15. My (date and time) cell
are inputted like this:

4/24/04 6:12.

My problem with my current formula:

Start Time:
4/24/04 6:12

End Time:
4/27/04 0:17

Result:
"3 Days
18 Hours
5 Minutes"

The apparent issue is this duration is not even 3 full days.
My current formula is as follows:
=CONCATENATE(ROUND(ROUND(((K15-K13)*1440),)/1440,0)," Days
",TRUNC(MOD(ROUND((K15-K13)*1440,0),1440)/60)," Hours
",ROUND(MOD(ROUND((K15-K13)*1440,0),60),0), " Minutes")

I need it to display a result like:
"2 Days
18 Hours
5 Minutes"

I'm gettin a headache trying to figure it out. Please help!!!

 

[Frank Kabel]

Hi
try the following formula (A9: starting date/time, A10 end
date/time:

=INT(A10)-INT(A9) -(MOD(A10,1)<MOD(A9,1)) & " days " & TEXT
(MOD(A10,1)-MOD(A9,1)+(MOD(A10,1)<MOD(A9,1)),"h ""hours""
m ""minutes""")

 

[JE McGimpsey]

One way:

=INT(K15-K13+0.000001) & "_Days,_" & MOD(INT((K15-K13)*24+0.000001),24)
& "_Hours,_" & MOD(ROUND((K15-K13)*1440,0),60) & "_Minutes"

I replaced the spaces within quotes with underscores to prevent
unfortunate line wrap.

 

[Madcap]

Being a bit fresh to constructing formulas, I really appreciate the
albeit possibly simple for you guys, time and effort you shown. Than
You