Excellで2030-04-01T14:20:00Zを別の形式に変換する

スポンサーリンク

1. 実現したいこと

  • YYYY-MM-DDTHH:MM:SSZ 形式の日時をエクセルで取り扱える形式に変換する
  • 変換した時刻をさらに日本時間に変換する

YYYY-MM-DDTHH:MM:SSZ はISOで定められた規格 (ISO8601) に従った表記となります。末尾のZはこれがUTCであることを示しています。日本時間はUTCとの時差が9時間なのでYYYY-MM-DDTHH:MM:SS+09:00 のような表記となります。

2. 変換方法

2.1. 日付を取り出す

日付は下記の関数で取り出すことができます。

=DATEVALUE(MIDB([変換したい値],1,10))

例えば B2セルに変換したい値がある場合には =DATEVALUE(MIDB(B2,1,10)) のように指定します。下記の例ではC2セルに関数をいれています。

MIDB(B2,1,10) 関数で B2 セルの1文字目から10個の文字、すなわち 2030-04-01 を文字列として取り出しています。さらに =DATEVALUE(2030-04-01) で文字列を Excel で日付として認識できるシリアル値に変換しています。

なお、変換後の日付が想定どおりに表示されない場合は「表示形式」を変更してください。

2.2. 時刻を取り出す

日付を取り出す方法と同じ考え方です。MIDB(B2,12,8) 関数で B2 セルの12文字目から8個の文字、14:20:00 を取り出し TIMEVALUE 関数でエクセルが時刻として取り扱える形式に変換しています。

=TIMEVALUE(MIDB([変換したい値],12,8))

2.3. 日付と時刻を取り出す

上記で説明した関数を + でつなぐだけです。

=DATEVALUE(MIDB([変換したい値],1,10)) + TIMEVALUE(MIDB([変換したい値],12,8))

変換後の日付は「セルの書式設定」の表示形式でお好みの形式に変更してください。

2.4. 日付と時刻を日本時間 (JST) として取り出す

取り出した値に +TIME(9,0,0) で9時間0分0秒を足し、UTCから日本時間(JST)に変換しています。

=DATEVALUE(MIDB([変換したい値],1,10)) + TIMEVALUE(MIDB([変換したい値],12,8))+TIME(9,0,0)

以上。

<参考>
https://www.smartftp.com/en-us/support/kb/2684

コメント