When I set up my spreadsheet 4 years ago, I didn't have luck getting the time format to work the way I wanted, so I did something different. It sounds like most of you are looking for a data warehouse, but I also wanted to have some analysis capabilities via excel's PivotTables.
To get the PivotTables to show up the way I wanted, by ascending time, I had to create a "total time in seconds" column. I have the time entered as text, and then have a bunch of hidden columns that break out the minutes and seconds (including the decimal) based on the text entry. Last, there's a column with a formula that adds them up into total seconds (a 5:05.05 500 is a 305.05). It is a pretty round-about way to get what I wanted, but I have the text "time" column in my PivotTable and don't really look at the total seconds column.
Yes, I basically use it as a database for our LMSC, while still allowing me to do calculations with the times. (Only recently have I ported my own swims to Excel.)
Jeff, you probably have it set up just the way you like it, but converting to seconds is much easier than that (its a one-step calculation); see how in
forums.usms.org/showthread.php
I guess there is a lot of :frustrated: about Excel and times...maybe someday I'll write up something and post it somewhere (not sure where)...
When I set up my spreadsheet 4 years ago, I didn't have luck getting the time format to work the way I wanted, so I did something different. It sounds like most of you are looking for a data warehouse, but I also wanted to have some analysis capabilities via excel's PivotTables.
To get the PivotTables to show up the way I wanted, by ascending time, I had to create a "total time in seconds" column. I have the time entered as text, and then have a bunch of hidden columns that break out the minutes and seconds (including the decimal) based on the text entry. Last, there's a column with a formula that adds them up into total seconds (a 5:05.05 500 is a 305.05). It is a pretty round-about way to get what I wanted, but I have the text "time" column in my PivotTable and don't really look at the total seconds column.
Yes, I basically use it as a database for our LMSC, while still allowing me to do calculations with the times. (Only recently have I ported my own swims to Excel.)
Jeff, you probably have it set up just the way you like it, but converting to seconds is much easier than that (its a one-step calculation); see how in
forums.usms.org/showthread.php
I guess there is a lot of :frustrated: about Excel and times...maybe someday I'll write up something and post it somewhere (not sure where)...