Elapsed Time Formula

A

Art

A1 22:23:54 Start Time
A2 02:15:30 End Time
A3 3:52 Elapsed Time (Hrs:Min)

I need the formula that would produce the result shown in cell A3.
(A3 is formatted in hr:mm).

Though no dates are shown, total elapsed time will never exceed about 6 hours.

Thank you .
 
R

Rick Rothstein

You can try this formula...

=MOD(A2-A1,1)

However, when you format cell as h:mm, it won't round the 36 seconds up to
the next higher minute as your example seems to show.
 
F

francis

try in A3, place this formula

=MOD(A2-A1,1)

be sure that you have the correct format in A3

--
Hope this is helpful

Pls click the Yes button below if this post provide answer you have asked


Thank You

cheers, francis
 
J

joeu2004

A1 22:23:54 Start Time
A2 02:15:30 End Time
A3 3:52 Elapsed Time (Hrs:Min)
I need the formula that would produce the result
shown in cell A3. (A3 is formatted in hr:mm).

=round(mod(A2-A1,1)*1440,0)/1440

Caveat: Although the result will display as 3:52 when formatted as
hh:mm, if you entered 3:52 into A4, IF(A4=A3,TRUE) returns TRUE, but IF
(A4-A3=0,TRUE) returns FALSE (!). The results in A3 and A4 are the
same up to 15 significant digits, but the binary representations are
not identical [1]. Still, I suspect it is as close as you can get
without resorting to exorbinant means, for example:

=--text(round(mod(A2-A1,1)*1440,0)/1440,"hh:mm")


Endnotes:

[1] The constant 3:52 is represented internally exactly by
0.161111111111111,1215965507881264784373342990875244140625. The
result of 2:15:30 - 22:23:54 using the ROUND(MOD) formula above is
0.161111111111111,09384097517249756492674350738525390625. (The comma
demarcates 15 significant digits to the left.)


----- original posting -----
 
R

Rick Rothstein

If you have the Analysis ToolPak Add-in installed, you could use this
formula to round your answer as your post seems to indicate you want...

=MROUND(MOD(A2-A1,1),TIME(0,1,0))
 

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

Top