８時間以上だったら１時間マイナスする関数

質問

勤務時間が８時間を越えると、１時間マイナスするという関数を作りたいのですがうまくいきません。
開始　終了　勤務時間
20:00　05:00　8:00

としたいと思っています。

どういう関数を使えば良いのでしょうか。

回答No.1

mshr1962 · ベストアンサー

20:00～29:00(翌日05:00)ということでしょうか？
B2が29:00と入力なら
=B2-A2-((B2-A2)>1/3)/24
解説：
(B2-A2)>1/3の部分が終了-開始と1/3日(24時間/3で8時間)の比較で
8時間を越えると1(TRUE)、8時間以内なら0(FALSE)となります。
時間からすると1は1日(24時間)を表すので24で割ってます。

B2が05:00と入力なら
=B2-A2+(B2<A2)-((B2-A2+(B2<A2))>1/3)/24
解説：
追加した(B2<A2)が時刻が逆転の判定で1日(24時間)を追加しています。

※29:00と表示したい場合は、セルの書式設定の表示形式をユーザー定義にして
[hh]:mm
としてください。24時間以上の時刻が表示できます。

お礼

=B2-A2+(B2<A2)-((B2-A2+(B2<A2))>1/3)/24でできました。

時間計算はどうも苦手で・・・。

本当にありがとうございました。

回答No.4

zap35

開始時刻がA1、終了時刻がB1のとき
=IF(B1-A1<0,IF(B1+1-A1>=8/24,B1+1-A1-1/24,B1+1-A1),IF(B1-A1>=8,B1-A1-1/24,B1-A1))
でどうでしょうか。

補足説明
１）開始時刻＞終了時刻の場合は日またがりと判断して終了時刻に１を加算しています。１はEXCEL内部では１日に相当しますので、質問例では翌日の朝５時を意味するようになります
２）勤務時間が８時間以上の時は勤務時間から１時間（EXCEL内部では1/24）を減算しています

お礼

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

計算式はNo.1さんのでうまくいきましたが、皆さんの計算式も後でゆっくり勉強させていただきます。

色々な計算方法があるのがわかりとても勉強になりました。
ありがとうございました。

回答No.3

naokun1976

=IF(E4>=1/3,E4-1/24,E4)
(E4に勤務時間8:00が入っているものとします。)
Excelでは時間の場合、１＝1日＝24時間ですので、
1/3が8時間
1/24が1時間です。

お礼

計算式はNo.1さんのでうまくいきました。

＞1/3が8時間
＞1/24が1時間です。

そうなんですか。とても勉強になりました。
ありがとうございました。

回答No.2

merlionXX

開始時間をＡ１終了時間をＢ１に入れます。
ただし、例示されたような日付をまたぐ場合は、2006/11/14 20:00や2006/11/15 5:00というように日付も入れないと計算できないと思います。

エクセルでは1時間を一日の1/24、８時間なら8/24⇒1/3というように認識します。
式は
=IF(B1-A1>1/3,B1-A1-1/24,B1-A1)

式を入れたセルの書式は「時刻」にしてください。

お礼

時間の所には時間しか入ってなく、それが３００行くらいあったので、今回はNo.1の方のでやりました。

今後の参考にさせていただきます。
ありがとうございました。