Home 
Search 
Today's Posts 
#1




Adding and Subtracting a Time with MilliSeconds
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. 
#2




Adding and Subtracting a Time with MilliSeconds
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. 
#3




Adding and Subtracting a Time with MilliSeconds
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. 
#4




Adding and Subtracting a Time with MilliSeconds
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. 
#5




Adding and Subtracting a Time with MilliSeconds
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. 
#6




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




Adding and Subtracting a Time with MilliSeconds
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. 
#8




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. 
Reply 
Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
Adding and subtracting time  Excel Worksheet Functions  
Comparing time values which have milliseconds in them e.g 10:20:30  Excel Discussion (Misc queries)  
Adding/Subtracting Time Help...  Excel Worksheet Functions  
adding/subtracting time  Excel Worksheet Functions  
Adding Subtracting Time FormulaHorse Racing  Excel Discussion (Misc queries) 