Time in Minutes and seconds

[Rick]

I have times entered a minutes and seconds - 3.08 being 3 minutes and 8
seconds.

I want to subtract 2.28 which would be 40 seconds, but obviously decimals
returns 0.80.

How do I get this to show 40?

Thanks
Rick

 

[JE McGimpsey]

One way:

A1: 3.08
A2: 2.28
A3: =(SUBSTITUTE(A1,".",":") - SUBSTITUTE(A2,".",":")) * 1440

 

[Rick]

Thanks, it's long but it works. Except where one of the times is 2.00.

One example has 2.05 - 2.00 and gives, -2755. I am guessing it isn't
recognising the .00. And obvioulsy the answer isn't working when the higher
time is a full minute either.

Any suggestions?

Thanks again.
Rick

 

[Pete_UK]

One way is like this:

=(TIME(0,INT(A1),MOD(A1,1)*100)-
TIME(0,INT(B1),MOD(B1,1)*100))*60*60*24

assuming the larger time is in A1 and the smaller time in B1.

Hope this helps.

Pete

 

[Mike H]

Rick,

This is a great example of "I wouldn't start from here if I was you" It's
far easier to enter times correctly and in a format Excel understands in the
first place and I would recommend this for future. However you are where you
are. Try this for subtracting A1-A2

Format as mm.ss

=(INT(A1)/1440)+TIME(0,0,MOD(A1,INT(A1))*100)-((INT(A2)/1440)+TIME(0,0,MOD(A2,INT(A2))*100))

Mike

 

[Mike H]

Sorry format as [ss]

Rick,

This is a great example of "I wouldn't start from here if I was you" It's
far easier to enter times correctly and in a format Excel understands in the
first place and I would recommend this for future. However you are where you
are. Try this for subtracting A1-A2

Format as mm.ss

=(INT(A1)/1440)+TIME(0,0,MOD(A1,INT(A1))*100)-((INT(A2)/1440)+TIME(0,0,MOD(A2,INT(A2))*100))

Mike

 

[David Biddulph]

=(IF(ISNUMBER(SEARCH(".",A1)),SUBSTITUTE(A1,".",":"),A1&":00") -
IF(ISNUMBER(SEARCH(".",A2)),SUBSTITUTE(A2,".",":"),A2&":00")) * 1440

but better to put the data in with a colon in the first place.