Excel - Adding and Subtracting a Time with MilliSeconds

Asked By car on 31-May-07 11:20 AM
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.




JE McGimpsey replied on 31-May-07 11:33 AM
Hmmm... you give two different formats.

If they come in as milliseconds:

10:39:41.001

(.01 would be centiseconds, and :01 is something else entirely), then

A1:     10:39:41.91     (or   10:39:41.910)
A2:     10:39:54.01     (or   10:39:54.010)

use

A3:     = A2 - A1

and format A3 with Format/Cells/Number/Custom hh:mm:ss.00 (hh:mm:ss.000)





In article <20DAC38E-0086-4406-A2FA-AC8B7B6A3F78@microsoft.com>,
David Biddulph replied on 31-May-07 11:45 AM
They look to me like centiseconds rather than milliseconds.

If you correct the first one where you've mistyped a colon instead of the
decimal point, then you can add as normal.
=A1+A2 will give you the answer.
If the answer may go beyond 24 hours, custom format something like
[h]:mm:ss.00
--
David Biddulph
Peo Sjoblom replied on 31-May-07 11:47 AM
That would be text for Excel, the custom format that would include
milliseconds in Excel is

hh:mm:ss.000

it's a period not a colon so you would need to convert them

=--SUBSTITUTE(A1,":",".0",3)


formatted as above would work for you examples but if you would have

10:39:55:211

then this might work

=IF(LEN(MID(A1,FIND(".",SUBSTITUTE(A1,":",".",3))+1,255))=3,--SUBSTITUTE(A1,":",".",3),--SUBSTITUTE(A1,":",".0",3))


remember to format as

hh:mm:ss.000


--
Regards,

Peo Sjoblom
Peo Sjoblom replied on 31-May-07 11:53 AM
Doh! Forget it, I thought you had 3 colons there


--
Regards,

Peo Sjoblom
Erny replied on 31-May-07 02:09 PM
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(RIGHT(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(RIGHT(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
Peo Sjoblom replied on 31-May-07 02:44 PM
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 replied on 31-May-07 05:55 PM
Thx for the reminder, was a bit tired today...:-)