Excel時間計算で対象となる時間数を求める方法は

質問

Officeバージョン：2003
PC環境：WindowsXP

Excelの時間関数を基にした経過時間(対象時間)を算出する方法について質問させて頂きます。

勤怠を管理するExcelを作成しているのですが、勤務体系の中に「深夜勤務」という項目があり、
対象時間が「22:00～翌5:00」までとなっています。

これまでに出勤時間や退勤時間を基にした勤務時間などの差引計算は問題無く解決しているのですが、
計算対象となる出勤時間～退勤時間の中に、今回の対象となる「22:00～翌5:00」がどれだけ含まれているかを計算する過程でつまずいている状況です。

時間計算でシリアル値などは問題無く使用可能です。

これまでに質問で色々探しましたが見つからず...というかどの様なワードで検索したら良いかわからず、
同様の質問の発見に至りませんでした。

良い計算のプロセスなどありましたらご教授頂ければ幸いです。

ps.ソフトウェアはExcel限定でVBAなどのプログラムは現在のところ想定しておりません。

回答No.5

nayuta_lot

こんにちは　No.３です。

すみません。　質問の内容を勘違いしてました。
大変失礼しました。　

【前提】

A2～A20まで出勤時間
B2～B20まで退勤時間

【数式】
=COUNTIFS($A$2:$A$20,">=22:00",$B$2:$B$20,"<=05:00")+COUNTIFS($A$2:$A$20,">=00:00",$A$2:$A$20,"<=05:00",$B$2:$B$20,"<=05:00")

出勤と退勤の間に００：００が入るので、２つに分けて考えます。

１．２２：００から２４：００までの出勤で、かつ翌０５：００までに退勤した人数

=COUNTIFS($A$2:$A$20,">=22:00",$B$2:$B$20,"<=05:00")

２．００：００から０５：００までの出勤で、０５：００までに退勤した人数
　※05：00に出勤して05：00に退勤することはあり得ないと思いますが数式上・・

=COUNTIFS($A$2:$A$20,">=00:00",$A$2:$A$20,"<=05:00",$B$2:$B$20,"<=05:00")

３．１+２

=COUNTIFS($A$2:$A$20,">=22:00",$B$2:$B$20,"<=05:00")+COUNTIFS($A$2:$A$20,">=00:00",$A$2:$A$20,"<=05:00",$B$2:$B$20,"<=05:00")

ちなみに、『２２：００以降の出勤かつ翌０５：００までに退勤』を計算対象とすると、
下記のようなシフトは無視されますが、よいのでしょうか？

２２：００以降に出勤して、退勤時間が深夜勤務に該当しない人（22：00～06：00）
０５：００以降に出勤して、退勤時間が深夜勤務に該当する人（21：00～05：00）

また、見当違いのこと言ってたら無視してくださいね＾＾；
それでは

回答No.4

KURUMITO

B2セルから下方に出勤時間が、C2セルから下方に退勤時間が入力されています。
そこで出勤時間が22：00（以前）から翌5：00(以降）まで勤務した人の数は次の式で求めることができます。
翌日になる退勤時間が5：00のように入力されている場合には次の式で

=SUMPRODUCT((B2:B1000<="22:00"*1)*(C2:C1000>="5:00"*1)*(B2:B1000>C2:C1000))

上の式では退勤時間が出勤時間よりも小さいことが条件に入っています。

翌日になる退勤時間が29：00のように入力されている場合には次の式になります。

=SUMPRODUCT((B2:B1000<="22:00"*1)*(C2:C1000>="29:00"*1))

回答No.3

nayuta_lot

こんにちは

以下のような式でどうでしょうか？
ただし、２４時間以上、勤務することは非現実的なので対応していません。

【前提】
B2に出勤日　C2に出勤時刻
B3に退勤日　C3に退勤時刻

＜基本の式＞
=IF(B3=B2,C3-C2,"24:00"-C2+C3)

出勤日と退勤日が同じであれば、『出勤時刻＜退勤時刻』となるはずです。
従って、C3-C2（退勤時刻　-　出勤時刻）

そうでない場合（出勤日＞退勤日　または　出勤日＜退勤日）

出勤日＞退勤日の場合は、そもそも日付の入力エラーなので退勤日の欄を、入力規則で

　入力の種類　：　日付
　データ　　　：　次の値以上
　次の日付から：　=B2

として、出勤日以降の日付しか入力できないようにしておけばよいでしょう。

出勤日＜退勤日の場合は、

出勤時刻～0：00と0：00から退勤時刻の２つに分けて考えます。

出勤時刻～0：00　　"24:00"-C2

　　24：00から出勤時刻を引きます。

0：00～退勤時刻　　C3

　　退勤時刻がそのまま、0：00からの時間になります。

従って、"24:00"-C2+C3

最終的に最初の式になります。

また、『退勤時刻＞＝出勤時刻』の場合は、同日に退勤し、『退勤時刻＜出勤時刻』の場合
には、翌日に退勤したという前提で考えれば、

=IF(C3>=C2,C3-C2,"24:00"-C2+C3)

と書くこともできます。

その他のエラー処理付きの式の参考です。

＜例１＞
=IF(B3=B2,IF(C3<C2,"時刻エラー",C3-C2),IF(C3<C2,"24:00"-C2+C3,"24時間以上働いています。"))

＜例２＞
=IF(AND(B3=B2,C3>=C2),C3-C2,IF(AND(B3>B2,C3<C2),"24:00"-C2+C3,"入力エラー"))

なにかあれば補足してください。
それでは

回答No.2

keithin

深夜零時過ぎの時刻を「25:15」のように記入しているのか「1:15」と記入しているのかによって，具体的な数式が変わるので注意が必要です。
また例えば「深夜2時出社のお昼上がり」とか，極端な話「朝４時から翌２時(２６時)まで」みたいな勤務も計算したいのか，そういうイレギュラーは全く全然考えなくても良いのかによっても，組み立ては当然変わります。

計算例：
Ａ２に出社時刻（２４時間制で記入）
Ｂ２に退社時刻（零時過ぎは26:15などで記入）

13:00～17:30帯
=IF(COUNT(A2:B2)=2,MAX(MIN("17:30",B2),"13:00")-MIN("17:30",MAX("13:00",A2)),"")
17:30～22:00帯
=IF(COUNT(A2:B2)=2,MAX(MIN("22:00",B2),"17:30")-MIN("22:00",MAX("17:30",A2)),"")
22:00～27:00帯
=IF(COUNT(A2:B2)=2,MAX(MIN("27:00",B2),"22:00")-MIN("27:00",MAX("22:00",A2)),"")

どの時間帯も同じパターンで計算しますので，午前（８時～１２時）帯とか早朝帯とかも練習で作成してみてください。
言わずもがなですが，各セルの書式設定の表示形式はユーザー定義で[h]:mmとしておきます。

お礼

ご回答ありがとうございます！

終了時間は他の計算の都合で24時以降の表示に変換してあります！

なのでご教授頂いたMAXとMINでいけそうな感じです♪

週明けに試してみたいと思います(^^)

回答No.1

mshr1962

開始時間(A1)が5:00～23:59
終了時間(B1)が12:00～翌5:00
時間限定（入力規則を使用）として

深夜時間=MAX(B1+(B1<A1),22/24)-MAX(A1,22/24)

お礼

早速のご回答ありがとうございます！
MAXとMIN、盲点でした。
確かにこれを使用すればいけそうな気がします！

ちなみに終了時間は元は0時に直ってしまっていましたが、他の計算の都合で24時以降に修正してありました。