Excelで勤務時間の計算をしたいです。

質問

すでに作られている勤務表を前任者から受け継ぎましたが、
すべて手入力しているので関数を使ってもっと効率化を図りたいと思います。
皆様の知恵をお貸し下さい。

出勤時間→B列
退社時間→C列
出勤日数のカウント→D列
残業時間→E列

条件として
・定時は8：00～17：00に対して1時間の休憩をとる。
例：B列　　　C列　　D列　　E列
　　8：00　17：00　　1　　[空白]

・実働時間9時間を超える場合は残業時間に入れる
例：B列　　　C列　　D列　　E列
　　8：00　18：30　　1 　　1.5

・実働時間9時間以上の場合は1時間休憩を差し引き実働時間8時間としてそれに対して
出勤日数のカウント「1」
実働時間4時間以下の場合は休憩時間を差し引かずに
出勤日数のカウント「0.5」
実働時間4時間超え、9時間未満のものに対しては休憩時間に関係なく
出勤日数のカウント「1」

（ここが一番厄介な点かと思われます。4時間以内ならカウント0.5、4時間を越えたものはカウント1です。）
例：B列　　　C列　　D列　　E列
　　8：00　12：00　 0.5　 ［空白］
　　8：00　15：00　　1　　［空白］
　　8：00　17：30　　1　　　0.5

・深夜勤務もあるが、その場合の残業は手動で計算するので、出勤日数のカウントだけでOK
例：B列　　　C列　　D列　　E列
　23：00　　5：00　　1　　[空白]

このような状態なのですが、計算の列などを増やして（あまり増やしたくはないのですが・・・）
関数をいれてある程度自動で計算をさせたいのです。
休憩時間はほぼ1時間と決まっているのであまり列は増やさずに計算させたいです。

簡単な関数は知っていますが、表示形式など詳しいことは分かりません（残業時間1時間半を1.5と表示するなど・・・）
ちなみに出勤日数のカウント、残業に関しては1や1.5などの数値を
他の時給計算で使っています。

巧く説明できなくてすみません（(汗)）
どのような関数を組んだらよいか、ご指導を宜しくお願いします_(．＿．)_

回答No.2

hallo-2007 · ベストアンサー

休憩時間が4時間稼動後1時間ときまっているとかでしょうか？
D列を
=IF((B2-A2)<="4:00"*1,0.5,1)
とか
=IF((B2-A2)<="5:00"*1,0.5,1)
で試してみてください。
E列は
=IF((B2-A2)<"9:00"*1,"",B2-A1-"9:00")
表示書式は　時刻　或いは　ユーザー定義で　h:m　にしてみてください。

補足

ありがとうございました。
概ねこの関数でいけそうです。

ただ、残業時間は表示形式を直して、例えば2.0とかになっていても、
実際の値？は数値ではなく時刻ですよね？（シリアル値？）
ここの残業時間も他の時給計算で使っているので、この表示の2.0を
数値の2.0に直すことは可能でしょうか？
もしご存知でしたら宜しくお願い致します。m(＿＿)m

回答No.6

hallo-2007

No2です。
エクセルで取り扱う日付のシリアル値と数値の関係ですが
数値の　１　を日付で1日（時間で24時間）となっています。
ちなみに空いているセルに　0.5　と数値を入れて表示形式を時刻にしてみてください。
0.5＝＞　12:00　になります。
シリアル値で 2:00　と表示されているのであれば
=IF((B2-A2)<"9:00"*1,"",(B2-A1-"9:00")*24)
として表示形式を　標準　或いは　数値にしてみてください。

お礼

ありがとうございました。
ほぼ思っていたことが出来るようになりました(*^-^*)
日付（シリアル値）は不得意なのでなかなかなじめません・・・

回答No.5

tom04

No.4です。
前回の回答は無視してください。

残業がちゃんと表示されないみたいです。

どうもごめんなさい。m(__)m

回答No.4

tom04

こんばんは！
参考になるかどうか分かりませんが・・・

質問内容を読んでみると、実働時間が4時間を超えた場合（休憩時間は除く）は「1」と表示され、
4時間以下は「0.5」と表示されれば良いと解釈しています。
そして残業時間に関しては30分単位での計算（例　29分の残業は0になる）
ということで残業の方の数式を入れています。
そして仮に退社時刻が24：00を超えた場合は　26：30　のような表示にしていただければ
ちゃんと希望に近い形に表示できると思います。
（とはいっても・・・深夜勤務の形態は考えなくて良いという事なので必要ないかもしれませんね）

↓の画像のように表を作ってみました。

尚、出社時間が8：00以前であっても8：00出社扱いで計算式を入れています。

D2セル　=IF(B2<"8:00",IF((C2-"8:00")*24>"4:00"*24,1,0.5),IF((C2-B2)*24<="5:00"*24,0.5,""))

E2セル　=IF(C2*24>"17:00"*24,(FLOOR((C2-"17:00"),"0:30"))*24,"")

としてD2・E2セルを範囲指定してオートフィルでコピーしています。

以上、参考になれば幸いですが、
的外れの回答なら無視してくださいね。m(__)m

お礼

画像付きで説明していただきありがとうございました。

回答No.3

有田 賢治（@ARIKEN43）

niaxxxさん　今晩は！
プログラムを作成することは大変なことです。一々不明な点を聞いていたのでは何回も質問する事になりますよ。
良い方法は他人の作成したプログラムを真似して覚える事です。
■Excel(エクセル)実用編：目次↓
http://www.eurus.dti.ne.jp/~yoneyama/Excel/jituyou/jitu_menu.htm
内容：17のプログラム作成例が図解で作成手順が載っています。
関連：■Excel(エクセル)実用編：時給計算書の作成例
http://www.eurus.dti.ne.jp/~yoneyama/Excel/jituyou/jikyu.htm
をヒントにして下さい。
時間に関する関数も調べられます。

お礼

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

回答No.1

kickknock

長いので簡潔に。

まず、式を一通り作ることです。表示形式は無視。

そして、表示形式を変えたい場所から別の列にリンク貼り付け。
その列の表示形式を変えます。
変える元になる列は非表示にしましょう。式は成立し、表示形式のみ違う列で表示されます。

それと、個人？　管理者？　立場がわかりません。
管理者なら、別シートで深夜労働用のシートを作れば?

あと、休憩時間などほぼ固定で決まっているなら、先に1を入力しておきます。

そして、「調整時間枠」に休憩時間の変更の数字をいれて、「休憩時間短縮、延長」など備考欄も設けます。

各種設定は検索すれば出てきますのでここでは、考え方工夫の仕方のみ記述しました。

お礼

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