Swimming times in hours, minutes and seconds

66nana

New Member
Joined
Jul 19, 2013
Messages
3
Hi
I need assistance with Swimming times in hours, minutes and seconds. I have a small swim squad and I would like Excel to add the times in minutes and seconds for every 50 metres. Eg for 400 m swim.
Example:
Tom 47.04 49.03 51.12 54.89

It would also be advantageous to know how to subtract one time from another time.

I'd sincerely appreciate some assistance.

Many thanks.
 

Some videos you may like

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

FormR

MrExcel MVP
Joined
Aug 18, 2011
Messages
6,406
Office Version
365
Platform
Windows
Hi, you can simply sum the seconds and divide by the number of seconds in a day. Format the cell that contains the formula as time.

For example:

<b>Excel 2013/2016</b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="color: #333333;;">Tom</td><td style="text-align: right;;">47.04</td><td style="text-align: right;;">49.03</td><td style="text-align: right;;">51.12</td><td style="text-align: right;;">54.89</td><td style="text-align: right;;"></td><td style="text-align: right;;">00:03:22</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="color: #333333;;">Jerry</td><td style="text-align: right;;">50.1</td><td style="text-align: right;;">51.5</td><td style="text-align: right;;">48.6</td><td style="text-align: right;;">90.5</td><td style="text-align: right;;"></td><td style="text-align: right;;">00:04:01</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">00:00:39</td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">G1</th><td style="text-align:left">=SUM(<font color="Blue">B1:E1</font>)/86400</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">G2</th><td style="text-align:left">=SUM(<font color="Blue">B2:E2</font>)/86400</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">G4</th><td style="text-align:left">=G2-G1</td></tr></tbody></table></td></tr></table><br />
 

Watch MrExcel Video

Forum statistics

Threads
1,099,142
Messages
5,466,923
Members
406,510
Latest member
wizekor

This Week's Hot Topics

Top