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

EIICHI

9年前

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

2006-12-30T10:35:00Z

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

協定世界時(UTC)との時差(+9時間)で日本標準時(JST)を表すと、

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

となり、さらに時刻に時差を含めると、

2006-12-30T19:35:00

となります。

UTC：Coordinated Universal Time
JST：Japan Standard Time

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

Excelで日付や時刻を扱うには、1900年1月1日からの経過日数を表す「シリアル値」に変換すると便利です。時刻はシリアル値の小数点以下で表されます。

シリアル値に変換した協定世界時(UTC)に、時差+9時間のシリアル値を加算すれば、日本標準時(JST)になります。

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

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

計算結果は、日付時刻を示すシリアル値「39081.8159722222」になります。

XMLファイルをインポートしたExcelシートで、実際に変換してみます。
created_on(M列)、updated_on(N列)、closed_on(O列)には、協定世界時(UTC)が文字列で入っています。

最初に、右側の空いているところに変換用の列を3列用意して、用意した列の左上隅のセル(S2)に、先の計算式を入力します。

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

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

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

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

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

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

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

シリアル値は、書式設定によって見え方を変化させることができます。

先のシートのセル”M12″「2007年3月7日 0時49分」の場合、
セルの値は、日付時刻を示すシリアル値「39148.0340277778」ですが、

と、シート上の見え方は変化します。

Excelにはセルの値から推測して自動的に書式を設定する機能がありますが、期待通りにならない場合は、変更してみましょう。

最後までご覧いただき、ありがとうございます。
では、また。