Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Function for stopwatch times
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.- |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Function for stopwatch times
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 [s].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 On Jan 11, 4:39*pm, DTTODGG wrote: 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.- |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Function for stopwatch times
You don't need the zero for the hours if you've got numbers after the
decimal point for the seconds. -- David Biddulph "Pete_UK" wrote in message ... 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 [s].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 On Jan 11, 4:39 pm, DTTODGG wrote: 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.- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Is it possible to place a stopwatch in Excel | Excel Worksheet Functions | |||
Is there a way to have a timer or stopwatch function in Excel. | Excel Worksheet Functions | |||
how do i format an excel cell for stopwatch times mm:ss? | Excel Discussion (Misc queries) | |||
How do I set up stopwatch racing times in Excel? | Excel Worksheet Functions | |||
Stopwatch | Excel Worksheet Functions |