Time in Minutes and seconds
 

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

Time in Minutes and seconds
 

One way:

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

In article ,
Rick wrote:

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

Time in Minutes and seconds
 

Enter with a colon instead of a full stop.
3:08, not 3.08
--
David Biddulph

"Rick" wrote in message
...
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

Time in Minutes and seconds
 

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


"JE McGimpsey" wrote:

One way:

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

In article ,
Rick wrote:

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

Time in Minutes and seconds
 

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

On Nov 4, 8:31*am, Rick wrote:
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

Time in Minutes and seconds
 

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


"Rick" wrote:

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


"JE McGimpsey" wrote:

One way:

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

In article ,
Rick wrote:

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

Time in Minutes and seconds
 

Sorry format as [ss]

"Mike H" wrote:

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


"Rick" wrote:

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


"JE McGimpsey" wrote:

One way:

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

In article ,
Rick wrote:

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

Time in Minutes and seconds
 

=(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.
--
David Biddulph

"Rick" wrote in message
...
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


"JE McGimpsey" wrote:

One way:

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

In article ,
Rick wrote:

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