I recently battled with Excel as well. Apparently people who had my position have been reporting WAY wrong for like a year. And since I was the one who found out they had, I had to not only correct the Excel sheet, but also redo all the monthly reports... Fun times.
Hmm, it makes me wonder if you can rig up an Excel sheet to accept values that represent time durations without converting it to a ratio to a 24-hour day. My first thought was to report time in terms of how many hours and how many seconds. The problem is that most people just want to enter 2 minutes and 13 seconds as 2:13 and not 2, tab, 13.
I suppose I could rig a cell to be text only and then create a formula from that cell to give a decimal representation of the time. This formula could work: =LEFT(A1, FIND(":", A1, 1)-1)*60 + RIGHT(A1, 2)
But then what if there is a decimal portion? Then I suppose I should go with: =LEFT(A1, FIND(":", A1, 1)-1)*60 + RIGHT(A1, LEN(A1)-FIND(":", A1, 1))
Then there's the need to error-check it, either through validating the original cell's value or including another IF statement in the formula to return a blank value if the cell does not contain a colon, which then opens up other cans of worms if someone types in something weird like 23:1a.87:1