タイムカードの集計

質問

エクセルでタイムカードの集計を考えております。
色々調べたり、自分でやってみたりしましたが
どうしてもうまくいかない為、こちらで相談させてもらいました。

勤務時間は以下の通りです。
8：00～17：00　普通出勤時間
10：00～10：10　休憩時間
12：00～12：40　休憩時間
15：00～15：10　休憩時間
ここまでの休憩時間は合計1時間になります。

17：00～17：10　休憩時間
19：10～19：20　21時まで残業の場合の休憩時間
19：10～19：30　22時まで残業の場合の休憩時間（夜食を取る為）

残業時間の勤務形態は以下の通りです。
17：00～17：40　30分残業
17：00～18：10　1時間残業
17：00～18：40　1時間30分残業
17：00～19：10　2時間残業
17：00～20：00　2時間40分残業
17：00～20：30　3時間10分残業
17：00～21：00　3時間40分残業
17：00～21：30　4時間10分残業
17：00～22：00　4時間30分残業
17：00～22：30　5時間残業
17：00～23：00　5時間30分残業

以上のようにとても複雑で、計算するにも大変です。
22時以降の残業は基本的にありませんが、22時以降は
30分刻みで計算しています。

こんな勤務時間の計算でも、エクセルで出来るのでしょうか？
また、フリーでなくてもかまいませんので、
上記のような計算が出来るソフトがあればご紹介頂きたく存じます。

回答No.5

MarcoRossiItaly

＞残業時間の四捨五入の所がうまくいかないのですが・・・。
　例えば、8：00～19：13までの……

う～ん…。念のため私の手元のExcelで再確認しましたが、正しく算出していますよ。A1に「8:00」を、B1に「19:13」を、C1～E1にNo.3の各式をコピペしたら、C1～E1にそれぞれ「8:00」、「2:00」、「####」と表示されます。

これはExcelのバージョンの違いによって結果が変わるような計算ではありません。ですから、何らかの入力ミスと思われます。できたら、新規のワークシートに上のとおり入力し、今一度、確かめてみてください。式や数字は、このウェブページからコピペが確実ですよ。

8:00～19:13の場合、質問文によると、残業時間はちょうど2:00になりますね。17:10～19:10の2時間のはずです。ですから四捨五入するまでもなく、2:00がD1に算出されているかと。仮に、ちょうどでない時間が算出されるようなケースでは、No.3での説明どおり、ROUND関数での処理をD1に対して行えば、四捨五入できるはずですね。

ちなみにExcelでは、マイナスの時間は上のように「####」みたいに表示される仕様になっています。これが嫌だという場合は、時間をゼロ以上に変換すればいいだけです。

=max(,e1)

という処理をすると、マイナスの数値をゼロにし、ゼロ以上はそのまま表示してくれます。つまり、「####」というのは表示されず、「0:00」となります。

No.3のC1～E1の各式には問題ないと思うのですが、説明文には、まだ変なところがありました。次のとおり訂正します。何度もすみません。

×「そのほうが式が簡単になります。なおこの際、A～D列の…」→○「そのほうが式が簡単になります。なおこの際、A～E列の…」
×「…「B1-A1」の代わりに、「MIN(B1,"17:00")-A1」や「B1-MAX(A1,"17:00")」、…」→○「…「B1-A1」の代わりに、「MIN(B1,"17:00")-A1」や「MIN(B1,"22:00")-MAX(A1,"17:00")」、…」
△「切り捨ての場合、ご質問のケースでは…」→○「INTにせよROUNDにせよ、結果として切り捨てになる場合、ご質問のケースでは…」

回答No.4

MarcoRossiItaly

No.3です。記述に一部、誤りがあったので、訂正します。何度もすみません。

×「E1:勤務時間（23時～）」→○「E1:勤務時間（22時～）」

補足

お返事が遅くなりまして申し訳ございません。
色々と教えてくださいましてありがとうございます。

残業時間の四捨五入の所がうまくいかないのですが・・・。
例えば、8：00～19：13までの勤務の場合
8時間普通出勤で2時間の残業が付かなければならないのですが
8時間の普通出勤に1：43の残業⇒四捨五入で1：30と表示されてしまいます。

回答No.3

MarcoRossiItaly

※質問文の「4時間30分残業」以降の数字は、これで正しいのですか？そうだとすると、なぜなのか説明がありませんが…。

A1:出勤時間、B1:退勤時間、C1:勤務時間（定時）、D1:勤務時間（17～22時）、E1:勤務時間（23時～）

勤務時間中に日付が変わる場合は、例えば午前1時を25時などと入力してください。そのほうが式が簡単になります。なおこの際、A～D列の「セルの書式設定」をユーザー定義の（h:mmではなく）「[h]:mm」に設定すると、入力ミスをしにくいかと思います。

★
休憩時間などの「対象外の時間」を求めたいときは、「MAX(,MIN-MAX)」という式で計算します。公式として使えます。A1・B1セルに入力する時間は、何時でも問題ありません。例えば17:05といった中途半端な時刻に退勤しても、休憩時間は17:00～17:05と算出されます。

