よくある記法のスプリットタイムをラップタイムにする #GoogleSpreadSheet

経緯

Googleスプレッドシートにて。
スプリットタイム→ラップタイムを計算させたいのに、分未満の ss.00 は浮動小数点扱いになり、分超えの m:ss.00 は文字列扱いになっていた。
これを数式ゴリ押しでシリアル値計算に揃えて計算させてみた。

注意点

50Mスプリット=M6、100Mスプリット=N6 として、50M-100Mのラップを算出
どちらかが空欄だったときの対応も入れている
Googleスプレッドシートで作成 → Excelで動作確認しているので両対応のはず
Excelは m:ss.00 と入力した値をシリアル値として認識してくれるようです
- 引く側引かれる側両方がそうなっていれば下記の数式を使わなくとも普通に計算できますが、片側が s.00 (つまり分未満）だった場合、 シリアル値 - 浮動小数点値 となるため、下記の数式ごときではマトモな結果になりません。

数式

=IF(
    N6=0,
    "",
    TIME(
        IF(
            LEN(N6)-LEN(SUBSTITUTE(N6,":",""))>1,
            LEFT(N6,FIND(":",N6)-1),
            0
        ),
        IF(
            LEN(N6)-LEN(SUBSTITUTE(N6,":",""))>1,
            MID(N6,FIND(":",N6)+1,FIND(":",N6,FIND(":",N6)+1)-FIND(":",N6)-1),
            IF(
                LEN(N6)-LEN(SUBSTITUTE(N6,":",""))>0,
                LEFT(N6,FIND(":",N6)-1),
                0
            )
        ),
        IF(
            LEN(N6)-LEN(SUBSTITUTE(N6,":",""))>1,
            RIGHT(N6,FIND(":",N6,FIND(":",N6)+1)),
            IF(
                LEN(N6)-LEN(SUBSTITUTE(N6,":",""))>0,
                RIGHT(N6,LEN(N6)-FIND(":",N6)),
                N6
            )
        )
    )
    +TIMEVALUE("0:00:00"&RIGHT(N6,LEN(N6)-FIND(".",N6)+1))
    -IF(
        M6=0,
        0,
        TIME(
            IF(
                LEN(M6)-LEN(SUBSTITUTE(M6,":",""))>1,
                LEFT(M6,FIND(":",M6)-1),
                0
            ),
            IF(
                LEN(M6)-LEN(SUBSTITUTE(M6,":",""))>1,
                MID(M6,FIND(":",M6)+1,FIND(":",M6,FIND(":",M6)+1)-FIND(":",M6)-1),
                IF(
                    LEN(M6)-LEN(SUBSTITUTE(M6,":",""))>0,
                    LEFT(M6,FIND(":",M6)-1),
                    0
                )
            ),
            IF(
                LEN(M6)-LEN(SUBSTITUTE(M6,":",""))>1,
                RIGHT(M6,FIND(":",M6,FIND(":",M6)+1)),
                IF(
                    LEN(M6)-LEN(SUBSTITUTE(M6,":",""))>0,
                    RIGHT(M6,LEN(M6)-FIND(":",M6)),
                    M6
                )
            )
        )
        +TIMEVALUE("0:00:00"&RIGHT(M6,LEN(M6)-FIND(".",M6)+1))
    )
)

結果

M6(50M Split)	N6(100M Split)	O6(50-100M Rap)
32.38	1:08.17	35.79

※上記はあくまで参考値であり、私のタイムではありません