勤務時間をエクセルで自動計算できません。。

勤務時間を計算するためにエクセルで表を完成させるように会社で依頼されています。

表自体はあらかた出来上がっていて、
項目は、日付
就業時間
終業時間
就労時間
18時間以降の残業
祝日残業 です。

依頼の中身なのですが、基本勤務時間は830～1730
で、休憩時間は800～830・1200～1300・1730～1800
で設定してほしいといわれています。

そして、休憩は就労時間によって自動入力にしてほしいといわれています。

昼一時間の休憩を差し引くやり方はたくさん乗っていたのですが。。

この場合はどうしたらよいのでしょうか。。
明日には提出する必要があります。

どうかお知恵をくださいm(__)m

宜しくおねがいします。

質問者からの補足コメント

• ママチャリさんありがとうございます。
  書き込み自体初めてで、
  分かりづらく申し訳ございません。
  
  表は上司の方から降りてきたもので、
  私が作成したものではありません。
  出来上がった表をもとに、
  関数をいれて仕上げるようにと言われています。
  
  就業時間・終業時間は手入力でいれてもらいます。
  
  就労時間・18時以降の残業時間・祝日の残業時間に入れる関数が分からず困っていました。

    補足日時：2016/11/06 15:54

No.3 ベストアンサー 回答者： ママチャリ 回答日時： 2016/11/06 21:33

VBAでもよければ、こんな感じです。

後述のVBAを標準モジュールへコピペした後、シートに次の関数を設定してください。

A1セルに就業時刻、B1セルに就業時刻が入力されているものとします。
その上で、C1セル、および、D1セルに次の式を設定します。
【C1セル】=就労時間(A1,B1)
【D1セル=残業時間(A1,B1)
※祝日残業について、意味が分からなかったので回答できません。

【VBA】
Function 就労時間(就業時刻 As Variant, 終業時刻 As Variant) As Variant
If 就業時刻 > 終業時刻 Then 終業時刻 = 終業時刻 + 1
就労時間 = 終業時刻 - 就業時刻
就労時間 = 就労時間 - 重複時間(就業時刻, 終業時刻, TimeValue("8:00"), TimeValue("8:30"))
就労時間 = 就労時間 - 重複時間(就業時刻, 終業時刻, TimeValue("12:00"), TimeValue("13:00"))
就労時間 = 就労時間 - 重複時間(就業時刻, 終業時刻, TimeValue("17:30"), TimeValue("18:00"))
End Function

Function 残業時間(就業時刻 As Variant, 終業時刻 As Variant) As Variant
If 就業時刻 > 終業時刻 Then 終業時刻 = 終業時刻 + 1
残業時間 = 重複時間(就業時刻, 終業時刻, TimeValue("18:00"), 2)
End Function

Function 重複時間(AF As Variant, AT As Variant, BF As Variant, BT As Variant) As Variant
Dim F As Variant
Dim T As Variant
重複時間 = 0
If BT < AF Or AT < BF Then Exit Function
F = AF
T = AT
If AF < BF Then F = BF
If AT > BT Then T = BT
重複時間 = T - F
End Function

この回答へのお礼

的確なお言葉とお答えありがとうございました！

お礼日時：2016/11/10 00:02

No.4 回答者： yucco_chan 回答日時： 2016/11/06 23:09

質問内容の計算だけでいいのですか？

・8:30より遅く出社した。（遅刻）
・17:30より前に退社した。（早退）
・8:00前に出社した。（早出残業）
・22:00以降の深夜残業は区別しなくていいのか？
・徹夜時には、1時間の休息時間があるはずだが、
　その時間帯は？
　　深夜、徹夜は無いとの前提か？

これらを考慮しなくても良いなら、
・退社時間－18:00が残業時間で、
　　－（マイナス）になる時は、ゼロ（残業なし）にする。
　if 関数などで制御できます。
・祝日残業 は、休日出勤の事だと思われますが、
　貴社の休日は、誰にも分かりません（汎用のデータでは無い）ので、
　別途、休日のデーターベースを作っていおいて、
　該当日（LOOKUP関数などで抽出）は、全部残業
　（平日と同じ休息時間は差し引く）とすれば良いです。

No.2 回答者： ママチャリ 回答日時： 2016/11/06 15:36

「表自体はあらかた出来上がっている」とのことですが、では、何ができていないのですか？

もしかして、レイアウトは作ったが、就労時間などを求める数式が作れないということですか？

もしそうであれば、もう少し具体的な質問にしないと回答も付きにくいですよ。
例えば、こんな感じです。

ーーー質問例ーーー
次の値を求める数式を教えて下さい。
【就労時間】
就業時刻から終業時刻までの時間数を求める。ただし、800～830・1200～1300・1730～1800は休憩時間とし、時間数には含めない。

【残業時間】
就労時間のうち、18:00以降の時間数？？？（8:00以前も残業になりそうですが･･･）

【祝日残業】
祝日の就労時間？それとも祝日の残業時間？
（祝日を判断できる項目はシート上に表示済み？）

No.1 回答者： angkor_h 回答日時： 2016/11/06 14:25

時間に関する関数のお勉強をしていては間に合わないと思います。

先ずは出来合いのものを参照されたほうが良いと思います。
http://zestyoga.net/kintai/