はじめに
本記事の対象者
- Power Automateで文字列の日付からシリアル値に変換する方法を知りたい人
本記事の目的
- Power Automateで文字列の日付からシリアル値に変換する方法を知る。
PowerAutomateで文字列の日付からシリアル値に変換する方法
2021年2月から無料版Power Automateを使って今まで貯めてきたノウハウを解説していきます。
自分のノウハウを以下の記事にまとめました。
もしよろしければ、確認してみてください！！
今回の紹介するフロー
PowerAutomateでExcelテーブルから日付を取得すると、シリアル値になっていて、単純に文字列の日付の比較ができないから、抽出条件に入れられない。。
PowerAutomateで文字列の日付をナノ秒にする関数があるので、基準日(後述します)との差を求めて、シリアル値を求めることができますよ。
ということで、今回は文字列の日付からシリアル値に変換するフローを紹介します。
下のフローが今回作成するものです。
数式内にそのまま数値を入れればいいところも、あえて変数に入れていたりするので、もっと短くすることもできます。
そこについてはご了承下さい。
シリアル値と日付の関係性について
自分もはじめExcelテーブル上で、日付から数値に変わった瞬間に、「え、なにこの数値。。」となっていました。
ネットで色々検索したところ、この謎の値は「シリアル値」らしいきものだということがわかりました。
自分が理解したものをイメージ化して、下のような図を作りました。
結局は、Excel内で基準日(1899/12/30)の情報を持っていて、それに値(シリアル値)を加算したものが日付になっているということでした。
今回は、上の図の「基準日」と「対象日(本記事では指定日)」を用いて、「シリアル値」を出します！
今回のフローにおける仕様のコメント
今回のフローの流れを下に書きます。
- ボタンを押下して、フローを実行
- 文字列の日付をタイムスタンプにデータ型に変換
- タイムスタンプの日付をナノ秒に変換
- ナノ秒にした指定日と基準日の差分を取得
- 差分のナノ秒を日付に変換してシリアル値を算出
PowerAutomateで文字列の日付からシリアル値に変換するフローのコネクタ説明
まずはトリガーから説明していきます。
トリガー(手動でフローをトリガーします)：同左
トリガーは、「手動でフローをトリガーします」です。
このコネクタでは、PowerAutomateをインストールしたスマホで、アプリ上のボタンを押下したら、フローを実行するものです。
テストのために入れただけで、特段意味はないため、説明はないです。
コネクタ1(タイム ゾーンの変換)：基準日定義
コネクタ１は、「タイム ゾーンの変換」です。
このコネクタでは、文字列の日付をタイムスタンプに変換します。
基準時間が基準日「1899/12/30」で文字列の日付を入れていて、「書式設定文字列」で指定したフォーマットでタイムスタンプのデータ型にします。
「基準時間のフォーマットと書式設定の文字列が同じだけど、何やってんの？意味あるの？」となる方もいるかもしれませんが、単純にタイムスタンプのデータ型にしたいだけです。
念のため、コネクタ1の設定値については、表にまとめておきます。
| No | 設定項目名 | 設定値 | 備考 |
|---|---|---|---|
| 1 | 基準時間 | 1899/12/30 | Excelの基準日で、不変の日付なので、おまじないのように覚えてしまっていいです！ |
| 2 | 書式設定文字列 | yyyy/MM/dd | 自分が好きな日付のフォーマットなだけなので、タイムスタンプ型にできれば他のでもいいと思っています。 |
| 3 | 変換元のタイムゾーン |
(UTC+09:00)大阪、札幌、東京 |
日本であればいいので、ここもおまじない。 |
| 4 | 変換先のタイムゾーン |
(UTC+09:00)大阪、札幌、東京 |
同上 |
コネクタ2(タイム ゾーンの変換)：指定日定義
コネクタ2は、「タイム ゾーンの変換」です。
このコネクタでは、コネクタ1と同様に文字列の日付をタイムスタンプに変換します。
画像をご覧の通り、項目名「基準時間」がただ指定日(2022/02/01)になっているだけで、コネクタ1と同じです。
そのため、コネクタ2の設定値については、割愛します。
コネクタ3(変数を初期化する)：指定日をナノ秒に変換
コネクタ3は、「変数を初期化」です。
このコネクタでは、コネクタ2でタイプスタンプに変換した指定日を、ticks関数を用いてナノ秒に変換しています。
具体的な式は下の画像のようになります。
単純にticks関数に動的なコンテンツに表示されているコネクタ2の項目を入れればいいだけです。
変換内容は実行結果の所で確認したいと思います。
コネクタ4(変数を初期化する)：基準日をナノ秒に変換
コネクタ4は、「変数を初期化」です。
このコネクタでは、コネクタ1でタイプスタンプに変換した基準日を、ticks関数を用いてナノ秒に変換しています。
具体的な式は下の画像のようになります。
コネクタ3と同様のため、説明は割愛します。
コネクタ5(変数の値を減らす)：指定日から基準日のナノ秒を引く
コネクタ5は、「変数の値を減らす」です。
このコネクタでは、指定日のナノ秒から基準日のナノ秒を引いて、ナノ秒の差分を算出します。
ここは式でsub関数を用いて減算が出来ますが、あえて変数のアクションを使っています。
自分だけかもしれないのですが、PowerAutomateの無料版だと式の値の設定が不安定で、更新を押しても設定がされないことがあるというのも、このアクションを入れている理由です。
コネクタ6(変数を初期化する)：１日のナノ秒定義
コネクタ6は、「変数を初期化する」です。
このコネクタでは、１日分のナノ秒を設定しています。
後ほど、コネクタ7で基準日と指定日の差分を、ナノ秒単位から日付単位に変換するための数値になります。
ここも、コネクタ5と同様に式に直接入力すれば、不要のアクションです。
項目名「値」の「864000000000」も、基準日の日付と同様に不変のため、おまじないのように覚えてしまった方がいいです。
個人的には、0が２桁足りなくない？っと思っていますが、上の数値で正しく計算できます！(笑)
コネクタ7(変数を初期化する)：差分を日単位に変換
コネクタ7は、「変数を初期化する」です。
このコネクタでは、基準日と指定日の差分を、ナノ秒単位から日付単位に変換します。
具体的な式は下の画像のようになります。
画像では式が途切れているので、全部書くと以下のようになります。
div(variables(‘指定’),variables(‘１日分のナノ秒’))
やっていることとしては、以下の式になります。
これで説明は以上になります！
実行結果
それでは、今回紹介したフローを実際に実行してみましょう！
全てが正常終了していることがわかります。
それでは、個人的に重要だと考えているアクションの結果を確認してみます。
コネクタ1の実行結果は以下の通りです。
コネクタ1の説明でもあったように、本当に入力の日付と出力の日付は変わらないですね。
そうですね。タイムスタンプのデータ型にしてticks関数で処理できるするのが目的なので、
そのようになっています。
コネクタ3の実行結果は以下の通りです。
あっているかわからないですが、ナノ秒っぽいですね！
あっているか確かめたい方は、2022/2/1のナノ秒を計算してみてください。
上の計算結果と同じになるはずです。
続いて、コネクタ5の実行結果は以下の通りです。
まぁ、あれです。こちらもあっているか確認したい人は確認してください。。
最後に、コネクタ7の実行結果です。
シリアル値が、44593になっていることが分かりますね。
はい！下の記事で、シリアル値から日付を算出する方法を紹介してましたけど、
基準日に44593日を足すと「2022/2/1」になってたので、正解ですね！
以上が実行結果です。
補足情報 今回のフローの想定用途
今回フローの用途は、Excelテーブルの日付を抽出した際のシリアル値と比べるためのシリアル値を作成することを想定しています。
もっと具体的には、Excelテーブルの台帳で期限が近づいた際に、リマインドするための基準日をシリアル値に変換することになります。
また、別記事で作成します。
まとめ
まとめに入ります。
業務効率を上げるために提供されているMicrosoft365のツール群で、今回はPowerAutomateで文字列の日付からシリアル値に変換する方法を説明しました。
今回フローの用途は、Excelテーブルの日付を抽出した際のシリアル値と比べるためのシリアル値を作成することを想定しています。
逆にシリアル値から日付を作成するのは、以下の記事を参考にしてください。
最後まで読んでいただきありがとうございます。
コメント