View Single Post
  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Erny Erny is offline
external usenet poster
 
Posts: 12
Default Adding and Subtracting a Time with MilliSeconds

Hmmm, if you wish to be able to add this in a way that you use mixed
separation symbols (as in your example for the "milliseconds" - i'd call it
rather hundreth of seconds) and still have a result displaying hours minutes
seconds and hundreths of seconds, you could use of course (for an addition
for example) a formula such as:

=TEXT(VALUE(LEFT(A1,2))+VALUE(LEFT(A2,2))+((VALUE( MID(A1,4,2))+VALUE(MID(A2,4,2))+((VALUE(MID(A1,7,2 ))+VALUE(MID(A2,7,2))+((VALUE(RIGHT(A1,2))+VALUE(R IGHT(A2,2)))99)59)59),"#0")&":"&TEXT(MOD(VALUE( MID(A1,4,2))+VALUE(MID(A2,4,2))+((VALUE(MID(A1,7,2 ))+VALUE(MID(A2,7,2))+((VALUE(RIGHT(A1,2))+VALUE(R IGHT(A2,2)))99)59),60),"00")&":"&TEXT(MOD(VALUE( MID(A1,7,2))+VALUE(MID(A2,7,2))+((VALUE(RIGHT(A1,2 ))+VALUE(RIGHT(A2,2)))99),60),"00")&":"&TEXT(MOD( VALUE(RIGHT(A1,2))+VALUE(RIGHT(A2,2)),100),"00")

This should work, but I'm sure there's other ways...:-) I'm just too lazy to
think today, and of course you would find a similar approach for
subtraction...
(hope I haven't missed a parenthesis somewhere - no means to test today)

Have fun,
Erny

"carl" schrieb im Newsbeitrag
...
My time comes in like this:

10:39:41:91
10:39:54.01

hours:minutes:seconds.milliseconds.

Is there a way to add and subtract times in this format ?

Thank you in advance.