LoginSignup
9
11

More than 3 years have passed since last update.

@echolimitless

Power AutomateでExcel日付（シリアル値）を日付形式に変換する

Last updated at Posted at 2020-09-08

概要

Power AutomateでExcelから日付データ（日付書式が設定されているセルのデータ）を取得すると、日付形式ではなくシリアル値になってしまう。
Power Automate内部で利用するのにシリアル値では不便なので日付形式に変換します。

Excel日付のシリアル値

  • Excelでセルに日付の書式を設定すると表示は日付として見えるが、内部的にはシリアル値で記録されている。
    （日付の書式が設定されているセルの書式を標準にするとシリアル値が表示されます）
    image.png
    • 画像の赤枠部分がシリアル値
      • 日付：少数点より前の部分
      • 時間：小数点より後ろの部分
        • 時間は1/86400(24時間*60分*60秒)で1秒のシリアル値が計算できます。
        • 2分の場合
          • 1/86400*120
        • シリアル値から時間に戻す場合
          • シリアル値 * 86400
    • Power Automateで取り扱うため小数点で分割処理をしています。

手順

Excelデータの準備

  1. 日付のデータを入力したExcelを準備しました。
    image.png

  2. セルに日付の書式を設定します。（画像のように入力すると自動的に書式設定されています）
    image.png

    • 「表示形式」→ 日付
    • 「種類」→ 2012/3/14 13:30

  3. 「テーブルとして書式設定」をクリックして、お好みのデザインをクリックします。
    image.png

  4. 「テーブルとして書式設定」ダイアログの《先頭行をテーブルの見出しとして使用する》にチェックを入れて《OK》をクリックします。
    image.png

  5. テーブル化できたので、OneDrive for Businessに保存します。（SharePointでも大丈夫です）
    image.png

Power Automateの設定

