エクセルの勤務計算で一部の時間を省く計算式は？

質問

エクセルで勤務管理表を作りました。
1日何時間働いたか、タイムカードの時間を打ち込み
月間で管理する表です。

だいたいは出来たのですが、1点難問が・・・。
基本は9:00-17:00勤務なので
（昼45分差し引き）計7:15分です。
残業が17:16～しかつかないんです。
（17:00～17:15は残業とみなさない）
つまり・・・17:15までですと勤務計は7:15分。
　　　　　　17:16までですと勤務計は7:31分です。
これを考慮した計算ができる式を教えていただけないでしょうか？
　　　 A1 　 　　　 B1 　　　　 C1 　　　 　 D1
（退社時間－出社時間）－昼時間＝1日の勤務時間

上記のことがあるので
「D1」セルに正しい勤務計を出すことが出来ないんです。
　17:01～17:15は省く・・・みたいな。計算式が必要なんでしょうか？

回答No.4

goomania · ベストアンサー

＃1さんの方法だと17：00に退社した人の勤務時間が７時間になりますが、質問者の意図は違うように思います。
以下の方法はどうでしょう。
Ｅ1に17：00という定時退社時刻を入れておく。
Ｆ1に残業発生時刻である17：15を入れておく。
Ｇ1に=IF(AND(A1>E1,A1<=F1),E1,A1)という式を入れて17：00以降17：15までの退社であれば17：00退社と強制的にみなすための工夫をする。
　　　 G1 　 　　　 B1 　　　　 C1 　　　 　 D1
（みなし退社時間－出社時間）－昼時間＝1日の勤務時間
となるわけです。

回答No.12

macchan1

#11さん、ご指摘ありがとうございます。

=IF(A1>VALUE("17:15"),元の式,元の式-VALUE("0:15"))
の式では確かに元の式-VALUE("0:15")の部分がおかしいですね。
詳細な条件が提示されていないのでモデルのような式を提示してみましたが、早とちりでした。
ここは、もっとも簡単なパターンなら定時のVALUE("7:15")を入れるべきですね。

いずれにしろ実用的に使用できるようにするには、早退や遅刻などにも対応する必要があると思います。最終的にはもう少し複雑な式になると思いますので、すべての条件（パターン）を提示していただけると、具体的な式を回答できるのではないかと思います。

お礼

あまり高度なことが分からなかったもので、遅刻・早退を考えず簡単な表を考えておりました。
なので実はIF関数というのも？？？だった為、皆様から頂いた回答を参考にさせて頂き
=IF(H5<$L$1,"0:00",G5-D5)　　とし、単純にタイムカードの退社時間を入力しただけで7.15にプラスできる7:16以降の残業時間を出すことができました!セル位置をいろいろ変えてしまったので質問時と違うのですが・・・

H5　実質残業時間、　　L1　0:16　　　G5　退社時間　　D5　出社時間　　　　　 です。

皆様のおかげです。感謝します。ありがとうこざいました。

回答No.11

goomania

どうも混乱が生じているようです。
chiro23さんの質問によれば

＞つまり・・・17:15までですと勤務計は7:15分。
＞　　　　　　17:16までですと勤務計は7:31分です

との記述がありますので
＃１０さんのおっしゃっておられる
=IF(A1>VALUE("17:15"),元の式,元の式-VALUE("0:15"))
では定時退社である１７：００とか１７：１０に退社した人は通常勤務時間７時間１５分を下回ってしまいませんか？
確かにchiro23さんが提示した条件だけでは様々なケースが想定されてしまいますので、とりあえず「定時出社した場合の退社時刻での切り分け」としてchiro23さんに補足を求めたいと思いますが・・・

お礼

あまり高度なことが分からなかったもので、遅刻・早退を考えず簡単な表を考えておりました。
なので実はIF関数というのも？？？だった為、皆様から頂いた回答を参考にさせて頂き
=IF(H5<$L$1,"0:00",G5-D5)　　とし、単純にタイムカードの退社時間を入力しただけで7.15にプラスできる7:16以降の残業時間を出すことができました!セル位置をいろいろ変えてしまったので質問時と違うのですが・・・

H5　実質残業時間、　　L1　0:16　　　G5　退社時間　　D5　出社時間　　　　　 です。

皆様のおかげです。感謝します。ありがとうこざいました。

回答No.10

macchan1

A1 　 　　　 B1 　　　　 C1 　　　 　 D1
（退社時間－出社時間）－昼時間＝1日の勤務時間

