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

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

### Excel日付のシリアル値
- Excelでセルに日付の書式を設定すると表示は日付として見えるが、内部的にはシリアル値で記録されている。  
（日付の書式が設定されているセルの書式を標準にするとシリアル値が表示されます）  
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/520689/cf766399-5646-4efd-3b83-bf8e83fb3773.png)
    - 画像の赤枠部分がシリアル値
        - 日付：少数点より前の部分
            - Excelでは1900年1月1日が1となる（設定により1904年1月1日が1になる場合もある）
            - Power Automateでは1900年1月1日を基準にするとずれるため、1899年12月30日を基準日にしている。   
              詳しくは、[Excelの日付データは1900年1月1日から数えた連番ではない 1900年うるう年問題](https://qiita.com/wagase/items/6444ce93a24aaaa74981#%E3%81%A1%E3%81%AA%E3%81%BF%E3%81%ABmicrosoft-visual-basic%E3%81%A0%E3%81%A8)を参照してください。  
  多分Power Automateもこの仕様になっていると思われます。
        - 時間：小数点より後ろの部分
            - 時間は1/86400(24時間*60分*60秒)で1秒のシリアル値が計算できます。
            - 2分の場合
                - 1/86400*120
            - シリアル値から時間に戻す場合
                - シリアル値 * 86400
    - Power Automateで取り扱うため小数点で分割処理をしています。

## 手順

### Excelデータの準備
1. 日付のデータを入力したExcelを準備しました。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/520689/79e53cca-b224-943a-ffbe-c67b5d51365b.png)

1. セルに日付の書式を設定します。（画像のように入力すると自動的に書式設定されています）
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/520689/0e503474-f8bc-b345-e5f4-ee1e1652aca5.png)
    - 「表示形式」→ 日付
    - 「種類」→ 2012/3/14 13:30

1. 「テーブルとして書式設定」をクリックして、お好みのデザインをクリックします。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/520689/3c46f504-5b9c-1672-3c45-77656b6fc55e.png)

1. 「テーブルとして書式設定」ダイアログの《先頭行をテーブルの見出しとして使用する》にチェックを入れて《OK》をクリックします。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/520689/6aad9288-3159-d493-f840-ef92497576f8.png)

1. テーブル化できたので、OneDrive for Businessに保存します。（SharePointでも大丈夫です）
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/520689/6fe5b5e6-c8ad-32e4-f587-b904c32511f8.png)

### Power Automateの設定

#### Excelからシリアル値を取得

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

1. 左側のメニューより《作成》→ 《予定フローを作成》をクリックします。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/520689/f5f71321-4f45-139c-53c8-74fd4ab05a11.png)
    - 今回は「予定フローを作成」を選択していますが、適宜あったメニューを選んでください。

1. 必要項目を設定して《作成》をクリックします。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/520689/e851f3fd-10f6-ba2e-3ca1-407a56e02f19.png)
    - フロー名：適当な名前を入力します。
    - その他必要な設定を行います。

1. 作成画面が開きますので、《新しいステップ》をクリックします。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/520689/e4e9ef15-5dfa-71fb-10fe-e9d0444cc712.png)

1. 「アクションを選択してください」の検索欄に「excel」と入力し、表示された《Excel Online(Business)》をクリックします。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/520689/c90b8959-b318-77f6-7764-117d8cac494c.png)

1. 「Excel Online(Business)」アクションの中から、《表内に存在する行を一覧表示》をクリックします。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/520689/51404013-c0b2-597a-0584-4cc30b37aa6b.png)
    - このアクションは、指定したExcelファイルからデータを取得します。
    - 1回実行すると256行のデータを取得します。  
      256行以上データが存在する場合は、このアクションを複数回実行する必要があります。  
      方法は、[Do untilを使い、ExcelやSharePointリストのデータ取得数制限を回避して全件取得する](https://qiita.com/Yellow11/items/97e3a142c7510c8378eb)を参考にしてください。

1. Excelデータの準備でOneDrive for Businessに作成したExcelを指定する。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/520689/f48fdbb7-02b8-52f5-0413-6ce3704d5172.png)
    - 場所：OneDrive for Business
    - ドキュメントライブラリー：OneDrive
    - ファイル：保存してあるフォルダーを選択する
    - テーブル：適当なテーブル名（何も設定していない場合は、テーブル１になる）

