Slowtwitch Forums: Triathlon Forum: For the excel savvy...adding time in Microsoft Excel Training log Question

Calendar Coaches Fitters Retailers Triathlon Clubs Stack & Reach Custom Geometry Calculator Training Log
		MAIN INDEX	RULES & LEGEND	LOG IN

Search for (options)

Newsletter Signup

Slowtwitch Forums: Triathlon Forum:
For the excel savvy...adding time in Microsoft Excel Training log Question

grayskinner

Nov 4, 09 7:03

Post #1 of 7 (306 views)

For the excel savvy...adding time in Microsoft Excel Training log Question	Can't Post

Any excel savvy triathletes out there know how to add time cells together in excel? i have been messing with it for a while and seem to be stuck.
I have used this pace calculator http://www.bx3.com/phil/tri/tritime.asp which is good, but not as useful if you are trying to run negative splits.

I am trying to tinker with some half marathon and tri splits and want to be able to see the effect on the net time of different splits.
Specifically i would like to be able to enter time in mm:ss rather than fractions of an hour, and still be able to sum several mile or time splits.

Anyone have a file with this already done or know how to format the cells this way?

Would like it to look like this ideally, where you can see what your overall time would be at each mile split.

mile
1 - 7:00 - 7:00
2 - 7:00 - 14:00
3 - 6:45 - 20:45

Total - 1:40:00
www.oscycling.com - Train Hard! Replenish Well! Drink OS!

jeremyb

Nov 4, 09 7:09

Post #2 of 7 (296 views)

Re: For the excel savvy...adding time in Microsoft Excel Training log Question [grayskinner] [In reply to]	Can't Post

Adding Times
You can add times using the =SUM worksheet function. Just enter all of your times as HH:MM:SS, and then
use SUM to add them up. You may leave off the :SS if you prefer. By default, Excel will display the sum of
times in "time-of-day" format, meaning that adding 12:30 + 12:45 will yield 01:15. You can prevent Excel
from "rolling over" at 24 hours by formatting the result cell as [h]:mm which will cause it to display 25:15
rather than 01:15.
If you want to add up minutes and seconds, you must include a leading "0:" in your data. For example,
enter "0:10:20" to indicate 10 minutes, 20 seconds. When you sum these times, Excel will display the
sum in "time-of-day" format, meaning that adding 0:40:10 and 0:30:20 will yield 1:10:30. You can prevent
Excel form "rolling over" at the hour by formatting the result cell as [m]:ss which will cause it to
display 70:30 rather than 1:10:30.
Another method of adding times is to use the TIME function. To add 1 hour, 35 minutes, 10 seconds to a time in A1, use the function
=A1 + TIME(1,35,10)

http://www.cpearson.com/excel/datearith.htm

jeremy
Plus One Lap: Online Cyclocross Magazine

grayskinner

Nov 4, 09 7:18

Post #3 of 7 (281 views)

Re: For the excel savvy...adding time in Microsoft Excel Training log Question [jeremyb] [In reply to]	Can't Post

perfect, thank you!
www.oscycling.com - Train Hard! Replenish Well! Drink OS!

STP

Nov 4, 09 7:26

Post #4 of 7 (265 views)

Re: For the excel savvy...adding time in Microsoft Excel Training log Question [grayskinner] [In reply to]	Can't Post

The trick to doing math with time in Excel is to make sure your cells are formated correctly and that your are entering your data consistant with the format you have chosen. Go to "Format Cells" select "Time" and pick the format you want to use (eg HH:MM HH:MM:SS etc). Once you have the format you want applied to all your cells, the basic math like adding or multiplying is pretty much the same as with numbers.

For the example you have, just put your pace in one cell (eg 7:00) then create a column with 1 - however many miles you want and in the next column put =pace cell * mile cell. At the end of the column total it up with the =sum( ) function.

You can get more creative with multiple paces once you have the basic sheet set up.

mcdoublee

Nov 4, 09 8:09

Post #5 of 7 (212 views)

Re: For the excel savvy...adding time in Microsoft Excel Training log Question [grayskinner] [In reply to]	Can't Post

In google spreadsheets, it automatically recognizes time and adds, subtracts, divides correctly. Pretty handy.

------------------------------------
MC Double E

greg'n

Nov 4, 09 8:13

Post #6 of 7 (209 views)

Re: For the excel savvy...adding time in Microsoft Excel Training log Question [grayskinner] [In reply to]	Can't Post

You can simplify your data entry and save a lot of trial and error if you use two custom cell formats and one easy summing formula.

For the cells where you enter each mile (or partial mile) split, use Custom format h:mm
For the sum or total cell, use Custom format h:mm:ss and divide the sum by 60

While technically you are "incorrectly" entering your mile splits as hours and minutes rather than minutes and seconds, it allows for cleaner data entry and simplified display of the splits without a leading zero.

bx3

Nov 4, 09 9:55

Post #7 of 7 (144 views)

Re: For the excel savvy...adding time in Microsoft Excel Training log Question [grayskinner] [In reply to]	Can't Post

You can also use this handy online cumulative time calculator:
http://www.bx3.com/phil/tri/pace.asp

Phil
___________________________________________________________________
Predict your finish time: http://www.bx3.com/phil/tri/tritime.asp
Triathlon Conversion Calculator: http://www.bx3.com/phil/tri/tricalc.asp