Excelからシリアル値を取得

  1. Power Automate(https://flow.microsoft.com)のサイトを開きます。

  2. 左側のメニューより《作成》→ 《予定フローを作成》をクリックします。
    image.png

    • 今回は「予定フローを作成」を選択していますが、適宜あったメニューを選んでください。

  3. 必要項目を設定して《作成》をクリックします。
    image.png

    • フロー名：適当な名前を入力します。
    • その他必要な設定を行います。

  4. 作成画面が開きますので、《新しいステップ》をクリックします。
    image.png

  5. 「アクションを選択してください」の検索欄に「excel」と入力し、表示された《Excel Online(Business)》をクリックします。
    image.png

  6. 「Excel Online(Business)」アクションの中から、《表内に存在する行を一覧表示》をクリックします。
    image.png

  7. Excelデータの準備でOneDrive for Businessに作成したExcelを指定する。
    image.png

    • 場所：OneDrive for Business
    • ドキュメントライブラリー：OneDrive
    • ファイル：保存してあるフォルダーを選択する
    • テーブル：適当なテーブル名（何も設定していない場合は、テーブル１になる）

  8. 《新しいステップ》をクリックします。
    image.png

  9. 《組み込み》→《コントロール》をクリックします。
    image.png

  10. 《Apply to each》をクリックします。
    image.png

    • このアクションは、受け取ったデータが全てなくなるまで繰り返し実行します。

  11. 「Apply to each」の「以前の手順から出力を選択」ボックスをクリックします。
    image.png

  12. 「動的なコンテンツ」ダイアログから「表内に存在する行を一覧表示」の《value》をクリックします。
    image.png

    • Valueには「表内に存在する行を一覧表示」のアクションで取得したデータが入っています。

シリアル値を日付に変換

  1. 《アクションの追加》をクリックします。
    image.png

  2. 《組み込み》→《データ操作》をクリックします。
    image.png

  3. 《作成》をクリックします。
    image.png

  4. 「作成」の「入力」ボックスをクリックします。
    image.png

  5. 「動的なコンテンツ」ダイアログから「表内に存在する行を一覧表示」の《日時》をクリックします。（日時はExcelに作成したテーブルの見出し名です）
    image.png

  6. 《アクションの追加》をクリックします。
    image.png

  7. 《組み込み》→《データ操作》をクリックします。

  8. 《作成》をクリックします。

  9. 「作成」の「入力」ボックスをクリックします。

  10. 「動的なコンテンツ」ダイアログの《式》をクリックします。
    image.png

  11. 「文字列関数」の《もっと見る》をクリックしします。
    image.png

  12. 下にスクロールして、《split(text, separator)》をクリックします。
    image.png

  13. 《動的なコンテンツ》→「作成」の《出力》をクリックします。
    image.png

  14. 《更新》をクリックします。
    image.png

  15. 《アクションの追加》をクリックします。
    image.png

  16. 《組み込み》→《日時》をクリックします。
    image.png

  17. 《時間への追加》をクリックします。
    image.png

  18. 「時間への追加」アクションに以下の値を設定します。
    image.png

    • 設定値
      • 基準時間：1899/12/30
      • 間隔：int(outputs('作成'))
      • 時間単位：日
    • 「間隔」の設定方法
      • 「間隔」のボックスをクリックする。
      • 「動的なコンテンツ」ダイアログの《式》をクリックします。
        image.png
      • 下にスクロールして、「変換関数」の《int(value)》をクリックします。
        image.png
      • 《動的なコンテンツ》→「作成2」の《出力》をクリックします。
        image.png
      • 《OK》をクリックします。
        image.png

シリアル値を時間に変換

シリアル値を日付に変換とほぼ操作が同じなので画像は割愛しています。

  1. 《アクションの追加》をクリックします。

  2. 《組み込み》→《データ操作》をクリックします。

  3. 《作成》をクリックします。

  4. 「作成」の「入力」ボックスをクリックします。

  5. 「動的なコンテンツ」ダイアログの《式》をクリックし、以下の式を入力します。

    • 式：float(mul(div(int(substring(last(outputs('作成_2')), 0, 5)), 100000.0), 86400))
    • 説明
      • last(outputs('作成_2'))：小数点から後ろの数値を取得
      • int(substring(last(XXXX, 0, 5))：先頭から5桁を取得してint型に変換
      • div(XXXX, 100000.0)：除算して少数値にする。100000.0に.0（少数値）を付加しているのは結果をfloat型にするため。.0がないとint型になり少数以下が切り捨てられる
      • float(mul(XXXX, 86400))：秒数を計算してfloat型に変換

  6. 《更新》をクリックします。

  7. 《アクションの追加》をクリックします。

  8. 《組み込み》→《日時》をクリックします。

  9. 《時間への追加》をクリックします。

  10. 「時間への追加」アクションに以下の値を設定します。

    • 設定値
      • 基準時間：「時間への追加」の《算出時間》をクリックします。
        image.png
      • 間隔：「作成3」の《出力》をクリックします。
        image.png
      • 時間単位：秒

実行結果の確認

  1. これでフローが作成できたので正しくできているかテストするので、《テスト》をクリックします。
    image.png

  2. 「フローのテスト」画面が表示されるので、《トリガーアクションを実行する》を選択し、《テスト》をクリックします。
    image.png

  3. 《フローの実行》をクリックします。
    image.png

  4. 《完了》をクリックします。
    image.png

  5. 実行結果画面が表示されるので、《Apply to each》をクリックします。
    image.png

  6. 《時間への追加2》をクリックします。
    image.png

  7. 「時間への追加2」アクション内の入出力データが表示されます。
    image.png

    • 別のデータが見たい場合は、上部にある《前へ》や《次へ》をクリックします。
    • 出力データの確認
      • body：加工後の結果

  8. これでExcel日付（シリアル値）を日付に変更できました。

比較方法

  1. 基準となる時間を算出します。
    • 《組み込み》→《日時》に時間算出に利用できるアクションがあるので基準となる日時を作成します。
  2. 《コントロール》→《条件》アクションを利用して比較を行います。
    image.png

参考サイト

Excelの日付データは1900年1月1日から数えた連番ではない 1900年うるう年問題
Do untilを使い、ExcelやSharePointリストのデータ取得数制限を回避して全件取得する
Power Automate 「表内に存在する行を一覧表示(Excel Online (Business))」アクション
Azure Logic Apps および Power Automate の式で関数を使用するためのリファレンス ガイド
Azure Logic Apps のコネクタ
(Azure Logic Apps を使用して Excel Online データを管理する)[https://docs.microsoft.com/ja-jp/azure/connectors/connectors-create-api-excel]

9
11
2

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
9
11