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

holdener

New Member
Joined
Sep 27, 2018
Messages
1
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.
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,825
Office Version
  1. 2010
Platform
  1. Windows
A​
B​
C​
1​
Best time:
1:00.00​
Format of B1: [m]:ss.00
2​
Slower by
3​
5%​
1:03.00​
B3: =B$1*(1+A3)
4​
10%​
1:06.00​
 

Mark Leman

New Member
Joined
Sep 2, 2019
Messages
4
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

New Member
Joined
Sep 2, 2019
Messages
4
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
 

Forum statistics

Threads
1,148,179
Messages
5,745,201
Members
423,932
Latest member
pablo2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top