注意：この記事について
この記事を書いたときには、Power AutomateでExcelを読み取るとシリアル値になってしまったのでこの時期のような小細工を考えたのですが、2021年2月時点ではISO 8601形式での取得ができるようになりました。
日付で読み込めばほとんどのケースでは本記事のような計算をする必要はないと思いますので、ご注意ください。
また、そもそもシリアル値求めるまではよかったのですが、Excelを読み込む際のフィルタクエリではでシリアル値を使ったフィルタが出来ないことが分かりました。なのでフィルタ目的の場合は約に立たない可能性があります。ご了承ください。
はじめに
Power AutomateでExcelを読み取ったら、Excel上は日付になっているデータが、43936などの良くわからない数値になっていて困ったことはありませんか？私はあります。
対処方法
式で次のように入れれば、当日のシリアル値を求める事が出来ます。
add(div(sub(ticks(startOfDay(convertFromUtc(utcNow(),'Tokyo Standard Time'))),ticks(startOfDay(convertFromUtc('1899-12-31T00:00:00Z','Tokyo Standard Time')))),864000000000),1)
Excelのシリアル値と落とし穴
Excelのシリアル値は、既定では1900/1/1からの日数で計算されます。（"1900/1/1"を"1"とします）
が、ここに落とし穴があり、Excelの既知の問題で実際の日数より数値が1大きくなります
1900と1904の日付システムの相違点 | Microsoft Docs
この設計は、1900がうるう年ではないことに関連する問題を回避することを目的としています。
こちらにもまとまっています。
Excelの日付データは1900年1月1日から数えた連番ではない 1900年うるう年問題 - Qiita
解説
（解説読まなくても上の式をコピペすれば使えますが、応用したい人、何やってるか知りたい人は参考までに。）
Power Automateではシリアル値を直接扱うことは出来ません。また、日付を直接計算して差の日数を出すことも（調べた限りでは）できません。
ただ、Ticksという関数で日付を数値に変えることができます。
Power Automateの関数のヘルプでは以下のように書かれています。
文字列のタイムスタンプの、1601 年 1 月 1 日 00:00:00 UTC からのティック数 (100 ナノ秒間隔) を返します
難しいことは省きますが、ある日と他の日の差を数値データにすることが出来るので、引き算すれば差の日数をティック数で求める事が出来ます。
今回は、当日のExcelのシリアル値を求めたいので、
(当日)- (1899/12/31)= (1900/1/1からの日数) = (Excelのシリアル値)
書き換えると、
(Excelのシリアル値) = (当日)-(基準日)
となります。
日付のままだと計算できないので、それぞれのTicks値を計算します。
ticks(当日)-ticks(基準日)
ただ、Power Automateでは引き算の時にも関数を使いますので、その形で書き換えます。
引き算はsub関数を使います。
sub(A,B)
これでA-Bになります。代入すると
sub(ticks(当日),ticks(基準日))
となります。
ただ、ここで得られる値は確かに日付の差分ですが、単位が100ナノ秒です。
1日 = 24時間 = 24*60分 = 24*60*60秒 = 24*60*60*1000ミリ秒 = 24*60*60*1000*1000マイクロ秒 = 24*60*60*1000*1000*10 100ナノ秒
なので、計算すると
1日 = 864000000000ナノ秒
となります。
なので、Ticks数の差分を864000000000で割れば、日数の差を求められます。
割り算も関数を使います。
A/Bはこのように書きます。
div(A,B)
代入します。
div(sub(ticks(当日),ticks(基準日)),864000000000)
これで基準日を1989/12/31にすればほとんどExcelのシリアル値になるのですが、冒頭のExcelの既知の問題のため、実際の日付の差に比べてシリアル値は一つ大きくなります。
これを修正するために1足します。
A+Bは関数を使うと
add(A,B)
なので、代入すると
add(div(sub(ticks(当日),ticks(基準日)),864000000000),1)
となります。
さて、ここまで、(当日)や(基準日)と書いてきましたが、これもPower Automateで処理できるようにする必要があります。
当日はこのように求めました。
startOfDay(convertFromUtc(utcNow(),'Tokyo Standard Time'))
実行時の日時をutcNowで取得した後で、convertFromUtcで日本時間に直し、startOfDayでその日の0時に設定しています。
同様に、基準日になる1898/12/31を計算します。
startOfDay(convertFromUtc('1899-12-31T00:00:00Z','Tokyo Standard Time'))
これを先ほどの式に代入します。
add(div(sub(ticks(startOfDay(convertFromUtc(utcNow(),'Tokyo Standard Time'))),ticks(startOfDay(convertFromUtc('1899-12-31T00:00:00Z','Tokyo Standard Time')))),864000000000),1)
これで最初の式になりました。
おわりに
逆にシリアル値から日付を求めたり、シリアル値の小数点以下を計算して日時を扱う方法もあるようです。うまく出来たら記事にする予定です。