エクセルでタイムカード計算

質問

初心者です。エクセルで次のような計算をしたいのですが
基本の 始業時間19：00　 終業時間1：10　で　実働　5：30　です

21：00～21：20　まで20分休憩を引きます
23：10～23：30　まで20分休憩を引きます

19：00より前は早出になります
1：20から残業ですが2Hに10分ずつ休憩が入ります
残業は30分単位です

それで第2の設定として
A 19：00～21：59　普通時間（早出含む）
B 22：00～1：10　　普通残業
C 1：20～　　　　　　深夜残業　　

作成したい表は　始業時間と 終業時間をいれると
A（定時だと2：40）
B（定時だと2：50）
C　　　　　　　　　　　　
　　と3段階で表示されるものです

何とかできないでしょうか？
フリーソフトも色々みたのですが？

困っています
宜しくお願いいたします

回答No.2

KURUMITO · ベストアンサー

かなり面倒な式になりますね。
それにしても残業は30分単位でとなっていますがそれでは始業時間19：00　 終業時間1：10　で　実働　5：30　ですとはなりませんね。実働の計算を30分単位にするというのでしたら正しいでしょう。以下に示す式でも実働時間で30分単位で切り下げることにしています。
Ａ1セルに始業時間、Ｂ1セルに終業時間、Ｃ1セルに普通時間、Ｄ1セルに普通残業、Ｅ1セルに深夜残業、Ｆ1セルに実働時間の項目名が入力されておりそれぞれのデータが2行目以降に入力または表示されるとします。
例えばＡ2セルには19:00と入力し、Ｂ2セルには1:10と入力します。
Ｃ2セルには次の式を入力して下方にドラッグコピーします。

=IF(COUNT(A2:B2)<>2,"",MIN(B2+IF(A2>B2,"24:00",0),"22:00")-MAX(A2,"19:00")-IF(AND(A2<"21:00"*1,B2+IF(A2>B2,"24:00",0)>="21:20"*1),"0:20",0))

Ｄ2セルには次の式を入力して下方にドラッグコピーします。

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

Ｅ2セルには次の式を入力して下方にドラッグコピーします。

=IF(MAX(A2,"25:20")>B2+IF(A2>B2,"24:00",0),"",B2+IF(A2>B2,"24:00",0)-MAX(A2,"25:20")-INT((B2+IF(A2>B2,"24:00",0)-MAX(A2,"25:20"))/"2:00")*"0:10")

Ｆ2セルには次の式を入力して下方にドラッグコピーします。

=IF(COUNT(A2:B2)<>2,"",FLOOR(SUM(C2:E2),"0:30"))

お礼

面倒なんです！本当にありがとうございます。

なにせ初心者なもので
時間がかかります

試してみます。
まずはお礼申し上げます。

補足

試してみました。
できました！！ありがとう感激です

深夜残業が
例えば4：07までで2：30なのですが
2：37　と出て
30分単位にならないのですが
どうすれば良いのでしょうか？

回答No.5

MarcoRossiItaly

何度もすみません。No.4の最後の一文で、変換ミスをしてしまったので、念のため。

×「休憩時間を除いた終業時間」
○「休憩時間を除いた就業時間」

お礼

本当にありがとうございます

回答No.4

MarcoRossiItaly

時刻を入力するセルには全て、（「h:mm」ではなく）「[h]:mm」の書式を設定しておくと（＊）、ミスが発生しにくいかと思います。

＊セルを右クリックなどして表示される「セルの書式設定」ダイアログの「表示形式」タブで「ユーザー定義」の「種類」ボックスに書式記号を入力

ご質問の意味が少しずつ見えてきました。No.1で載せた数式を、とりあえず下のものに差し替えてみてください。場合分けが多いのでちょいと面倒というか、1本の式で済まそうとすると少々長くなります。

C1
=choose(1+sum(b1<="22:00"+0,b1<="25:20"+0),"C","B","A")

D1
=b1-a1-sum(max(,min(b1,"21:20")-max(a1,"21:00")),max(,min(b1,"23:30")-max(a1,"23:10")),max(,min(b1,"25:20")-max(a1,"25:10")),max(,min(b1,"27:30")-max(a1,"27:20")),max(,min(b1,"29:40")-max(a1,"29:30")))

C1の式ですが、Aシフトは「普通時間」、B・Cシフトは「残業」との記述から、始業ではなく終業の時刻を基準にA、B、Cを判定する式に変更しました。

ちょっと難しいのですが、D1の式の計算内容。「b1-a1」の部分は、始業から終業までの時間です。「sum」以下の部分は、5回の休憩時間（21:00～21:20、23:10～23:30、25:10～25:20、27:20～27:30、29:30～29:40）の合計を計算しています。ただし始業から終業までの間に含まれない時間帯の休憩時間は、ゼロとして合計するようになっています。以上により、始業と終業が何時であっても、休憩時間を除いた終業時間を正しく計算するかと思います。

お礼

ありがとうございます
今 他の締め切りを抱えてまして…
すみません。試してないのですが
出来る気がします。
何度も恐縮です。

又　 宜しくお願い致します

回答No.3

MarcoRossiItaly

No.1の数式の計算内容を解説すると、次のとおり。

C1　始業の時刻が19:00以前であればAを、19:00過ぎから22:00以前であればBを、22:00過ぎであればCを表示
D1　「Aシフトかつ終業の時刻が22:00以後」または「Bシフトかつ終業の時刻が25:10以後」または「Cシフト」のときは「通常」を、それ以外のときは「早退」を表示

＞……と出ました。嬉しい！！

関数名で、Googleとかで検索しておかれることをお勧めします。自力で式を書けたらもっと嬉しいですよね？なおここでのSUM関数は、論理値というもの（0か1に相当する特殊な値）を合計する働きをしています。

＞D1の深夜残業が上手くいかないのですが
　"通常","早退　の所に入れるのは、どのような数字でしょうか？

何が入力されたときにどう表示してほしいのか、実際の数字などで「具体的に」説明してください。質問者さんの要望がよく分かりません。分かれば、修正版を提示します。

お礼

たびたびありがとうございます

数式の計算内容を解説よくわかりました。

D1の深夜残業の件ですが

1：20から深夜残業になります（30分単位です）

勤務は2時間ごとに10分休憩がはいり、時間に加えません、
（3：20、5：30、に10分）

例えば　　　　　深残時間
1：20～2：15　→　0：30
1：20～3：25　→　2：00
1：20～4：10　→　2：30
　　　　　　　　　　　　　と計算したいのですが
この説明でわかりますでしょうか？

回答No.1

MarcoRossiItaly

A1に始業の、B1に終業の時刻が入力されるとします。次の式を入力。

C1　=choose(1+sum(0+"19:00"<a1,0+"22:00"<a1),"A","B","C")
D1　=if(sum((0+"22:00"<=b1)*(c1="A"),(0+"25:10"<=b1)*(c1="B"),c1="C"),"通常","早退")

＞フリーソフトも色々みたのですが？

Excelより便利なフリーのソフトを見付けるのもなかなか難しいかと。易しい計算なので、ご質問の計算が実行できさえすればいいのであれば、Excelのみで問題なし。

お礼

早速のご回答ありがとうございました
試してみました。
C1で　普通時間2：40　普通残業2：50　と出ました。嬉しい！！

D1の深夜残業が上手くいかないのですが
"通常","早退　の所に入れるのは、どのような数字でしょうか？

初心者なので、すみません。