Power AutomateでExcelの時間の取り扱い

私はPower Automateを使用して自動化する際にはExcelと組み合わせることが多いです。 その際、基本データの取り扱いは大丈夫なのですが、1点日付だけは扱いが少し面倒なのです。なにが面倒なのかというと、Excel上で見えているデータと実際に格納されているデータが異なるのです。 試しにPower Automate上で日付データを取り出してみましょう。
「2021/12/1〜2021/12/3」をPower Automateで取得し表示してみると下記のように表示されます。

44531,44532,44533と表示されていますね！このようにExcelでの日付データは数値で管理されており、そのまま取り出すと数値のまま表示されてしまうのです。
そのため、yyyy/MM/ddのように表示させるためにはひと手間ステップを増やす必要があります。今回はこちらをまとめていきたいと思います。

なおこの内容はYouTubeにも挙がっています。合わせて参考にしてください。

Excel上での日付管理

先程Excelでは日付データは数値で管理されていることを説明しました。まずはこの「日付データの数値」と「yyyy/MM/dd」の関連性を説明します。
先に結論から言うと「1900/1/1」が基準となっており、これが数値の「1」に当たります。つまり「日付データの数値」が「1」から増えた分だけ「1900/1/1」から日付を増やせば、その日付が求められます。
「2021/12/1」と「44531」の関係性で見ると、「44531-1=44530」つまり「1900/1/1」の「44530」日後が「2021/12/1」ということになります。
※後述しますがとある仕様によりズレが生じます。。

これをPower Automate上で実装するには「1900/1/1」から何日後かという計算を行うステップを追加します。
ただ上記の計算では面倒なので事前に「-1」した日付「1899/12/31」を基準にすることにします。

ステップの実装

それでは実際にPower Automate上でステップを実装してみましょう！
普段どおりサンプルのフローを作成して説明するので、「スケジュール済みクラウドフロー」で作成します。
まずはサンプルのデータテーブルは下記です。

1. 確認用の変数を用意

結果の確認用に変数を用意しておきます。
文字列型の変数「str」を作成します。

2. まずはテーブルの取得

テーブルを取得するためExcelの「表内に存在する行を一覧表示」を選択、対象のテーブルを指定します。


3. 取得データを変数に格納

取得したテーブルデータを扱うために「Apply to each」を使用します。



• 1899/12/31から加算

「日時」の「時間への追加」を選択し、下記のように値を入力します。



• 変数に格納

データを確認するため、先程用意した変数「str」に取得データ「日付」と「算出時間」を入力します。




4. 出力用変数に格納

フローテストの際に変数「str」の格納データを確認するため、再度文字列変数に格納します。

それではフローテストを実施して、値を格納してみましょう！
テスト結果は下記です。

あれ？？？「1900/1/1」はあっているのに、「2021/12/1」以降のデータがおかしくなっています。。。1日ずれていますね。
そうなんです。Excel上での日付管理のところで説明しました計算式自体は合っているのですが、とあるExcelの仕様により日付がずれるのです。
調べてみたところ、Excel上に存在している「1900/2/29」が原因なのですが、ここだけ聞くと「うるう年じゃん」と簡単に済みそうです。しかし、うるう年には面倒な仕様があるのをご存知でしょうか？

4で割り切れる年はうるう年
　 100で割り切れる年はうるう年にはしない
400で割り切れる年はうるう年にする

1900年は4で割り切れるものの100でも割り切れてしまうので、うるう年ではありません。(400では割れない)
つまり「1900/2/29」は存在しないはずなのに、Excel上には存在しているのです。試しにExcel上で「1900/2/29」を入力すると「60」が認識されます。
調べたところ、これはExcelの仕様らしいです。。。そのため、1900/2/29以降の日付を計算から求めようとすると1日ずれるのです。なのでステップ作成の際には基準を「1899/12/31」ではなく「1899/12/30」を設定する必要があるのです。

なのでステップを下記のように修正します。（1900/2/28以前の日付を算出しない前提ですが）

再度テストを実施すると下記のような結果が得られます。これで正しく算出できていますね。（1900/1/1は対象外）

表示を工夫する

今のままですと表記が「2021-12-02T00:00:00.0000000」のようになってしまうので、これを「yyyy/MM/dd」で表現したいと思います。
使用する関数は「formatDateTime」です。入力の仕方は下記です。

formatDateTime(body('時間への追加'),'yyyy/MM/dd')

第一引数には対象の数値、第二引数には出力フォーマットを指定します。これをステップに反映させて表示させると下記のようになります。

このようにformatDateTimeを使用することで日付の表示はある程度自由に変更することができます。

なお、「時間への追加」で得られたデータはそのまま比較ができるので、本日から何日後かのように条件分岐も行えます。

まとめ

ということで今回は、Excelの日付データをPower Automateで取り扱う方法をまとめました。まさかのExcelにあのような仕様があったのは知らなかったので、まとめるにあたり色々勉強になりました。
やっぱりPower Automateは便利かつ、Officeソフトとの親和性も高いのですが、こういったデータの取り扱いにはひと手間入れないといけないので少し面倒ですね。ましてやExcelの仕様には対応していないので、自分でどうにかしないといけないので。。。
と文句ばかり言っていてもいけないので、日付を取り扱う際には今回説明したステップを実装して行きましょう！
今回はこのへんで、ではまた！！