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!!!

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

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 <(E-Mail Removed)>,
Madcap <<(E-Mail Removed)>> 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. Than
You

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