【AlteryxTips】Excelファイル読み込み時に日付がシリアル値になったものを修正する

AlteryxでExcelファイル読み込み時に日付がシリアル値になったものを修正する方法を紹介します

Excel読み込みで日付が化ける問題

Excelファイルを読み込んだ際、日付のフィールドが「2019年8月1日」のような表記ではなく、「43678」のような意味不明の表記になっていることがありませんか?

書式設定などで設定していてもおかしくなるケースがあり、困るケースがあるかと思います。

これ、理由はわかりませんが、Excelが日付を管理している「シリアル値」というものでなぜか読み込んでしまっているのが原因です。

Excelのシリアル値とは?

Excelのシリアル値は、「1900年1月1日」が基準となっています。内部的には文字列ではなく、数値で管理しています。

実際にこのシリアル値はExcel上で簡単に確認することができます。

確認する方法として、表示形式を「日付」から「数値」「標準」などに変更することで確認可能です。

Excelで日付をシリアル値に

Alteryx上で変換する方法とは?

Excelのシリアル値は、「1900年1月1日」が基準となっており、そこから1日経つと1加算されていきます。

例えば、「2019年8月1日」は、Excelのシリアル値だと「43678」となります。

1.Excelのシリアル値をAlteryxで日付に変換する具体的な方法

これをAlteryx上で表現するには、フォーミュラツールを使います。

Alteryxには、基準日に対して数値を加える関数「DateTimeAdd」というものがあるので、これを使います。

書式はこのような形です。

 

DateTimeAdd(dt, i, u)

dt:計算元のフィールド(日付型)

i:加減する数値(整数値)

u:加減する単位(ユニット)

※「dtにiをuという単位(年、月、日等の単位)で足す」という形で使用します

 

これをAlteryxでそのまま計算すると、なぜか「2019年8月3日」となり、「合わない!」ということになると思います。

ネタばらししますと、Excelのバグがあるため、実際の基準は「1899年12月30日」としなければならないです(詳細は英語版のコミュニティにも記載があります)。

 

実際のWFは以下の通りとなります。

Excelのシリアル値を変換するWF

なお、DateTimeAdd関数の加減の数値は整数しか受け付けないため、小数点があっても反映されません(本来、小数点部分は時間の部分になります)。

2.Excelのシリアル値をAlteryxで日時に変換する具体的な方法

1の方法では日付のみとなります。小数点がある場合は時刻まで入ったデータとなりますので、今度は時刻まで変換したいと思います。

Excelのシリアル値を時刻まで変換するWF

今回は、小数点部分を必要とするため、FLOOR関数を使って小数点部分を切り出しています。

その後、小数点部分に24(時間)×60(分)×60(秒)=86400(秒)を掛け算することで、秒に戻しています。その秒を日付部分に足すことで最終的な結果が得られます。

DateTimeAdd関数は整数値のみ足す仕様(小数点部分は無視)になっているため、時刻も反映したい場合は2段階で行う必要があります。

まとめ

  • Excelのシリアル値をAlteryxで変換する方法を紹介しました
  • 日付のみの場合、日時が必要な場合についてそれぞれ変換方法を紹介しました

サンプルWFダウンロード

Excelのシリアル値を日時に変換

 

※Alteryx Designer 2019.2.7.63499時点の情報です

おすすめの記事