IF関数 複数条件 で困っています。

質問

お疲れ様です。
今　勤務表を作っています。
休憩時間合算が自動表示されるようにしたいのですが・・・。
まず、社内ルールで、22時15分過ぎには10分休憩が入ります。
22時20分に帰った人は、休憩が5分、22時30分に帰った人は休憩が15分
といったように、22時15分から22時30分に帰った人はその差分が休憩に出るようにしたいのですが、
どういった数式を入れたら良いでしょうか？

エクセルに長けた方、ぜひぜひ教えて下さい！

回答No.4

nattocurry

休憩時間が何分間か、を求めたいということで良いんですよね？

退社時刻によって、3つの場合に分けます。

・退社時刻が22時15分以前　⇒　0分
・退社時刻が22時15分から22時25分の間　⇒　退社時刻－22時15分
・退社時刻が22時25分以後　⇒　10分

ただ、IF関数では、2つの場合にしか分けられないので、3つの場合に分けるために、IF関数の中で、さらにIF関数を使います。

・退社時刻が22時15分以前　⇒　0分
・退社時刻が22時15分以後
　　└・退社時刻が22時25分以前　⇒　退社時刻－22時15分
　　└・退社時刻が22時25分以後　⇒　10分

これを数式っぽく書くと、

=IF(退社時刻が22時15分以前,0分,IF(退社時刻が22時25分以前,退社時刻－22時15分,10分))

となります。
でも、これだと、書式が間違っているので、エラーになります。

退社時刻をA1に記入するとして、

=IF(A1<VALUE("22:15"),0,IF(A1<VALUE("22:25"),A1-VALUE("22:15"),VALUE("0:10")))

とすれば良いです。
セルの書式で、表示形式を[mm]にしてください。

回答No.3

heinell

No1です。
A1に退勤時刻が入ってると仮定すれば
=IF(A1>TIMEVALUE("22:15:00"),MINUTE(MIN(A1-TIMEVALUE("22:15:00"),TIMEVALUE("0:10:00"))),0)
ってトコでしょうか

Excelで日付・時刻がどういう扱いを受けているか？
実際はシリアル値という使い方をされてるんですが
=TIMEVALUE("22:15:00")
とやると
0.927083333
と表示されます。
確かに22：15ですから24時間の93%程度というのは解ると思います。
これが日付シリアル値の考え方です。

そして数値の形になっていれば時・分・秒の区別がないし全て10進数で表現できるので計算は楽。
で、計算が終わったら表示を「分」の形式にします。(MINUTE関数）

MINUTE()は分の部分を「切り出す」ので、1時間1分だったら1分と出してしまいます。
なんで、MIN関数で退勤時刻から22:15を引き算した数字(23:16分だったら本来61分)と、休憩の最大値の10分を比べて、小さい方を取ってから分の部分を切り出してます。
（「最大値」が10分なのに「MIN関数」を使うというのが混乱しやすいところですが）

TIMEVALUEで秒まで書いてるのは、22:15だと22時間15分なのか、2分15秒なのか区別がつかないため。（実際は22時間15分で計算されます）
略して書く癖がつくといつか秒が必要なときにハマりますので、略さずに書く癖をつけると良いでしょう。

と言うことで、Excelの日付・時刻計算は、人間表記の時分秒とExcel計算用のシリアル値という二つの概念を理解する段階で非常に混乱しやすいので、安易に聞いて解決しようとすると全く理解できません。
ガンと腰を据えて勉強した方が良いでしょう。

回答No.2

Cupper

勤務表ではなく退勤表ですよね
ぶっちゃけ、一つの計算式で全部やろうなんて考えちゃダメ

定時の時間、残業の時間、深夜残業の時間 などに分けて計算するようにしましょう
（実際、そうしないと計算式が長く（項目が多く）なりすぎてエラーになることがあります）

■本題
まず、仕様が正しく伝わってきません
＞22時15分過ぎには10分休憩が入ります
＞22時30分に帰った人は休憩が15分
この5分の差はどう考えたらよろしいのでしょうか
（5分余計に休憩したという扱いで、勤務時間にカウントしないなど）
さすがにこの扱いが分からないとどうにも手が出ません

…たぶん一つ解決したと思うと次の条件が出てくる気がしてなりません
そんなですので、近くにいる Excelに詳しい人を捕獲して一緒に作らせましょう

お礼

すみません。さっそくの返答ありがとうございますー！！
初めての質問なので感激。
あせって書いたばかりに間違いがありました。
休憩時間ですが、22時15分から一律！10分休憩です。
22時20分に帰ったとしたら、休憩は「５分」という感じです・・・。

回答No.1

heinell

・22:21分に帰った人の休憩時間はどうなりますか？（暗黙の5分刻みなどが存在するかどうか？）
・24:00を超えた場合はどうしてますか？
・22時15分過ぎには「10分休憩が入ります」なのに22時30分に帰った人は休憩が「15分」この矛盾はどうしますか？
・22:35に帰った人は休憩は何分ですか？

余談
Excelで勤務表を作るのはかなり難儀な事。
勤務時間計算って誰がこんなルールを考え付いたのか小一時間説教したい例外処理の嵐な上に、時間計算で単純な数字じゃない分計算も厄介です。
正直、相当の覚悟が必要かと…

お礼

あせって書いたばかりに間違いがありました。
休憩時間ですが、22時15分から一律！10分休憩です。
22時20分に帰ったとしたら、休憩は「５分」という感じです・・・。

24時を超えた場合はもう考えなくて良いということでした。

本当大変です・・

誰か助けてほしい