スイマーによるスプリント系スポーツのための表計算シリーズ第2弾。
（前回はこちら「よくある記法のスプリットタイムをラップタイムにする」）
困ったこと
記録会のタイムを入力していたらこんな感じ↓になった。
数字の解釈が見事にバラバラ。
（1時間超えてミリ秒取る種目ある？？てつっこみは置いといて）
例1. ただの数字になる
12.34→12.34（数字）
12秒34（のつもり）なのに、「あーはいはい12.34ね」と言われる。
表示だけするならそれでもいいけど、ラップタイムを計算したかったらこれではダメ。ちゃんと時間扱いになってる数字とは計算できない。100m1分未満で折り返せる人ばっかじゃないんですよ
例2. 文字列扱いになる
1:23.45→1:23.45（文字列）
文字列 です。計算なんてできたものじゃない。しようもんなら当然#VALUE!です。
例3. 秒より下が表示されない
1:23:45.67→表示1:23:46、内部1:23:45.67
実はこれが一番マシで、ちゃんと時間扱いになってる上に小数点未満も内部に持っています。
数式バーにも秒以下四捨五入の数字が入っているので、一見すると小数点以下が強制的に丸められてしまったように見えますが、表示形式からhh:mm:ss.00を指定するとちゃんと復活します。
解決方法（両方必要）
手順1. 正しい書式で入力する
まずは例1,2について、きちんと時間（差）として認識させる必要があります。
そのためには、例3と同様にh:m:ss.00で入力します。
そこまで遅くないという人もインド人に感謝して0で埋めてください。
これで時間形式の数値になりました。
手順2. 表示形式を変更する
表示形式を変更することで小数点未満が表示できます。
正直手順1さえできればこの問題はほぼ解決してますし、表示形式の変更なんてみんな知ってることでしょうが、手順として備忘も兼ねて書いておきます。
（私は例3で小数点未満が消えてしまったと思い込んで表示形式変更せず右往左往してましたが、こんなやつそうそうおらんやろ……）
「表示形式」→「数字」→「表示形式の詳細設定」→「カスタム数値形式」で
m":"ss"."00です。
ここはお好みで、1時間超える競技の人ならh":"m":"ss"."00、ミリ秒以下要らなければh":"m":"ss"、分単位を必ず2桁出したければh":"mm":"ss"."00、などなどカスタムしてください。
しかし面倒だ
長くて数分の競技なのに毎回時間から入れるとか面倒極まる
入力するときは最初に入れたようなフォーマットでササっと入れたい
後からなんかこう、いい感じにしてほしい
まあそうなりますよね。これです。
| パターン | 数式 |
|---|---|
| 例1 | =IFERROR(("00:00:"&[入力したセル])*1) |
| 例2 | =IFERROR(("00:"&[入力したセル])*1) |
| 例3 | 不要 |
いちいち人力で例1-3を判別するのはあまりにダサいので、まとめるとこれ↓（入力したセルをB2とする）
=IF(ISNUMBER(B2),IF(B2>1,IFERROR(("00:00:"&B2)*1),B2),IFERROR(("00:"&B2)*1))
これでついにいろんなパターンのタイムがすべて時間差として扱えるようになりました。
あとは足しても引いてもお好きなように！
いちおう解説しておくと、こういうことです。
数値である→例1か例3
1より大きい→例1
1未満 →例3
数値でない→例2
※日付形式になっている場合、内部では24時間を1とする浮動小数で評価されています。
参考
だいたいここに書いてありました。感謝。
How to Format Time to Millisecond Format in Google Sheets
蛇足
（ミリ秒って言ってるけど小数点以下2桁なので厳密にはセンチ秒かもしれない）