Calculating (Date and Time) differences

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""")

-----Original Message-----
All,
I am trying to develop a formula to calculate the
difference between
one (date and time) and another (date and time) with the
times being in
military format. I want the result to be displayed in (X
hours: X
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) cells
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!!!!


---
Message posted from http://www.ExcelForum.com/

.

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.

In article ,
Madcap wrote:

All,
I am trying to develop a formula to calculate the difference between
one (date and time) and another (date and time) with the times being in
military format. I want the result to be displayed in (X hours: X
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) cells
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!!!!

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


---
Message posted from http://www.ExcelForum.com/