Function for stopwatch times

[DTTODGG]

Calling on all Excel gurus!

Is there an already made function to convert text to mm:ss.hh?

I'm trying to work with stopwatch times (minutes, seconds, hundredths).. I
need to be able to add, subtract, average, etc...

Example:
Text field of 1:03.95 equals 63.95 seconds
Then add 50.02 seconds equalling 113.97 seconds
Then convert back to text of 1:53.97

Thank you so much for sharing your wisdom.-

 

[Pete_UK]

First of all, you do not need to enter your stop-watch times as text -
you can enter them as 0:1:3.95 (i.e. with a leading 0: for the hour)
and then use a custom format set to .00 to display this as 63.95.
Then you will be able to add/subtract etc directly on these values,
remembering to use the same custom format for all cells.

If you must enter them as text (by pre-formatting the cell as Text, or
by putting an apostrophe in front of the first digit), then you can
convert the value to time by:

=("0:"&A1)*1

and format as above. If you want the result in seconds, then format
the cell as general or as number and use this formula:

=("0:"&A1)*24*60*60.

In both cases, I've assumed that the "text" time is in A1.

If you want to convert the result of any arithmetic back to text, then
you can use the TEXT function.

Hope this helps.

Pete

 

[David Biddulph]

You don't need the zero for the hours if you've got numbers after the
decimal point for the seconds.
--
David Biddulph

First of all, you do not need to enter your stop-watch times as text -
you can enter them as 0:1:3.95 (i.e. with a leading 0: for the hour)
and then use a custom format set to .00 to display this as 63.95.
Then you will be able to add/subtract etc directly on these values,
remembering to use the same custom format for all cells.

If you must enter them as text (by pre-formatting the cell as Text, or
by putting an apostrophe in front of the first digit), then you can
convert the value to time by:

=("0:"&A1)*1

and format as above. If you want the result in seconds, then format
the cell as general or as number and use this formula:

=("0:"&A1)*24*60*60.

In both cases, I've assumed that the "text" time is in A1.

If you want to convert the result of any arithmetic back to text, then
you can use the TEXT function.

Hope this helps.

Pete