Search code examples
excelmilliseconds

how to substract milliseconds (time with milliseconds as text)

is there a way for me to make this work?

the time is formated as TEXT

Solution

  • Create a new custom cell format. If you want to simply display the subtracted values as "0:00.500" (the value for the Column F cell outputting #VALUE), select General in the ribbon and then More Number Formats. Select the last option Custom to input the following in the Type field:

    m:ss.000

    This will allow for numerical values to calculate, so you can copy down your formula. If you want the cells to look exactly like the format you have in the last Column F rows (+0,000), use this custom type:

    +_:s.000

    (I am in the U.S., so I believe your cell will output with a comma in lieu of a period for the millisecond decimal point). FYI, cells with the regular Text format cannot be subtracted, added, etc. The format must be custom.