今回はPowerAutomateの日時とExcelの日時の相互変換についてのお話です。
すでにそこら中で語られている内容ではあるのですが、私なりの観点と見解でまとめておきたいということで。
Background
Power Automateは日時データをISO8601形式でフォーマットされた文字列として表現します。Power Automateにはデータ型としての日時型というものはなく、ただ「決められた形式にフォーマットされた」文字列型があるだけです。そしてこの日時データはタイムゾーンの情報を持ちます。
Excelは日時データをシリアル値という数値（正の実数）で表現します。こちらもデータ型としての日時型というものはなく、ただ「決められた方法で、日付を整数部分に、時刻を小数部分に、それぞれ変換した」数値型があるだけです。
日付（整数部分）は1900年1月1日を1としてそこからの経過日数を加えていく形で表現します。例えば1900年1月3日は3です。
ただし「1900年2月29日が存在したかのように数える」（グレゴリオ暦上は存在しない）Excelの仕様のため、日付計算の観点で言えば、実質的には1989年12月31日を1とみなすべし、さらにそこから1989年12月30日を0とみなすべし、こういうことになります。
一方、時刻は24時間を1.0として各時刻を0.0から0.999...の間の小数で表現します。例えば06:00は0.25です。
この日時データはタイムゾーンの情報を持ちません。
Problem
Power AutomateとExcelの間で日時データをやり取り（Power AutomateからExcelに書き込んだり、Excelから読み込んだり）するとき、この日時の変換をどのように行うかが問題となります。
Power Automate標準の機能で行う
以前 PowerAutomateのISO8601とシリアル値の相互運用の条件でも言及している 「表内に存在する行を一覧表示」「表に行を追加」などのアクションの「DateTime形式」設定を活用することで、Power Automateの日時データとExcelの日時データの相互変換を実現できます。
メリット
- 可読性が高い。アクションやフローが「何をやっているか」がわかりやすい。難しい計算式を読み書きする必要はなく、アクションの設定項目を見れば「何をやっているか」は明白。
- 生産性・品質に優れる。わかりやすければ結果としてフロー作成の生産性や品質が上がる。
デメリット
- 外的要因の影響を受ける。この設定が正しく機能するかどうかは、Excelワークブック側の「表示形式」とテーブル内のレコードの状態に依存する（詳細は前掲記事を参照のこと）。
-
タイムゾーンの扱いが曖昧になる。Excelのシリアル値にはタイムゾーンを表現するすべがないので、Power Automateからその値を読み取るとすべてUTCの値として理解される。例えばシリアル値
1.25は1900-01-01T06:00:00.000Zとみなされる。
式関数で行う
これは「DateTime形式」設定の登場以前からあった方法です。
シリアル値の仕様を踏まえて、以下のように式関数を組み合わせることで、日時データの相互変換を実現します。
Power Automate → Excel
Power Automateの日時データ（ISO8601形式文字列）をExcelの日時データ（シリアル値）に変換する：
div(sub(ticks( ISO8601形式文字列 ), ticks('1899-12-30T00:00:00')), 864000000000)
Excel → Power Automate
Excelの日時データ（シリアル値）をPower Automateの日時データ（ISO8601形式文字列）に変換する：
addSeconds('1899/12/30T00:00:00', int(string(mul(float( Excelシリアル値 ), 86400.0))))
前述の通り Excelの仕様で（シリアル値は1900年1月1日起算、ただし開始値が0ではなく1、さらに1900年2月29日問題が存在）、日時データ変換の計算に用いる基準日は 1899年12月30日 となります。
なお、Excelから取得したシリアル値は文字列型となるので、計算の際に注意が必要です。
コードサンプル
例えば 2021年12月24日12時58分00秒 を表す日時データの相互変換は次のようになります：
div(sub(ticks('2021-12-24T12:58:00'), ticks('1899-12-30T00:00:00')), 864000000000)
// => 44554.5402777778
addSeconds('1899/12/30T00:00:00', int(string(mul(float('44554.5402777778'), 86400.0))))
// => '2021-12-24T12:58:00'
メリット
- 外的要因の影響を受けない。変換処理の挙動がExcelワークブック側の「表示形式」やテーブル内のレコードの状態に依存しない。
-
タイムゾーンの扱いが明瞭になる。
ticks()関数はタイムゾーン付きの日時表現を渡すと内部でUTCに変換した上で計算をしてくれる。タイムゾーンなしの日時表現や 末尾がZで終わるUTCの日時表現を渡すとそのままUTCとして計算をしてくれる。タイムゾーンに関わる変換が関数使用を通じて明確になっている。
デメリット
- 可読性が低い。アクションやフローが「何をやっているか」がわかりにくい（少なくともこの日時データ変換の部分はわかりにくくなる）。
- 生産性・品質が劣る。わかりにくければ結果としてフロー作成の生産性や品質が上がる。
私の見解
私は総合点で言えば「式関数で行う」方式が優れていると感じています。
「標準の機能で行う」方式は可読性こそ優れるものの、処理結果が外的要因の影響を受けるために挙動を読みづらいものとなっています。
状況次第で起きたり起きなかったりする分かりづらいエラーに悩まされるリスクのある方式よりは、最初にきちんと正確な式を書いておけばいつでも正しく動く方式の方が優れていると考えています。