【備忘録】ルートヒストリーの日時を UTC から JST に変換

ハイドラのデータを補完するために使用している「ルートヒストリー」では記録される日時の文字列が「2024-12-21T19:06:56Z」という形式。

日付の後ろの「T」は、時刻を表示する際に日付の後ろに付されることになっている記号。
最後の「Z」は時刻が UTC（世界協定時）とのずれが 0°であることを意味する。
JST（日本標準時）は東経135度分の時差を有するので対応するアルファベットは「I」。（Wikipedia 「世界協定時」の項を参照）
UTC で表示されている時刻を JSTに直すには 9時間だけ加算するので
2024-12-21T00:00:00Z は 2024-12-21T09:00:00I である。

日付と時刻を分けて時刻だけ ＋9時間すると日付をまたぐ場合に面倒なので、Excel上で書式を日付とした状態で ＋9時間すれば日付も正しく表示される。


元の日時文字列が C1セルにある場合、まず「T」を空白に換えて「Z」を削除する。
具体的には
「=LEFT(C1,10)&" "&MID(C1,12,8)」
または
=LEFT(REPLACE(C1,11,1," "),19)
とする。
文字列置換にSUBSTITUTE関数を使えば文字位置を指定する際のミスが防げる。
（　=SUBSTITUTE(SUBSTITUTE(C1,"T"," "),"Z","")　）

これによってデータは自動的に日時形式になるので、09:00:00 を加えれば JSTになる。

得られた値を列ごとコピーして値貼り付けし、変換に使用した列を削除する。


1 ファイルを選択
2 右クリックで Excel から開く
3 はい(Y)
4 OK　XML テーブルとして(T)


5 エラーが出るが OK
6 データが開く
7 テーブルデザインタブの左下「範囲に変換」
8 確認で はい(Y)


9 メニューがテーブルのものから通常のブックのものに変わっている
10 A・B・F列を削除　F列(高さ) は削除しなくても良い
11 別ファイルからコピー＆ペースト（赤字部分）
12 見やすいように列を拡大　　D列の計算式：書式を文字列から日時に変換


13 E列は UTC と JST の差（9時間）
14 F列が JST に変換された値
15 赤字の最下行を選択
16 フラッシュフィル（右下角の＋をダブルクリック）


17 右端列を選択してコピー
18 同じ列に値貼付
19 計算式から日時データに変換された
20 C～E列を選択して削除


21 名前を付けて保存
22 CSV形式を選択
23 ファイル名は任意
24 保存終了


25 ダブルクリックで開く
26 C列を拡大して表示　セルには分までしか表示されないがデータは秒まで保持されている


2025.6.4　追記
手順21 で保存する前に、日時の列の書式設定を「時刻」に変換しておく必要がある。
これをしない場合、行削除等の操作を行うと秒のデータが消えてすべて「00秒」になってしまうことが分かった。