PowerQuery（パワークエリ）で日付・時間の計算方法を紹介します。日時データの差の計算方法（減算）と日時データに期間を足し合わせる方法（加算）を解説しています。
テストデータを用いて経過日数や年齢などの計算方法も解説していますので参考にしてみてください。
日時を表すデータ型
日付や時刻を表すデータ型は、よく使われる「日付」や「日付/時刻」を含めて5種類あります。これらの加算、減算方法について後述で解説します。
| データ型 | データ例 | データ例の関数表記 |
|---|---|---|
| 日付 | 2023/04/01 | #date(2023,4,1) |
| 日付/時刻 | 2023/04/01 11:12:13 | #datetime(2023,4,1,11,12,13) |
| 時刻 | 11:12:13 | #time(11,12,13) |
| 日付/時刻/タイムゾーン | 2023/01/02 10:11:40 +02:20 | #datetimezone(2023,1,2,10,11,40,2,20) |
| 期間 | 14.10:11:40 | #duration(14,10,11,40) |
日時データの差を計算する（減算・引き算）
ボタン操作での計算方法
日時データ同士の差を計算するには、列の追加タブ内にある以下のコマンドを使用します。
- 日数の減算： 「日付」の「日数の減算」
- 時間の減算： 「時刻」の「減算」
「時刻」の「減算」を使用した場合は計算結果は期間データとなります。期間データを日数や年数に変換する方法は後述のカスタム列での計算方法で解説しています。
【計算手順】
- 減算で引かれる側の日時データ（遅い方の日時）を選択する。
- Ctrlキーを押しながら減算で引く側の日時データ（早い方の日時）を選択する。
- 列の追加タブ内にある「日付」の「日数の減算」を選択する。または「時刻」の「減算」を選択する。
【使用例】経過日数を算出する
下図のデータから日時データを引き算して経過日数を算出します。
- 引き算の引かれる側の[終了日]を選択する。
- 引き算の引く側の[開始日]を選択する。
この順番でデータを選択することにより [終了日] – [開始日] の計算ができるようになります。
列の追加タブにある「日付」の「日数の減算」を選択する。
以上で経過日数の計算完了です。減算の列に経過日数が算出されています。
カスタム列での計算方法
カスタム列で日時データの差を計算するにはマイナス記号（－）を使った計算式を作成し、結果の期間データを変換してほしい単位に求めます。
【計算手順】
- 列の追加タブの「カスタム列」を選択する。
- 日時データとマイナス記号（－）で計算式を作成して、引き算をする。
- 計算結果の期間データを変換タブの「期間」で変換したい単位に変換する。
【使用例】経過時間を計算する
下図のデータから日時データを引き算して経過時間を算出します。
- 列の追加タブの「カスタム列」を選択する。
- カスタム列の式に下記の計算式を入力してOKをクリックする。※列名は任意で設定。
[終了日]-[開始日]
- 計算結果の期間データを選択する。
- 変換タブの「期間」で「合計時間数」を選択して、期間データを経過時間に変換する。
以上で経過時間の計算完了です。減算の列に経過時間が算出されています。
Sponsored link
日時データに一定期間を足す・引く（加算・足し算）
関数での計算方法
日時データにある一定期間を足し引きして〇〇日後や〇〇日前などの計算をする場合は、下記の関数をカスタム列で利用します。〇〇後ならば日時データにプラスの値を追加し、〇〇前ならばマイナスの値を追加します。
| 加算期間 | 関数 |
|---|---|
| 日 | Date.AddDays ( 日時データ, 追加日数) |
| 週 | Date.AddWeeks ( 日時データ, 追加週数) |
| 月 | Date.AddMonths ( 日時データ, 追加月数) |
| 四半期 | Date.AddQuarters ( 日時データ, 追加四半期数) |
| 年 | Date.AddYears ( 日時データ, 追加年数) |
この計算方法は日～年の単位の計算に適しており、時間を加算したい場合は後述のカスタム列と期間データを用いた方法を参考にしてください。
【計算手順】
- 列の追加タブの「カスタム列」を選択する。
- 期間を追加する関数（Date.Add〇〇）を使った計算式を作成する。
【使用例】3か月後の日時を計算する
下図の開始日データから3か月後の日時データを作成する。
- 列の追加タブの「カスタム列」を選択する。
- カスタム列の式に下記の計算式を入力してOKをクリックする。3か月後のあためDate.AddMonths関数を用いて追加月数に3を設定します。※列名は任意で設定。
Date.AddMonths([開始日],3)
以上で3か月後の日時データの算出は完了です。
カスタム列＋期間データでの計算方法
カスタム列と期間データを用いて計算をすることも可能です。足し引きしたい期間を「期間」データのフォーマットを用いて作成し、それをカスタム列で加算（減算）することで計算します。
この計算方法は日、時間、分、秒の単位の計算に適しており、月の加算したい場合は前述の関数を用いた方法を参考にしてください。
【計算手順】
- 列の追加タブの「カスタム列」を選択する。
- 期間データ(#duration(日数,時間,分,秒))を使用した計算式を作成する。
【使用例】10時間前の日時を計算する
下図の開始日データから10時間前の日時データを作成する。
- 列の追加タブの「カスタム列」を選択する。
- カスタム列の式に下記の計算式を入力してOKをクリックする。10時間前ため #duration(0,10,0,0) を開始日から引き算します。※列名は任意で設定。
[開始日] - #duration(0,10,0,0)
以上で10時間前の日時データの算出は完了です。
Sponsored link
使用例： 経過月数の計算
下図のデータから日時データを引き算して経過時間を算出します。
- 列の追加タブの「カスタム列」を選択する。
- カスタム列の式に下記の計算式を入力してOKをクリックする。Duration.TotalDaysは期間データを日数に変換する関数であり、それにより算出された日数を1ヶ月の平均日数30.416で割ります。
Duration.TotalDays([終了日]-[開始日])/30.416
- 計算結果のデータを選択する。
- 変換タブの「丸め」で「切り捨て」を選択する。
以上で経過月数の計算完了です。
Sponsored link
使用例：年齢の計算
下図の生年月日データから本日時点での年齢を算出します。
- 列の追加タブの「カスタム列」を選択する。
- カスタム列の式に下記の計算式を入力してOKをクリックする。今日の日付を表す関数から生年月日を引いて経過期間を計算する。
- 今日の日付を表す関数：Date.From(DateTime.LocalNow())
Date.From(DateTime.LocalNow())-
- 計算結果の期間データを選択する。
- 変換タブの「期間」で「合計年数」を選択して、期間データを経過年数に変換する。
- 計算結果のデータを選択する。
- 変換タブの「丸め」で「切り捨て」を選択する。
以上で今日時点の年齢の計算完了です。（下図は2023/8/31時点の計算結果です）
Sponsored link
使用例：工程毎の作業時間の計算
下図の作業データから工程A,B,Cそれぞれの合計作業時間（分）を算出します。
- 日時が遅い[作業終了]を選択する。
- 日時が早い[作業開始]をCtrlキーを押しながら選択する。
- 列の追加タブの「時刻」の「減算」を選択して、２つの日時データの差を計算する。
- 計算結果の期間データを選択する。
- 変換タブの「期間」で「合計分数」を選択して、期間データを経過分数に変換する。
- 任意の列を選択する。
- 変換タブの「グループ化」を選択する。
- グループ化する列に[工程]、操作に[合計]、集計される列に[減算]を選択して工程ごとで経過分数を集計する。
グループ化については下記記事で紹介してますので参考にしてみてください。
- 集計データを選択する。
- 変換タブの「丸め」で「切り捨て」を選択する。
以上で工程ごとの作業時間（分）の集計は完了です。
Sponsored link
パーワークエリの基本的な使い方についての初心者向けの解説書です。基本を体系的に学ぶと習得も早くなりますので、気になった方はサンプルを確認してみてください。
コメント