View Single Post
  #8   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

Thx for the reminder, was a bit tired today...:-)

"Peo Sjoblom" schrieb im Newsbeitrag
...
You can leave out all the VALUE functions if you do calculations with them

=VALUE(LEFT(A1,2))+VALUE(RIGHT(A1,2))

is no different than

=LEFT(A1,2)+RIGHT(A1,2)

when it comes to the result, the calculation will force the text to number


also the value function is really totally obsolete except for pedagogical
reasons
same goes for datevalue and timevalue




--
Regards,

Peo Sjoblom




"Erny" wrote in message
...
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.