General Discussion Triathlon Talk » Entering Times into Excel Rss Feed  
Moderators: k9car363, alicefoeller Reply
2011-09-29 9:58 AM

User image

Veteran
189
100252525
Mississippi
Subject: Entering Times into Excel

Due to my own personal neurotic tendencies and the fact that I pretend to be a swim coach, I like to enter race results into Excel spreadsheets.  The problem is that I have yet to find a good format in which to do this. 

I have successfully entered times such that if you print them off or look at the work sheet, it looks normal (i.e. a 500yd swim time of 6:00).  However, the coding of the cell makes the program think I wrote 6:00 AM or 0:06:00.00 or something bizarre like that.  That's where my issue comes in-if I want to divide that time by 5 to get 100yd splits (same thing for a 5k, etc) it no longer works. 

Does anyone have a solution to this?  I guess my question is "is there a time format in Excel that would allow someone to easily calculate splits?".

Thanks ahead of time!



2011-09-29 10:20 AM
in reply to: #3704965

User image

Master
1584
1000500252525
Fulton, MD
Subject: RE: Entering Times into Excel

Put simply, working with time in Excel SUCKS. 

If you are only looking for display, I use the format you mentioned, and just let Excel think I mean am/pm.  If you want to do math on these times, it gets trickier.  The only thing I have found that works is separating the time into three columns, hour, min, sec. 

You can then calculate pace using the following: (hrs*60 + min + sec/60) / distance

That will give you the pace in min/mile (assuming distance is in miles) as a decimal format.  If you want that in time, divide by 24, and format the cell using the "Time / 13:30" option in Excel.

2011-09-29 10:24 AM
in reply to: #3704965

Iron Donkey
38638
50005000500050005000500050002000100050010025
, Wisconsin
Subject: RE: Entering Times into Excel

The Google hit I did didn't help much with an answer, but I didn't check all the hits.  It doesn't look that easy, but I'm sure someone has a better answer.

2011-09-29 10:37 AM
in reply to: #3705010

User image

Champion
9969
5000200020005001001001001002525
, Minnesota
Gold member
Subject: RE: Entering Times into Excel
This, at best, is a hint that might help.  Time needs to be entered like this:

=TIME(0,8,45)

for 8 mins 45 seconds

Not 8:45, for instance



I just enter all of my race data into a race report here on BT and let the magic happen with the available graphs.
2011-09-29 10:40 AM
in reply to: #3704965

User image

Extreme Veteran
509
500
Olathe, KS
Subject: RE: Entering Times into Excel

Excel is not very friendly when dealing with time, at least in terms of how athletes want to use it. It takes some basic familiarity with cell formatting. Two things that I try to do: enter time in terms of hh:mm:ss (for example, a 8 minute 30 sec swim split should be entered into a cell as 00:08:30).

However, what I end up doing, is using number formats with time fractions in stead of dealing with time formats, but this requires more work (I find this ultimately easier to work with). For example, an hour and a half run is 90 minutes, a 35 min 45 sec swim in Excel would be 35.75, and so on.

2011-09-29 10:42 AM
in reply to: #3705051

User image

Extreme Veteran
509
500
Olathe, KS
Subject: RE: Entering Times into Excel

BikerGrrrl - 2011-09-29 10:37 AM This, at best, is a hint that might help.  Time needs to be entered like this:

=TIME(0,8,45)

for 8 mins 45 seconds.

Cool. I wasn't aware that function existed. I learn something new every day.



2011-09-29 10:43 AM
in reply to: #3705065

Veteran
303
100100100
Subject: RE: Entering Times into Excel

Does this help?

 

This is based on 50 meters/yards per lap, and does a conversion for miles.

I can change the formulas for anyone who needs it (i.e. different length for laps, or conversion for kilometers).



Edited by firegirlred 2011-09-29 10:54 AM




Attachments
----------------
Lap Conversion.xls (23KB - 411 downloads)
2011-09-29 10:43 AM
in reply to: #3704965

User image

Pro
9282
500020002000100100252525
Omaha, NE
Subject: RE: Entering Times into Excel