ご質問の計算式は上記のような場合、出社時間がどのようなケースがあるのか（例えば定時の9：00以外に10：00出社があるのか?）、その場合の取扱い（A1-B1の数字が基準になるのか）、などによって数式を分岐させる必要があります。
退社時間だけで条件分岐するなら関数自体は非常に簡単になりますが・・・・

=IF(A1>VALUE("17:15"),元の式,元の式-VALUE("0:15"))

この当たりを具体的にすべての条件を提示されれば、的確な回答が得られると思います。

回答No.9

maggoteating

No.8さんのご心配は、ごもっともです。パートさんのように時間給の場合は、午前中の早退等も細かい勤務時間の割り出しが必要と思われます。

　正社員の場合は、一般に休暇は半日単位が多いので、１７時以降の残業時間だけ管理することになるのでしょうか。

　どちらなのでしょう？

お礼

あまり高度なことが分からなかったもので、遅刻・早退を考えず簡単な表を考えておりました。
なので実はIF関数というのも？？？だった為、皆様から頂いた回答を参考にさせて頂き
=IF(H5<$L$1,"0:00",G5-D5)　　とし、単純にタイムカードの退社時間を入力しただけで7.15にプラスできる7:16以降の残業時間を出すことができました!セル位置をいろいろ変えてしまったので質問時と違うのですが・・・

H5　実質残業時間、　　L1　0:16　　　G5　退社時間　　D5　出社時間　　　　　 です。

皆様のおかげです。感謝します。ありがとうこざいました。

回答No.8

quit123

＞だいたいは出来たのですが、1点難問が・・・。

難問は質問の一点だけですか？

（早退）
午前中に早退、昼休み時間に早退等々

（遅刻）
昼休みの出勤、昼休みが終わってからの出勤等々

このようなことは考えなくていいのですか？

ちゃんと考え、処理してましたらご容赦願います。

お礼

あまり高度なことが分からなかったもので、遅刻・早退を考えず簡単な表を考えておりました。
なので実はIF関数というのも？？？だった為、皆様から頂いた回答を参考にさせて頂き
=IF(H5<$L$1,"0:00",G5-D5)　　とし、単純にタイムカードの退社時間を入力しただけで7.15にプラスできる7:16以降の残業時間を出すことができました!セル位置をいろいろ変えてしまったので質問時と違うのですが・・・

H5　実質残業時間、　　L1　0:16　　　G5　退社時間　　D5　出社時間　　　　　 です。

皆様のおかげです。感謝します。ありがとうこざいました。

回答No.7

maggoteating

No.6です。すみません！

　A1<17:15の場合（FALSE）は、D1=A1-B1-1は

　A1>17:15の場合（FALSE）は、D1=A1-B1-1の間違い！

回答No.6

maggoteating

ＩＦ関数をつかって

　A1<=17:00 の場合（TRUE）は、D1=A1-B1-0:45

17:00<A1<=17:15の場合（TRUE）は、D1=17:00-B1-0:45

　A1<17:15の場合（FALSE）は、D1=A1-B1-1

回答No.5

inte-nori

#3です。補足です。

書式設定が時刻以外になっていると、変な小数が出てくるので
その場合は書式の変更をしてください。

お礼

あまり高度なことが分からなかったもので、遅刻・早退を考えず簡単な表を考えておりました。
なので実はIF関数というのも？？？だった為、皆様から頂いた回答を参考にさせて頂き
=IF(H5<$L$1,"0:00",G5-D5)　　とし、単純にタイムカードの退社時間を入力しただけで7.15にプラスできる7:16以降の残業時間を出すことができました!セル位置をいろいろ変えてしまったので質問時と違うのですが・・・

H5　実質残業時間、　　L1　0:16　　　G5　退社時間　　D5　出社時間　　　　　 です。

皆様のおかげです。感謝します。ありがとうこざいました。

回答No.3

inte-nori

D1セルに

=IF((A1>=TIMEVALUE("17:00"))*(A1<=TIMEVALUE("17:15")),TIMEVALUE("17:00")-B1-C1,A1-B1-C1)

と入れてみてはどうでしょうか？

回答No.2

quit123

No1のUltramanvaioさんへ

＞15分付かないのでしたら、昼休み60分で計算すれば簡単なのでは？

　ん？　ほんとにそれで出来ますか？(^^;;;

IF文がいるのではありませんか？