1. 《新しいステップ》をクリックします。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/520689/10fee872-aaf0-00cb-9d12-cf2cb96aa83a.png)

1. 《組み込み》→《コントロール》をクリックします。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/520689/e2b5832d-3318-ed2c-f438-c73652b9ca58.png)

1. 《Apply to each》をクリックします。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/520689/9d3192e1-4c61-197e-30dd-85b20460dbb8.png)
    - このアクションは、受け取ったデータが全てなくなるまで繰り返し実行します。

1. 「Apply to each」の「以前の手順から出力を選択」ボックスをクリックします。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/520689/968046b3-4309-80f1-0bab-6521bdf8b6e4.png)

1. 「動的なコンテンツ」ダイアログから「表内に存在する行を一覧表示」の《value》をクリックします。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/520689/69c26f2c-1ba8-bdf5-ef2f-7e68569ba081.png)
    - Valueには「表内に存在する行を一覧表示」のアクションで取得したデータが入っています。

#### シリアル値を日付に変換

1. 《アクションの追加》をクリックします。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/520689/bf595706-84ed-159f-4600-ce9548719c94.png)

1. 《組み込み》→《データ操作》をクリックします。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/520689/b18f81e3-3d84-b3d1-0da2-19272ba05e3b.png)

1. 《作成》をクリックします。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/520689/2fe5c258-d6e2-4b75-5122-1eeee491052f.png)

1. 「作成」の「入力」ボックスをクリックします。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/520689/748c87f3-cd28-28f2-fec7-3df2c62efd6e.png)

1. 「動的なコンテンツ」ダイアログから「表内に存在する行を一覧表示」の《日時》をクリックします。（日時はExcelに作成したテーブルの見出し名です）
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/520689/764c7b1b-883c-815f-0f01-8f188143aff0.png)

1. 《アクションの追加》をクリックします。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/520689/7f823e0d-ad1b-de29-eaca-91786e72d31f.png)

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

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

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

1. 「動的なコンテンツ」ダイアログの《式》をクリックします。
        ![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/520689/bf991427-2d94-2b10-87e4-29ccb95bb4a4.png)

1. 「文字列関数」の《もっと見る》をクリックしします。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/520689/f117679e-6dd9-9c59-a4fd-50e1a571248e.png)
 
1. 下にスクロールして、《split(text, separator)》をクリックします。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/520689/aa62887d-5959-9533-ecc6-4716907af542.png)

1. 《動的なコンテンツ》→「作成」の《出力》をクリックします。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/520689/11a34011-3315-c15f-c9b3-8640cc93093e.png)

1. 《更新》をクリックします。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/520689/b68b1f97-aa7a-a385-48d5-d99445dc0b23.png)

1. 《アクションの追加》をクリックします。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/520689/fb3fab39-0a01-22b6-91a2-6dc9abc4f9f5.png)

1. 《組み込み》→《日時》をクリックします。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/520689/ce1548ae-1560-db98-b60c-da9a2fde22dc.png)

1. 《時間への追加》をクリックします。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/520689/1c42c32f-5de4-baec-f85e-eb227675f05d.png)