I just did a quick google and found this link:

http://mathforum.org/library/drmath/view/63521.html

I did a quick test and created a cell with 00:06:00 in it.  entered 6:00 (which made it 6:00 PM)  then right click to format cell to time and changed the format to 13:30:35.  Then if you do math on the cell.  =A1/5 for example to get your split times of 1:12.  You may have to format your second cell in the 13:30:35 time format as well.

also, I'm testing this in Excel 2010, so it may work differently in other versions.

2011-09-29 11:09 AM
in reply to: #3705051

User image

Master
2603
2000500100
Los Angeles, CA
Subject: RE: Entering Times into Excel

BikerGrrrl - 2011-09-29 8:37 AM This, at best, is a hint that might help.  Time needs to be entered like this:

=TIME(0,8,45)

for 8 mins 45 seconds

Not 8:45, for instance



I just enter all of my race data into a race report here on BT and let the magic happen with the available graphs.

This works nicely in Excel 2007.  After entering data, format the cell so it's in the correct time type format for readability.  After this, do some basic math (in another cell) using the time cells as data and they will calculate correctly. =)

~Roland

2011-09-29 11:14 AM
in reply to: #3704965

Veteran
303
100100100
Subject: RE: Entering Times into Excel
Check the attachment in my previous post.
2011-09-29 11:52 AM
in reply to: #3704965

User image

Regular
303
100100100
Euless, Texas
Subject: RE: Entering Times into Excel
haha, i knew i wasn't the only one that did this!  i spend about an hour (give or take) every monday after a race to put the results into excel and make it all nice and pretty.


2011-09-29 11:59 AM
in reply to: #3704965

Master
2460
20001001001001002525
Subject: RE: Entering Times into Excel
A roundabout way but which works surprisingly well for downloaded bike/run splits (you can manually enter swim splits separately) is to cut and paste from Sport-tracks. I dumps your charts nicely right into Excel.
2011-09-29 3:15 PM
in reply to: #3704965

User image

Veteran
232
10010025
Charlotte
Gold member
Subject: RE: Entering Times into Excel

Under the 'teach a man to fish' guise I'm going to go into a bit of detail here.

First of all, excel thinks (not displays, but thinks) of all time/date information in units of "days since 1/1/1900."  So if you put "3" in a cell and change it to M/D/Y format it'll say "1/3/00." If you put it in time it'll show up as midnight.  Put 3.25 in a cell and as a date it'll say "1/3/00," but put it in time format and it'll show up as "6:00 AM" because 6:00 AM is 1/4 of the way through the day.

Once you really understand that, the rest is easy.  If you put in your time as "18:15" excel will probably interpret that as "6:15 PM." Change that to "number" format and it'll show up as 0.760, i.e., just over 3/4 of the way through the day. 

Once you see that, you know that excel has misinterpreted your "MM:SS" entry as "HH:MM."  How do you convert hours to minutes and minutes to seconds?  No problem!  Just divide your data by 60.  Now it'll be thinking of your data in the right scale, 0.013 days.  Now that it's on the right scale, if you divide it by the distance it'll still look right.

Similarly, if you did a 30 minute run and just typed in 30... well, now Excel is going to think you did something for 30 days.  To convert that to the appropriate scale, you'd divide by 24 (hours in day" and by 60 (minutes in an hour).  30/24/60 = 0.021 days, or 30 minutes. 

I tend to get carried away talking about Excel so not sure if I've over-explained it or just confused people so I'll stop there.  Well, almost. I'm not a pro, but I am the guy who still gets asked Excel questions by peers who've spent 60+ hours a week for years pounding out office documents. 

The point is, once you understand the implications of how Excel thinks about time, all the rest of this becomes pretty intuitive - just understand how Excel is interpreting the data and modify by 24 or 60 as appropriate to convert to the units you need.

Is that any help at all?



Edited by bruce_v 2011-09-29 3:21 PM
2011-09-29 3:31 PM
in reply to: #3704965

User image

NH
Subject: RE: Entering Times into Excel

