Excel で時刻が定時外のときは翌営業日と判定する

## 経緯

以前、[Power Automate で土日祝祭日や定時を判定する](https://qiita.com/miyamiya/items/1d83860aa330f3404581) という記事を書いたのですが、「Excel でマクロを使わずに同じことをやりたいけどできる？」と聞かれたので、Excel は嫌いですがやってみました

## 環境

* Windows 10 Pro
* Microsoft® Excel® for Microsoft 365 MSO (バージョン 2205 ビルド 16.0.15225.20172) 64 ビット

## 前提条件

* 祝祭日は日本の祝祭日を適用
* 定時の範囲は平日 9:00 ～ 17:00

## 仕様

* 日時が平日の定時内のときは、その日時をそのまま使う
* 日時が平日の 0:00 ～ 8:59 のときには、当日の 9:00 とする
* 日時が平日の 17:00 ～ 23:59、または土日祝祭日のときには、次の稼働日(平日)の 9:00 とする

## 対応方法

### 対応方法１

こっちは判り易いロジックです

1. フォーマットを作成  
  [![](https://1bed.allright.life/wp-content/uploads/2022/06/2022-06-16_02h47_04-370x530.png)](https://1bed.allright.life/wp-content/uploads/2022/06/2022-06-16_02h47_04.png)  
  - A2 には計測したい日時を入力
  - B2 に A2 の曜日が出力される(確認用)
  - E2:E34 に祝祭日データを登録
2. A 列と C 列のセルの書式設定に、ユーザー定義の `yyyy/m/d h:mm` を設定  
  [![](https://1bed.allright.life/wp-content/uploads/2022/06/2022-06-16_02h51_54-530x442.png)](https://1bed.allright.life/wp-content/uploads/2022/06/2022-06-16_02h51_54.png)  
  ※ A 列の例
3. B2 に下記計算を設定  
  [![](https://1bed.allright.life/wp-content/uploads/2022/06/2022-06-16_02h53_51.png)](https://1bed.allright.life/wp-content/uploads/2022/06/2022-06-16_02h53_51.png)
  `=IF($A2="", "",TEXT($A2,"aaaa"))`  
  A2 が空じゃないとき、曜日を表示する
4. C2 の列に下記計算を設定
```powershell
=IF(
    OR(
        WEEKDAY($A2,1)=1,
        WEEKDAY($A2,1)=7,
        COUNTIF($E$2:$E$34,TEXT($A2,"yyyy/mm/dd"))
    ),
    WORKDAY($A2,1,$E$2:$E$34) + TIMEVALUE("09:00:00"),
    IF(
        AND(
            HOUR($A2)>=9,
            HOUR($A2)<17
        ),
        $A2,
        IF(
            HOUR($A2)<9,
            DATEVALUE(TEXT($A2, "yyyy/mm/dd")) + TIMEVALUE("09:00:00"),
            WORKDAY($A2,1,$E$2:$E$34) + TIMEVALUE("09:00:00")
        )
    )
)
```

解説
[![](https://1bed.allright.life/wp-content/uploads/2022/06/memo-530x393.png)](https://1bed.allright.life/wp-content/uploads/2022/06/memo.png)

Excel には次の稼働日を求める `WORKDAY(開始日, 日数, [祝日])` という便利関数があるんですねー
マクロ使わずにあっという間にできました

境界値チェックを行いました
[![](https://1bed.allright.life/wp-content/uploads/2022/06/2022-06-21_01h29_46-472x530.png)](https://1bed.allright.life/wp-content/uploads/2022/06/2022-06-21_01h29_46.png)

大丈夫そうな気がします


### 対応方法２

こっちはちょっと捻ったロジックです

まずは、対応方法１の手順 1 ～ 3 までは同じで、C2 の列に下記計算を設定します
```powershell
=IF(
    AND(
        WEEKDAY($A2,2)<6,
        NOT(COUNTIF($E$2:$E$34, TEXT($A2,"yyyy/mm/dd"))),
        HOUR($A2)>=9, HOUR($A2)<17
    ),
    $A2,
    WORKDAY($A2+TIMEVALUE("07:00:00")-1,1,$E$2:$E$34) + TIMEVALUE("09:00:00")
)
```

解説
[![](https://1bed.allright.life/wp-content/uploads/2022/06/2022-06-18_04h05_36-530x168.png)](https://1bed.allright.life/wp-content/uploads/2022/06/2022-06-18_04h05_36.png)

少し捻って、こう考えました
* 平日の定時前のとき
  - 基準日を前日にして次の稼働日の 9 時にすればよい
* 平日の定時後のとき
  - 基準日を当日にして次の稼働日の 9 時にすればよい
* 土日祝祭日のとき
  - 基準日は前日でも当日でもよくて、次の稼働日の 9 時にすればよい

## Excel マンじゃないけど、やったときの流れ

Excel の関数って横に連ねていってとても見にくくて、私には 2 個ぐらい入れ子にしたらもう脳内で追っていけませんでした

なんとなく数式バーと右にある下矢印をクリックすると、入力欄が広がりました
[![](https://1bed.allright.life/wp-content/uploads/2022/06/2022-06-21_01h51_50-530x87.png)](https://1bed.allright.life/wp-content/uploads/2022/06/2022-06-21_01h51_50.png)

広がった！
[![](https://1bed.allright.life/wp-content/uploads/2022/06/2022-06-21_01h53_25-530x367.png)](https://1bed.allright.life/wp-content/uploads/2022/06/2022-06-21_01h53_25.png)

あとは、改行の方法探していたのですが、どうも Alt + Enter でできるようです

ここからコーディング(？)の開始です

Excel の if 文の構文は `if(条件文, true のとき, false のとき)` と書くようですので、まずは広げた数式バーに下記のように入力しました

```powershell
=IF(
    OR(
        WEEKDAY($A2,1)=1,
        WEEKDAY($A2,1)=7,
        COUNTIF($E$2:$E$34,TEXT($A2,"yyyy/mm/dd"))
    ),
    "土日祝",
    "平日"
)
```

OR 条件は、OR にしたい数式をカッコ内に半角カンマで区切るようです

これで、 `日曜日 or 土曜日 or 祝日` のとき という条件文になりました

土日祝のときは問答無用で次の稼働日の 9 時にすればいいので、 `"土日祝"` と記載しているところを WORKDAY 関数を入れました

WORKDAY 関数の構文は `WORKDAY(開始日,日数,祝日)` となり、返り値がシリアル値という 1900/1/1 を 1 とした数値が返ってきますが、その時に時間は 00:00:00 なので、9 時というシリアル値を足してみました

```powershell
=IF(
    OR(
        WEEKDAY($A2,1)=1,
        WEEKDAY($A2,1)=7,
        COUNTIF($E$2:$E$34,TEXT($A2,"yyyy/mm/dd"))
    ),
    WORKDAY($A2,1,$E$2:$E$34) + TIMEVALUE("09:00:00"),
    "平日"
)
```

次に平日は定時前、定時内、定時後 で考えないといけないので、`"平日"` を下記のように置き換えました

```powershell
=IF(
    OR(
        WEEKDAY($A2,1)=1,
        WEEKDAY($A2,1)=7,
        COUNTIF($E$2:$E$34,TEXT($A2,"yyyy/mm/dd"))
    ),
    WORKDAY($A2,1,$E$2:$E$34) + TIMEVALUE("09:00:00"),
    IF(
        AND(
            HOUR($A2)>=9,
            HOUR($A2)<17
        ),
        "平日の定時内",
        IF(
            HOUR($A2)<9,
            "平日の定時前",
            "平日の定時後"
        )
    )
)
```

あとは対応方法１の通りとなります

Excel は関数内にコメント打てない（思っている）ので、コメントっぽく徐々に組んでいくと判り易いですね
＃私が慣れてないだけだとはおもいますけど

## 考察

Excel 便利ですね
できるだけ使わないように生きてますけど

変なところあったら優しくご指摘下さい🙇