
Next: Subtotal Formula 
Author 
Message 
carl External
Since: Mar 03, 2006 Posts: 126

Posted: Thu May 31, 2007 12:20 pm Post subject: Adding and Subtracting a Time with MilliSeconds Archived from groups: microsoft>public>excel>worksheet>functions (more info?) 


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. 

Back to top 


Peo Sjoblom External
Since: Sep 15, 2003 Posts: 2970

Posted: Thu May 31, 2007 12:47 pm Post subject: Re: Adding and Subtracting a Time with MilliSeconds [Login to view extended thread Info.] Archived from groups: per prev. post (more info?) 


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

Back to top 


Peo Sjoblom External
Since: Sep 15, 2003 Posts: 2970

Posted: Thu May 31, 2007 12:53 pm Post subject: Re: Adding and Subtracting a Time with MilliSeconds [Login to view extended thread Info.] Archived from groups: per prev. post (more info?) 


Doh! Forget it, I thought you had 3 colons there

Regards,
Peo Sjoblom
"Peo Sjoblom" wrote in message
> 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
>
>
>
> "carl" wrote in message
>
>> 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.
>
> 

Back to top 


JE McGimpsey External
Since: Feb 05, 2004 Posts: 3284

Posted: Thu May 31, 2007 1:33 pm Post subject: Re: Adding and Subtracting a Time with MilliSeconds [Login to view extended thread Info.] Archived from groups: per prev. post (more info?) 


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 ,
carl wrote:
> 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. 

Back to top 


David Biddulph External
Since: Feb 24, 2007 Posts: 1373

Posted: Thu May 31, 2007 8:45 pm Post subject: Re: Adding and Subtracting a Time with MilliSeconds [Login to view extended thread Info.] Archived from groups: per prev. post (more info?) 


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

Back to top 


Erny External
Since: Apr 25, 2007 Posts: 32

Posted: Fri Jun 01, 2007 12:09 am Post subject: Re: Adding and Subtracting a Time with MilliSeconds [Login to view extended thread Info.] Archived from groups: per prev. post (more info?) 


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

Back to top 


Peo Sjoblom External
Since: Sep 15, 2003 Posts: 2970

Posted: Fri Jun 01, 2007 12:09 am Post subject: Re: Adding and Subtracting a Time with MilliSeconds [Login to view extended thread Info.] Archived from groups: per prev. post (more info?) 


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(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
>
> "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.
>
> 

Back to top 


Erny External
Since: Apr 25, 2007 Posts: 32

Posted: Fri Jun 01, 2007 3:55 am Post subject: Re: Adding and Subtracting a Time with MilliSeconds [Login to view extended thread Info.] Archived from groups: per prev. post (more info?) 


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(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
>>
>> "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.
>>
>>
>
> 

Back to top 