I just right click, pick format cell, custom, then pick either mm:ss, or h:mm:ss.  Then when you enter times, they show up and can be manipulated, though in the edit panel at the top it still shows it as time of day as mentioned in the previous post. 

I also sometimes revert to entering hours, minutes, and seconds in separate columns, then have formulas and such to convert everything to seconds for manipulation and then back to separate columns of hours, minutes and seconds on the other side of the math.

2011-09-29 3:46 PM
in reply to: #3704965

User image

Extreme Veteran
657
5001002525
Subject: RE: Entering Times into Excel

Now I remember why I go to this site so much.  I had given up on Excel long ago when it came to recording times.  That format and formula is so easy.

formula

 

2011-09-29 4:20 PM
in reply to: #3704965

User image

Veteran
232
10010025
Charlotte
Gold member
Subject: RE: Entering Times into Excel

The two posts above mine can work great for individual workouts for certain needs but I still think there's value in understanding the underlying logic Excel uses for time measurement.

I find when dumping large data sets of race results into Excel, it often reads the times in the wrong units.  Selecting an entire field of swim times, for instance, and 'paste special divide'-ing by 60 can save you a lot of time when you want to manipulate the data.

Another example - when I want to look at speeds biking, I need to use 24.  So let's say a given person did the bike in a 27 mile Oly in 1:04:48.  I'd need to do this:  27 / (time) / 24. 

This is because 27 / time gives you (miles/day) so you'd need to divide by 24 to get (miles/hour).



2011-09-29 4:24 PM
in reply to: #3705628

User image

Master
1681
1000500100252525
Rural Ontario
Subject: RE: Entering Times into Excel

I use MS Excel to log all my workouts. I simply have two columns - one for minutes (A1) and one for seconds (B1) .  They feed into a third column (C1) which is simply C1 = A1 + B1/60.

It expresses all my times in decimal points but being a metric minded engineer I kinda prefer that to the hexadecimal based Phonetician system of time marking.

2011-09-29 7:00 PM
in reply to: #3705668

User image

Master
1584
1000500252525
Fulton, MD
Subject: RE: Entering Times into Excel
bruce_v - 2011-09-29 5:20 PM

The two posts above mine can work great for individual workouts for certain needs but I still think there's value in understanding the underlying logic Excel uses for time measurement.

I find when dumping large data sets of race results into Excel, it often reads the times in the wrong units.  Selecting an entire field of swim times, for instance, and 'paste special divide'-ing by 60 can save you a lot of time when you want to manipulate the data.

Another example - when I want to look at speeds biking, I need to use 24.  So let's say a given person did the bike in a 27 mile Oly in 1:04:48.  I'd need to do this:  27 / (time) / 24. 

This is because 27 / time gives you (miles/day) so you'd need to divide by 24 to get (miles/hour).

I understand how Excel thinks regarding time - I just find it a PITA.  It has alwas just seemed so much easier to me to enter the time in 3 columns. 

2011-09-30 9:12 AM
in reply to: #3705841

User image

Veteran
232
10010025
Charlotte
Gold member
Subject: RE: Entering Times into Excel
jcnipper - 2011-09-29 8:00 PM
bruce_v - 2011-09-29 5:20 PM

The two posts above mine can work great for individual workouts for certain needs but I still think there's value in understanding the underlying logic Excel uses for time measurement.

(examples)

I understand how Excel thinks regarding time - I just find it a PITA.  It has alwas just seemed so much easier to me to enter the time in 3 columns. 

Totally understand.  I do the same in my personal log.  (Well, almost, I use two fields and ll put in 127 minutes rather than 2 hours and seven minutes, but materially the same.)

When I dump 1000 folks' race reports with five times each, however, rather than going back and manually adjusting 5000 data fields into three fields each, I just spend 30 seconds modifying the data with some creative dividing and I'm done.

Not trying to imply it's the right tool/approach for all cases, just that it can serve as the more generalized approach for more involved tasks.  For personal log or one-off calcs it's definitely not necessary!

New Thread
General Discussion Triathlon Talk » Entering Times into Excel Rss Feed