Adding percentages to times in a mm:ss:00 format

[holdener]

I am a swim coach and am trying to create a document where my athletes ca input their best times (mins:secs:hundredths) and it will add a percentage to show them goal times to achieve in practice.

For example:
Swimmers time is one minute (1:00.00). They input their time and a formula will export a time that is 10% SLOWER than their best time (1:06.00) do I have to convert to all seconds first (60 secs at 10% slower would be 66 secs) or can I do it in the mm:ss.00 format?

Thank you for your time.

 

[shg]

[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]

A​

[/td][td="bgcolor:#C0C0C0"]

B​

[/td][td="bgcolor:#C0C0C0"]

C​

[/td][/tr][tr][td="bgcolor:#C0C0C0"]

1​

[/td][td]Best time:[/td][td]

1:00.00​

[/td][td]Format of B1: [m]:ss.00[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

2​

[/td][td="bgcolor:#F3F3F3"]

Slower by​

[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

3​

[/td][td]

5%​

[/td][td="bgcolor:#CCFFCC"]

1:03.00​

[/td][td="bgcolor:#CCFFCC"]B3: =B$1*(1+A3)[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

4​

[/td][td]

10%​

[/td][td="bgcolor:#CCFFCC"]

1:06.00​

[/td][td][/td][/tr]
[/table]

 

[Mark Leman]

I am a swim coach and am trying to create a document where my athletes ca input their best times (mins:secs:hundredths) and it will add a percentage to show them goal times to achieve in practice.

For example:
Swimmers time is one minute (1:00.00). They input their time and a formula will export a time that is 10% SLOWER than their best time (1:06.00) do I have to convert to all seconds first (60 secs at 10% slower would be 66 secs) or can I do it in the mm:ss.00 format?

Thank you for your time.

Hi,
I too have found excel to be a PITA when trying to deal with swim times in MM:HH.00 format. Over the years I have tried several methods and have come to the conclusion that storing the times as text and converting to decimal seconds to to maths on them works best. There are several ways to convert the text to seconds using excel's built in formula, I happen to use

=IFERROR((IFERROR(LEFT(A1,FIND(“:”,A1)-1)* 60,0) ) + RIGHT(A1,LEN(A1)-FIND(“:”,A1)),VALUE(A1))

However it is easier to use VB and have a nice easy =MMSSHH2dec() function. I have done this and you can find a write up on my blog at leman.net (complete with an example that adds % to a time as you wanted

Regards,
Mark Leman

 

[Mark Leman]

Forgot to say, the reason I don't like using "Format of B1: [m]:ss.00" it that Excel corrupts times entered in this format if you try to edit them.
Example, set up a cell with custom formatting of [m]:ss.00, enter a time in it with hundredths like 2:30.45. Click on a different cell and then double click back on the 2:30.45, to edit it. Click away again an the time will read 2:30.00

Really frustrating that excel can corrupt values just by clicking on them!

Regards,
Mark Leman