BT Forum

No new posts
Moderators: k9car363, alicefoeller  Reply 
20110929 9:58 AM 
Veteran 189 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 inif 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! 

20110929 10:20 AM in reply to: #3704965 
Master 1584 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. 
20110929 10:24 AM in reply to: #3704965 
Iron Donkey 38638 , 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. 
20110929 10:37 AM in reply to: #3705010 
Champion 9969 , Minnesota  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. 
20110929 10:40 AM in reply to: #3704965 
Extreme Veteran 509 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. 
20110929 10:42 AM in reply to: #3705051 
Extreme Veteran 509 Olathe, KS  Subject: RE: Entering Times into Excel BikerGrrrl  20110929 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. 

20110929 10:43 AM in reply to: #3705065 
Veteran 303  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 20110929 10:54 AM Attachments  Lap Conversion.xls (23KB  411 downloads) 
20110929 10:43 AM in reply to: #3704965 
Pro 9282 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. 
20110929 11:09 AM in reply to: #3705051 
Master 2603 Los Angeles, CA  Subject: RE: Entering Times into Excel BikerGrrrl  20110929 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 
20110929 11:14 AM in reply to: #3704965 
Veteran 303  Subject: RE: Entering Times into Excel Check the attachment in my previous post. 
20110929 11:52 AM in reply to: #3704965 
Regular 303 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. 

20110929 11:59 AM in reply to: #3704965 
Master 2460  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 Sporttracks. I dumps your charts nicely right into Excel. 
20110929 3:15 PM in reply to: #3704965 
Veteran 232 Charlotte  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 overexplained 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 20110929 3:21 PM 
20110929 3:31 PM in reply to: #3704965 
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. 
20110929 3:46 PM in reply to: #3704965 
Extreme Veteran 657  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.

20110929 4:20 PM in reply to: #3704965 
Veteran 232 Charlotte  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). 

20110929 4:24 PM in reply to: #3705628 
Master 1681 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. 
20110929 7:00 PM in reply to: #3705668 
Master 1584 Fulton, MD  Subject: RE: Entering Times into Excel bruce_v  20110929 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. 
20110930 9:12 AM in reply to: #3705841 
Veteran 232 Charlotte  Subject: RE: Entering Times into Excel jcnipper  20110929 8:00 PM bruce_v  20110929 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 oneoff calcs it's definitely not necessary! 