独自の日付形式をスプレッドシートで認識可能な数値に変換する

先日の記事でインターネット速度計測結果をスプレッドシートに自動記録するようにした。

これをダウンロード/アップロード速度の日付順でグラフ表示するためにはひと手間加える必要があった。

速度の単位変換

CSVで書き出した結果はbit/sになっているのでCONVERTを使ってMbit/sに変換する。

=CONVERT(G2,"bit","Mbit")

日付の変換

CSVで書き出した結果は以下のフォーマットになっており、スプレッドシートで日時として認識されない。

2019-11-06T15:00:05.841452Z

これを日時として認識できるように変換する必要がある。

まずREGEXEXTRACTを使って日付と時間をそれぞれ取得できるようにする。

=REGEXEXTRACT(D2, "\d{4}-\d{2}-\d{2}")
=>2019-11-06

=REGEXEXTRACT(D2, "\d{2}:\d{2}")
=>15:00:05

次にDATEVALUEとTIMEVALUEを使って日時の文字列をシリアル値に変換する。

=DATEVALUE(REGEXEXTRACT(D2, "\d{4}-\d{2}-\d{2}"))
=>43775

=TIMEVALUE(REGEXEXTRACT(D2, "\d{2}:\d{2}"))
=>0.625

そして、+で日付と時間を結合する。

=DATEVALUE(REGEXEXTRACT(D2, "\d{4}-\d{2}-\d{2}"))+TIMEVALUE(REGEXEXTRACT(D2, "\d{2}:\d{2}"))

最後に日本時間に変換するため9時間追加し、「表示形式」 => 「数字」 => 「日時」で日時表示に切り替えると正しい日時が表示されるようになる。

=DATEVALUE(REGEXEXTRACT(D2, "\d{4}-\d{2}-\d{2}"))+TIMEVALUE(REGEXEXTRACT(D2, "\d{2}:\d{2}"))+TIME(9,0,0)
=> 2019/11/07 0:00:00

あとは、変換後の日時、ダウンロード速度、アップロード速度それぞれの列を選択して折れ線グラフを作成すれば完成。