[Excel] 協定世界時(UTC)を日本標準時(JST)に変換する計算式

Microsoft 365

RESTで取得したRedmineのチケットなどでは、XML要素の「created_on」や「closed_on」の値が次のようになってます。

2006-12-30T10:35:00Z

 

これは、ISO8601(日付と時刻の表記に関する国際規格)で定められた拡張形式です。
因みに、基本形式では年月日のあいだの"-"(ハイフン)がありません。
また、最後に「Z」が付いているのは、この時刻が協定世界時(UTC)であることを示しています。

この値を日本標準時(JST)で表すと、協定世界時より9時間進んでいるので、

2006-12-30T10:35:00+09:00

となり、

さらに、時差を調整した形式で表すと、

2006-12-30 19:35:00

となります。

UTC:Coordinated Universal Time
JST:Japan Standard Time

スポンサーリンク

日本標準時(JST)に変換するExcel計算式

セル"M2"に協定世界時(UTC)「2006-12-30T10:35:00Z」がある場合、日本標準時(JST)に変換するExcelの計算式は次のようになります。

=DATEVALUE(MIDB(M2,1,10))+TIMEVALUE(MIDB(M2,12,8))+TIME(9,0,0)

文字列から切り出した日付と時刻を各々シリアル値に変換して、時差の9時間を加算します。
時刻は、シリアル値の小数点以下で表されます。

 

計算式の利用方法

XMLファイルをExcelにインポートして、実際に変換してみましょう。

右側の空いているところに変換用の列を3列用意します。
※ created_on(S列)、updated_on(T列)、closed_on(U列)

先の計算式を、用意した列の左上隅のセル"S2"に入力します。

計算式を入力

 

次に、セル"S2"の計算式を周辺のセルにもペーストします。次のように操作すると簡単に範囲を指定して一度にペーストできます。

  1. セル”S2”を選択。
  2. CtrlCでセル”S2”をコピー。
  3. CtrlShiftEndで範囲選択。
  4. Enterで選択範囲にペースト。

計算式を周辺のセルにペースト

 

これで変換が終わりました!簡単ですね(^^)
[created_on]と[updated_on]は必ず値が入っていますが、完了していないチケットの場合、[closed_on]には値が入っていないため、計算式の結果が「#VALUE!」になっています。

 

エラーになったセルの計算式を一括削除

フィルターを使うと、このエラーになったセルの計算式を、一度に削除できます。

一行目のタイトルを選択した状態で、[データ]メニューから[フィルター]をクリックすると、フィルターが使えるようになります。[closed_on]のフィルターを「#VALUE!」だけ抽出するように設定して[OK]ボタンをクリックします。

#VALUE!でフィルタ

 

抽出された「#VALUE!」のセルをすべて選択してDeleteで削除します。削除ができたらフィルターを解除します。

#VALUE!でフィルタした結果

 

変換した値を元のセルに上書き保存

先ず、計算式の入った変換後の列全体をコピーします。[形式を選択して貼り付け]の貼り付けから「値」を選択して[OK]ボタンをクリックします。

形式を選択して貼り付け

 

計算式が無くなって値だけになります。列全体を切り取って、元のデータの列に貼り付けて入れ替えます。

計算式が無くなり値だけになったシート