したがって、休憩なしとした場合の勤務時間「B1-A1」から、いくつかの休憩時間を差し引くことで、ご質問の勤務時間が計算されます。

C1　=(min(b1,"17:00")-a1)-max(,min(b1,"10:10")-max(a1,"10:00"))-max(,min(b1,"12:40")-max(a1,"12:00"))-max(,min(b1,"15:10")-max(a1,"15:00"))
D1　=(min(b1,"22:00")-max(a1,"17:00"))-max(,min(b1,"17:10")-max(a1,"17:00"))-max(,min(b1,"19:20")-max(a1,"19:10"))-max(,min(b1,"19:30")-max(a1,"19:20"))*(b1>"21:00")
E1　=b1-max(a1,"22:00")

※上の説明に出てくる「B1-A1」の代わりに、「MIN(B1,"17:00")-A1」や「B1-MAX(A1,"17:00")」、「B1-MAX(A1,"22:00")」とC1・D1セルに記入しています。この処理は、3つの時間帯に区分してそれぞれ算出するためのものです。
※D1の式中、「*(b1>"21:00")」とあるのは、B1が21時を過ぎる場合のみ、19:20～19:30の10分間を差し引くという意味です。そうでない場合は、ゼロになります。

なお、30分刻みで計算するには、割り算とINT関数を次のように組み合わせるか、INTについてはFLOOR関数というものを使ってもできます。

=int(e1/"0:30")*"0:30"　　（切り捨て）
=round(e1/"0:30",)*"0:30"　　（四捨五入）

！
ただし、マクドナルドによる勤務時間の切り捨て事件と同じで、切り捨て処理はそもそも違法だと判断されることが一般的です。切り捨ての場合、ご質問のケースでは本当に問題ないのか、改めて、よくよく確認された上でご利用くださるようお願いします。

補足

回答ありがとうございます。
うちの会社は数名の役員と2名の管理職からなりたっておりまして
給与は月給になっておりますので、法律の面では大丈夫かと思われます。
今回は、かつて従業員が何人かいたころの計算と比べて
労働時間がどうなのかを知りたく、同じ条件で計算しようと思いました。

また、4時間30分残業以降は
休憩時間がないため？30分刻みとなります。
ただし、22時を過ぎての残業をした事がないので
本来は必要ないのかもしれません。

回答No.2

KURUMITO

回答Ｎｏ１です。
退勤時間を30分単位で切り捨て処理するにはＦＬＯＯＲ関数を使います。
Ｆ2セルへの入力の式は次のようにすればよいでしょう。

=IF(AND(A2<"17:00"*1,B2>"17:00"*1),MAX(FLOOR(B2,"0:30"),"17:00")-MAX(A2,"17:00")-E2,0)

普通勤務時間についても出勤時間を30分単位で切り上げ退勤時間を30分単位で切り捨てとするのでしたら
Ｄ2セルへの入力の式は次のようにします。

=IF(A2<"17:00"*1,MIN(FLOOR(B2,"0:30"),"17:00")-MAX(CEILING(A2,"0:30"),"8:00")-C2,0)

回答No.1

KURUMITO

Ａ１セルに出勤時間、Ｂ1セルに退勤時間、Ｃ1セルに普通時休憩時間、Ｄ1セルに普通勤務時間、Ｅ1セルに残業時休憩時間、Ｆ1セルに残業時間の文字をそれぞれ入力し、例えばＡ2セルに8：00と入力しＢ2セルに23：00のように入力するとします。
Ｃ2セルには次の式を入力します。

=IF(AND(A2<"10:00"*1,B2>"10:10"*1),MIN(B2,"10:10")-MAX(A2,"10:00"),0)+IF(AND(A2<"12:00"*1,B2>"12:40"*1),MIN(B2,"12:40")-MAX(A2,"12:00"),0)+IF(AND(A2<"15:00"*1,B2>"15:10"*1),MIN(B2,"15:10")-MAX(A2,"15:00"),0)

Ｄ2セルには次の式を入力します。

=IF(A2<"17:00"*1,MIN(B2,"17:00")-MAX(A2,"8:00")-C2,0)

Ｅ2セルには次の式を入力します。

=IF(AND(A2<"17:00"*1,B2>"17:10"*1),MIN(B2,"17:10")-MAX(A2,"17:00"),0)+IF(AND(A2<"17:00"*1,B2>"19:20"*1,B2<"22:00"*1),MIN(B2,"19:20")-MAX(A2,"19:10"),IF(AND(A2<"17:00"*1,B2>="22:00"*1),MIN(B2,"19:30")-MAX(A2,"19:10"),0))

Ｆ2セルには次の式を入力します。

=IF(AND(A2<"17:00"*1,B2>"17:00"*1),MAX(B2,"17:00")-MAX(A2,"17:00")-E2,0)

Ａ2セルからＦ2セルの表示形式は時刻の中から選択します。

補足

早速の回答ありがとうございます。
とても早くてびっくりしました。
厚かましいのですが、残業時間の計算で、切り捨て処理をしたいのですが
H列に計算式を入れる事は出来ますか？
例えば、7：40出勤　21：20退勤　普通勤務時間8時間　普通残業時間3：40
上記のように表示したいのですができますでしょうか？