Time

[ETobias]

I am using MS Excel 2000. I know that there is a time and date function. But is there any function, macros, format, etc. to handle "stopwatch" times? I am interested in doing calculations. I can alway separate the minutes and seconds in different cells and create my own method of calculation. But if there is one already there I do not want to re-invent the wheel.

Eric

[daddylonglegs]

There's no reason why you can't do this. Keep the times together in one cell as a time format value, i.e. using colons like 01:23:45, that makes all the calculations easier.

What sort of calculations do you want to do? You can sum these sort of times just like anything else, you may need to format result cell as [h]:mm:ss to show totals greater than 24 hours.

Post back with more details if you can't make it work like you want to.....

[ETobias]

I did try that and I have now determined that it was User error. Using "time" you cannot use sum(). When I switched to using pluses and minuses the calculations appear to of worked. I have to do more testing before I feel 100 percent sure.

Eric

[daddylonglegs]

Using "time" you cannot use sum().

That isn't true. If your "time" values are recognised as times by excel SUM function works perfectly well. If you get 0 using SUM thats probably because your time values are text formatted, where do they originate from? Are they the results of formulas, or imported from elsewhere or just input?

If you can convert the values to numeric SUM should work or, without converting

=SUMPRODUCT(A1:A10+0)

To test whether your "times" are text formatted try

=ISNUMBER(A1)

where A1 contains 1 of your times, if you get FALSE it's text

To convert a column of text formatted times to numeric select the column and use

Data > Text to Columns > Finish

you'll need to reformat the times to [h]:mm or similar

[ETobias]

Thanks.
You may be right concerning SUM(). I replaced one SUM() with A1+... and it made the value that did not make any sense to one that did. So I *assumed* that was the problem. But several of the other cells that I also used SUM() worked. Where it is a little tricky is that I use the same spreadsheet and just replaced the noncalculated cells with different values. Were it is tricky is that the visible cells might be in the format 0:00:00 but the top "entry" bar uses 12 (for 0):00:00AM. So I have to go to the bar to do my corrections. I cannot just select the cell and type in xx:xx:xx. There are some inconsistencies, but they are minor and I just have to be careful when changing the values.

Eric