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

2019年9月2日 2021年8月12日 4分33秒

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

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

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

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

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

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

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

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

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

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

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

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

DateTimeAdd(dt, i, u)

dt：計算元のフィールド（日付型）

i：加減する数値（整数値）

u：加減する単位（ユニット）

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

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

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

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

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

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

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

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

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

実は、DateTimeAddを使わずともToDate、ToDateTime関数がすべてを解決します。ヘルプによると、Excelのシリアル値をそのままToDate、ToDateTime関数は日付に変換することができます。

※2021/8/12追記

※Alteryx Designer 2021.3.1.47945時点の情報です