Elapsed Time Formula

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

 

[Sheeloo]

Try
=IF(A2<A1,A2+1-A1,A2-A1)

 

[Fred Smith]

The formula is:
=mod(a2-a1,1)

Regards,
Fred

 

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

 

[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

 

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

 

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