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.

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.

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.

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

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

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.

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

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?

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.

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.

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

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!