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

2022/06/21

経緯

以前、Power Automate で土日祝祭日や定時を判定する という記事を書いたのですが、「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. フォーマットを作成

    • A2 には計測したい日時を入力
    • B2 に A2 の曜日が出力される(確認用)
    • E2:E34 に祝祭日データを登録
  2. A 列と C 列のセルの書式設定に、ユーザー定義の yyyy/m/d h:mm を設定

    ※ A 列の例
  3. B2 に下記計算を設定

    =IF($A2="", "",TEXT($A2,"aaaa"))
    A2 が空じゃないとき、曜日を表示する
  4. C2 の列に下記計算を設定 
    =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")
    )
)
)

解説

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

境界値チェックを行いました

大丈夫そうな気がします

対応方法２

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

まずは、対応方法１の手順 1 ～ 3 までは同じで、C2 の列に下記計算を設定します

=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")
)

解説

少し捻って、こう考えました

  • 平日の定時前のとき
    • 基準日を前日にして次の稼働日の 9 時にすればよい
  • 平日の定時後のとき
    • 基準日を当日にして次の稼働日の 9 時にすればよい
  • 土日祝祭日のとき
    • 基準日は前日でも当日でもよくて、次の稼働日の 9 時にすればよい

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

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

なんとなく数式バーと右にある下矢印をクリックすると、入力欄が広がりました

広がった！

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

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

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

=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 時というシリアル値を足してみました

=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(
    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 便利ですね
できるだけ使わないように生きてますけど

ToolExcel

Posted by みやみや