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

#### holdener

##### New Member
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?

### 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
 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
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?

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

Replies
8
Views
239
Replies
10
Views
600
Replies
14
Views
406
Replies
2
Views
325
Replies
3
Views
358

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.

### Which adblocker are you using?

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

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