Function for stopwatch times

D

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.-
 
P

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
 
D

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top