【エクセルの関数について】

質問

エクセルの関数についてです
エクセルで出勤簿を作っているのですが、関数の数式がわかりません。

結論から申しますと、退勤時間を24:00と書いても0:00←（24時のこと）と書いても同じ処理をしてほしいということです。

詳細を以下に記載していきます

C9出勤：手入力
D9退勤：手入力　　←問題はココ

休憩セルには=AN9

AN9には「=SUM($AD9:$AM9)」

AD9には「=IF(OR(($C9=$D9),(設定!$A$4=設定!$C$4),COUNT($C9,$D9,設定!$A$4,設定!$C$4)<4),0,
IF(AND(設定!$A$4<=$C9,$C9<=設定!$C$4,設定!$A$4<=$D9,$D9<=設定!$C$4),($D9-$C9),
IF(AND(設定!$A$4>$C9,設定!$A$4<$D9,$D9<=設定!$C$4),($D9-設定!$A$4),
IF(AND(設定!$A$4<=$C9,$C9<設定!$C$4,設定!$C$4<$D9),(設定!$C$4-$C9),
IF(AND(設定!$A$4>$C9,設定!$C$4<$D9),(設定!$C$4-設定!$A$4),0)))))」

AM9には「=IF(OR(($C9=$D9),(設定!$A$14=設定!$C$14),COUNT($C9,$D9,設定!$A$14,設定!$C$14)<4),0,
IF(AND(設定!$A$14<=$C9,$C9<=設定!$C$14,設定!$A$14<=$D9,$D9<=設定!$C$14),($D9-$C9),
IF(AND(設定!$A$14>$C9,設定!$A$14<$D9,$D9<=設定!$C$14),($D9-設定!$A$14),
IF(AND(設定!$A$14<=$C9,$C9<設定!$C$14,設定!$C$14<$D9),(設定!$C$14-$C9),
IF(AND(設定!$A$14>$C9,設定!$C$14<$D9),(設定!$C$14-設定!$A$14),0)))))」

AD9～AN9までは同じようなことが書いてあります。
設定!$A$4には「12:00」
設定!$C$4には「13:00」

以上までが、休憩セルです

H9拘束時間には、=AS9

AS9には、「=AC9」

AC9には、「=IF(COUNT($C9:$D9)<2,0,IF($AA9*24>$AB9*24,0,TEXT($AB9-$AA9,"[h]:mm")))」

AA9には、「=IF(COUNT($C9:$D9)<2,0,TEXT(CEILING($C9,"0:10"),"[h]:mm"))」

AB9には、「=IF(COUNT($C9:$D9)<2,0,TEXT(FLOOR($D9,"0:10"),"[h]:mm"))」

以上が拘束時間

I9普通残業セルには、=AT9

AT9には、「=IF(D9-設定!$C$2<0,"0:00",D9-設定!$C$2)」

設定!$C$2には、「18:00」

以上が普通残業時間

J9深夜残業時間セルには、「=AU9」

AU9には、「=AQ9」

AQ9には、「=SUM($AO9:$AO9)」

AO9には、「=IF( COUNT($C9:$D9)<2, 0,
IF( AND(22<=$AA9*24,$AA9*24<29,22<$AB9*24,$AB9*24<=29), ($AB9-$AA9),
IF( AND($AA9*24<=22,22<$AB9*24,$AB9*24<=29), ($AB9-"22:00"),
IF( AND(22<=$AA9*24,$AA9*24<29,29<=$AB9*24), ("29:00"-$AA9),
IF( AND($AA9*24<22,29<$AB9*24), ("29:00"-"22:00"),
0 )))))」

以上が深夜残業時間

ということで、分かる方ご教授願います。

回答No.1

bunjii · ベストアンサー

>結論から申しますと、退勤時間を24:00と書いても0:00←（24時のこと）と書いても同じ処理をしてほしいということです。
出勤の翌日（23:59以降）に退勤がある勤務のときは日付と時刻を使う必要があります。
例えば2013年9月26日の9時00分に出勤して2013年9月27日の0時30分に退勤したとすれば算術式として成り立ちます。
時刻だけを使うと誤りが起こることもありますので出退勤の管理簿は日付を含むデータを使うと良いでしょう。
詳細の説明を解読するのは困難のため拘束時間の計算のみ以下のように回答します。

=D9-C9
表示形式を「時刻」の 13:30 にすれば目的通りに表示されます。

IF関数で =IF(D9<C9,D9-C9+1,D9-C9) で日付を除いた管理も可能ですが24時間以上の勤務（拘束）のとき対応できません。

補足

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

>>出勤の翌日（23:59以降）に退勤がある勤務のときは日付と時刻を使う必要があります。
日付なしで、時刻のみの計算の場合は、やはり難しいですか？？

回答No.2

bunjii

>日付なしで、時刻のみの計算の場合は、やはり難しいですか？？
難しくはないと思います。
但し、条件によっては誤りが起こります。
回答内容をあなたには理解できませんか？
あなたの応用力によって安易を判断してください。
IF関数の例示もしていますので説明は足りていると思います。

お礼

教えていただきありがとうございました