【Power Automate】Excelの「行の取得」のキー値に日付を使う方法

（この記事は、ある方のツイートとそれに対するMVPからのアドバイス、それを拝見した第三者の私が、アドバイスを紐解いて、自分の備忘録として書きなぐっている記事です。

読みやすい記事ではないかもしれませんがご容赦ください。）

少し前にこんなツイートを拝見しました。

えぇ━(*´・д・)━!!!
一緒やーーん！また日付操作に悩まされる…
_(:3 」∠)_#PowerAutomate pic.twitter.com/cV98yEDSuB
— まる🍑5550 (@wa_maru_gm) 2023年1月6日

Power Automate（クラウドフロー）にある、Excelの「行の取得」アクション。

キー列とキー値を設定して、それに合致する行を取得するというものですが、

まるさんは、この「キー列」に日付を設定した時に、うまく行が取得できずに悩まれている様子です。

Excelのセルに日付が入力されていても、本当は日付情報ではなくて「シリアル値」で入力されているため、Power Automateの「行の取得」のキー値に「日付情報」を入れてもうまく取得できないという感じでしょうか。

私がやっていること

私も同じようなシチュエーションで行を取得したい用事がありました。

その時は以下のようなことで対応しました。

私がやってるのは、日付列とは別に、日付列をText関数で変換した列を別で作っておいて、Automateの「行の取得」でそこをキー列にするという方法です。
「スマートじゃないなぁ」と思いながらも「今の自分にはこれしか思いつかない」のでこれでやってます。
Miyakeさんのアドバイス、自分も試してみよう pic.twitter.com/MWWobEB9dy
— yukio＠PowerPlatform勉強中 (@yukio_365) 2023年1月7日

「フローの可読性」といった意味ではいいかもしれませんが、

余計な列を1個作ることになり、なんとなく「スマートじゃない」と思っています。

まるさんからは「賢い」とお褒めの言葉を頂きましたが、この方法を取った理由は単純に

シリアル値で考えるのが面倒臭い

と思ったからでした（苦笑）

当時の自分に喝！

MVPからのアドバイス

まるさんのツイートに対して、今年Microsoft MVPを受賞されたMiyakeさんから、以下のようなアドバイスがありました。

検索時のキー値（'1/6/2023'で指定している箇所）を次の式で指定してみてください
add(div(sub(ticks('1/6/2023'),ticks('1/1/1900')),864000000000),2)
— Miyake Mito (@MiTo60448639) 2023年1月7日

Excelのシリアル値は1900/1/1からの日付、ticks()は1 月 1 日 0001 12:00:00 午前 0 時から指定したタイムスタンプまでの 100 ナノ秒間隔のティック数を返すため、その差分を埋めてやる必要があります。
（ツィートに気付かず、アドバイス遅れてスイマセン）
— Miyake Mito (@MiTo60448639) 2023年1月7日

ほうほう、何やら難しいことをやってるぞ！

ちょっと紐解いてみよう！

式を見てみると

・ticks関数

・sub関数

・div関数

・add関数

の4種類を使っています。

それぞれの部分で何をしてるかを分析してみたので、自分の備忘録として、以下にまとめてみようと思います。

(1)ticks関数

入れ子になっている関数の一番内側「ticks関数」から見ていきます。

公式ドキュメントは以下です。

learn.microsoft.com

これによると、ticks関数というのは

1 月 1 日 0001 12:00:00 午前 0 時から指定したタイムスタンプまでの 100 ナノ秒間隔のティック数 (または C# の DateTime.Ticks) を返します。

との事です。

は？？？

でもゆっくり読んでみると何となくわかりました。

西暦1年1月1日午前0時00分00秒を始点として、

そこから、

指定されたタイムスタンプまでの秒数を

ナノ秒に変換して

100で割った値を返す。

（↑「100ナノ秒間隔の」と書いてあるので）

ということだと思います。

(1)ticks関数検証

検証のためにこんなフローを組んでみました。

1個目は「西暦1年1月1日0:00:00」

2個目は「その1秒後」

ですね。

実行結果はこうなりました。

1個目は私の思った通り「0」が返ってきました。

2個目で返ってきた値は「10000000」つまり1000万

1秒をナノ秒になおすと「1000000000（10の9乗=10億）」ナノ秒で、

それを100で割ると「10000000（1000万）」

思った通りです。

まあ、回りくどい検証をしましたが、

ticks関数はそういった値を返す関数ということです。

(2)sub関数

次にやっていることを見るために、式の一部を抜粋すると

sub(ticks('1/6/2023'),ticks('1/1/1900'))

ticks関数で求めた「2023/1/6の値」と「1900/1/1の値」をsub関数で囲っています。

sub関数の公式ドキュメントは以下です。

learn.microsoft.com

sub関数は単純に「引き算の関数」ですね。

sub(ticks('1/6/2023'),ticks('1/1/1900'))

ということは、(1)で求めた

2023年1月6日の値（ナノ秒÷100）

引く

1900年1月1日の値（ナノ秒÷100）

ということになります。

Excelの「シリアル値」は1900年1月1日を基準にした値なので、

1900年1月1日からどのくらい遠いのか？

を知る必要があり、この引き算を入れているということみたいです。

(3)div関数

もう1個外側の関数まで広げて見ていくと

div(sub(ticks('1/6/2023'),ticks('1/1/1900')),864000000000)

subまでで求まった値と864000000000を、div関数で囲っています。

div関数の公式ドキュメントは以下です。

learn.microsoft.com

div関数も単純で「割り算の関数」です。

div(sub(ticks('1/6/2023'),ticks('1/1/1900')),864000000000)

ということは、

「(2)までで求まった値」÷「864000000000」

をしているということになります。

(3)なぜ864000000000？

1日は24時間

1時間は60分

1分は60秒

なので、1日は24×60×60＝86400(秒)です。

これをナノ秒に直すと86400000000000

100で割って864000000000

これで割ることによって、

単位が[ナノ秒/100]から[日]になり、

1900/1/1から何日たっているか？を知ることができる。

ということみたいですね。

(4)add関数

入れ子の最後は

add(div(sub(ticks('1/6/2023'),ticks('1/1/1900')),864000000000),2)

add関数です。

add関数の公式ドキュメントは以下です。

learn.microsoft.com

add関数は「足し算の関数」です。

といことは(3)までで求まった値に２を足しています。

なぜ？

ここが分からなかったので、ツイートにリプライして聞いてみました。

すると、以下のような返答を頂きました。

最後に2日分を追加している理由は下記です。
①Excelには1900/02/29というありえない日付が存在しているため
②ticksの返す値は、0001/01/01を0とするが、Excelのシリアルは1900/1/1を1とするため。
— Miyake Mito (@MiTo60448639) 2023年1月8日

なるほど、この2つの事情があるがために、

add関数を入れないと2日足りない状況が出来上がるんですね。