is there a way to display a time formatted as mm:ss.xxx (xxx = milliseconds) in mm"ss'xxx as in motorsport time tables, while keeping the time values (because I need to add them)?

I.e. from 1:22.623 to 1"22'623

CodePudding user response：

If you're willing to use a lookup table (hidden column) ... put your times into a column that you'll hide. Use the format given by Ron Rosenfield, then run a character replace on the .

A1 = time data

B1 = SUBSTITUTE(TEXT(A1,"mm\""ss.000"), ".", "'")