[変換/Excel] ISO8601(UTC)をローカル日時に変更する計算式

WEBブラウザから、RESTでRedmineのチケットを取得すると、XML要素の「created_on」や「updated_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

となります。

この後『A5:SQL Mk-2』で「Issues」テーブルに投入するには、CSVファイルにエクスポートする前に、データをこの形式に変更しておく必要があります。

先日の「区切り位置」による変更のような”うまい方法”がないか色々調べてみましたが、結局見つからなかったので、計算式で変換することにしました。

スポンサーリンク

ISO8601(UTC)をローカルな日時に変換する計算式

セル”M2″にISO8601(UTC)の文字列があるとすると、計算式は次のようになります。

文字列から切り出した日付と時刻の箇所を各々変換して、時差の9時間を加算します。とてもシンプルでわかりやすいですね。この計算式をシート上の空いているセルで使用します。

計算式の利用方法

XMLファイルをインポートしたExcelブックを開いて、右側の空いているところに変換用の列を3列用意します(created_on、updated_on、closed_on用です)。先の計算式を、用意した列の左上隅(2行目)のセルに入力します。

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

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

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

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

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

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

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

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

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

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

後は、変換や加工の必要がないフィールドをいくつか追加して完成です。

最後までご覧いただきありがとうございます。
スポンサーリンク

フォローする