1. 「時間への追加」アクションに以下の値を設定します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/520689/4d7afc20-2b65-e14c-e617-305411d2c1b7.png)
    - 設定値
        - 基準時間：1899/12/30
        - 間隔：int(outputs('作成'))
        - 時間単位：日
    - 「間隔」の設定方法
        - 「間隔」のボックスをクリックする。
        - 「動的なコンテンツ」ダイアログの《式》をクリックします。  
        ![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/520689/d2d3b3b2-c190-9596-162b-589630a4a276.png)
        - 下にスクロールして、「変換関数」の《int(value)》をクリックします。  
        ![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/520689/a6e9f46b-df84-e27a-bacb-4c0157a649dc.png)
       - 《動的なコンテンツ》→「作成2」の《出力》をクリックします。  
       ![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/520689/a7ae3322-aa83-a8f2-bf1e-f50f7b50a371.png)
       - 《OK》をクリックします。  
       ![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/520689/800ca482-307d-d8b5-60b0-2db31bc80af3.png)

#### シリアル値を時間に変換

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

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

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

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

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

1. 「動的なコンテンツ」ダイアログの《式》をクリックし、以下の式を入力します。
    - 式：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型に変換

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

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

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

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

1. 「時間への追加」アクションに以下の値を設定します。
    - 設定値
        - 基準時間：「時間への追加」の《算出時間》をクリックします。  
        ![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/520689/7f1f4015-a2ce-a463-488c-1d83d04af279.png)
        - 間隔：「作成3」の《出力》をクリックします。  
        ![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/520689/2edef50d-a4c5-e6a9-7e56-e3ce72e51302.png)
        - 時間単位：秒

#### 実行結果の確認

1. これでフローが作成できたので正しくできているかテストするので、《テスト》をクリックします。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/520689/93eb8540-5da1-3d8e-84e4-27ae2636041e.png)

1. 「フローのテスト」画面が表示されるので、《トリガーアクションを実行する》を選択し、《テスト》をクリックします。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/520689/0630ce2b-2851-375f-a604-cbaeb788b088.png)

1. 《フローの実行》をクリックします。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/520689/15b9646d-b928-2f6c-a257-805b93ff8323.png)

1. 《完了》をクリックします。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/520689/8a896ee4-fa0f-503c-a28c-f7ffe9cabcf9.png)

1. 実行結果画面が表示されるので、《Apply to each》をクリックします。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/520689/2345e994-9c92-695f-cae4-f94e6d5187d2.png)

1. 《時間への追加2》をクリックします。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/520689/99abe1f4-0855-2533-980e-9f27f9eaa691.png)

1. 「時間への追加2」アクション内の入出力データが表示されます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/520689/0714a76f-5a16-ab70-ef1f-3a457eee15ce.png)
    - 別のデータが見たい場合は、上部にある《前へ》や《次へ》をクリックします。
    - 出力データの確認
        - body：加工後の結果

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

#### 比較方法

1. 基準となる時間を算出します。
    - 《組み込み》→《日時》に時間算出に利用できるアクションがあるので基準となる日時を作成します。
2. 《コントロール》→《条件》アクションを利用して比較を行います。  
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/520689/124dd13e-681d-5eee-f925-9c92cf6d2327.png)

## 参考サイト
[Excelの日付データは1900年1月1日から数えた連番ではない 1900年うるう年問題](https://qiita.com/wagase/items/6444ce93a24aaaa74981#%E3%81%A1%E3%81%AA%E3%81%BF%E3%81%ABmicrosoft-visual-basic%E3%81%A0%E3%81%A8)
[Do untilを使い、ExcelやSharePointリストのデータ取得数制限を回避して全件取得する](https://qiita.com/Yellow11/items/97e3a142c7510c8378eb)
[Power Automate 「表内に存在する行を一覧表示(Excel Online (Business))」アクション](https://blog-tips.sekenkodqx.jp/2020/07/21/power-automate-action-list-rows-present-in-a-table-excel-online-business/)
[Azure Logic Apps および Power Automate の式で関数を使用するためのリファレンス ガイド](https://docs.microsoft.com/ja-jp/azure/logic-apps/workflow-definition-language-functions-reference)
[Azure Logic Apps のコネクタ](https://docs.microsoft.com/ja-jp/azure/connectors/apis-list)
(Azure Logic Apps を使用して Excel Online データを管理する)[https://docs.microsoft.com/ja-jp/azure/connectors/connectors-create-api-excel]