CdCf

With my system, you just enter time as you normally would, only with a decimal point instead of a colon.

For 2 hours and 43 minutes, you enter: 2.43
The first formula converts that to: ~2.717
The second formula sums it all up and converts it back to hours and minutes, but still as a decimal number.

This lets you enter time in a single cell.

Use whichever method you prefer, but I know I prefer mine...

phidauex

Ok.

You guys are crazy, time is easy in Excel! Well, maybe not easy, but I've been working with it for a while, and once you get it, its pretty easy to work with.

Here is what you do. If you have a column that is Start Time, and another column that is End Time, and you are trying to calculate the difference between those, then select all the START TIME and END TIME cells, right click, and select 'Format Cells'. Then select Time in the left hand column, and then pick your time format, probably the second one down: 1:30 PM style.

Now, start entering start and end times in the "12:45:00 PM" format. It will recognize this as a time. You can even shorthand and do "12:45 pm" and it will auto format once it realizes you are talking about a time.

Now, to calculate difference, you just need to subtract. Select all the TOTAL TIME cells, choose "Format Cells" from the right click menu, select time, and then choose a time format like the first one, with hh:mm and no PM or AM marker. This is just a quantity of time, formatted in hours:minutes:seconds. Then, as your formula, just use "=ENDTIMECELL - STARTTIMECELL" and you are good! You now have a quantity of time that is the difference between the end time and the start time of the ride.

You can then use a standard sum formula to sum these total times to get a total total. Again, use the format cells function to make sure it knows that the quantity you are indicating is a time quantity.

The only caveat to this is that it relies on the fact that the ride starts and stops on the same day. If you start your 4 hour ride at 10:00pm and finish at 2:00am, you may need to list it as two rides on two different days.

If anyone wants to see an example spreadsheet with this time formatting, let me know.

peace,
sam

neilG

I'm so Excel-impaired. Here's what I was trying to do. I rode 1:59:30 yesterday. I want to put 1 in the hour column and 59.5 in the minute column but I can't figure out how to change the formatting to allow that. Thanks.

Later that morning.........You have to unprotect each sheet to move the decimals and do other formatting. Duh.

Peek the Geek

Too bad I don't keep track of what specific time my rides start and end. I guess I could work around that by just entering 12:00 for a start time and entering whatever end time would coincide with my amt of time ridden.

It's crazy, though. Based on your method, Excel has the capacity to work with time. So why can't you just enter your hours:minutes:seconds as such and let Excel add 'em up? What a hassle.

Bigmark

Right, that is easy, but most Cyclometers don’t give you start and end time, they just give time ridden. That is where the problem comes in. And if you can show me an easer way to have Excel add up time in hours and seconds differently than I am I would really like to see it, because this is the only way I could figure it out.

My problem was that I would put in 1hour, and 45minutes, then the next enter would be something like 1hour and 40 minutes. Excel being base 10 would add the minutes to 85minutes. That would tick me off. I never road 1:85 before, I would ride 2:25 That was all.

I admit I am excel challenged, and the formula was the best I could do.

phidauex

OK, my mistake, I thought you were putting in a 'start' and 'stop' time and calculating the duration.

Try this revised solution:

Excel thinks of time in a 'serial' fashion. One day = 1, and hours, minutes and seconds are various decimal components of 1. However, when you add up a bunch of hours, it tends to 'roll over' when it hits 24 hours. If you add up 25 hours worth of stuff, you'll only see 1 hour as the solution.

Here is the trick:

Select all the cells that are expecting a quantity of hours (as opposed to a time of day). Right click, and select, "Format Cells". Now, scroll down in the left hand column and choose "Custom". Now, in the format box, type in [h]:mm or [h]:mm:ss , click OK, and you should be good. The [h] tells Excel NOT to roll over the hours in 24 hour increments, and will allow you to show sums of time greater than 24.

Is this what you want? Excel is amazingly powerful, but at the same time, amazingly complex and obfuscated. If you want to do something, there is almost certainly a way to do it, but there is no guarantee you'll be able to figure it out on your own.

peace,
sam

CdCf

I agree, Excel is fantastic, and I use it almost every day for various things.
One thing I really miss, though, is a solver.

toomanybikes

Do you mean like the one under the Tools menu????

CdCf

I have no solver in my Tools menu.

toomanybikes

**********

What version are you using?

I have MS Office 2004 for OSX - it has Solver under the tools menu

CdCf

Office 2001 for OS X.

toomanybikes

With 2001 and V.x you have to install Solver separately from the Value Pak add-ins that are on the installation disk.

Medpilot

Great excel sheets everyone.

I was using https://www.progresslog.com/

But I think I'll start logging my rides on excel.

Only thing I would add is max speed column.

WaitUpForMe

I was messing around with time computations a few weeks ago, while I was making an iPod playlist in order to keep track of cumulative time as I added songs. It took a while, but I believe I managed to figure this out. Not sure I can explain it real well, but I'll try. BTW, I still use Office '97, so YMMV.

I went to Format . . . Cells . . . Number tab . . . "Custom" Category . . . and added [h]:mm

When you enter your times, be sure to enter numbers on either side of the colon. E.g., if it's one hour, enter 1:00. If it's 3 hours 27 minutes, enter 3:27. If it's 45 minutes, enter 0:45. The number displays correctly on the spreadsheet, even though it appears as a time of day in the data-entry box towards the top of the window. If you have a mental disorder, you could format the cells as hours:minutes:seconds for your rides.

These numbers can be manipulated using simple formulas like =SUM(a1:a10).

For my spreadsheet, I had one column for the time of each track, and the next column keeps track of the cumulative time as you move down the rows. Now I know that when Dream Theater's Pull Me Under starts, I'm 38 minutes into my ride. Well, 38:20.

CdCf

Hmm, I'm actually using an internal version given to us at a place I used to work a couple of years ago.
I guess it's technically illegal now, since I don't work there anymore, but I don't have any installation discs, anyway.