Excelにて勤務表の作成で早出・残業、所定労働時間の計算式を教えて下さい

所定始業時間は8：30
所定終業時間は17：30
昼休憩は12：00～13：00までの1：00
の所定労働時間は８時間です

項目としては、始業時間（A)・終業時間（B)・所定労働時間（C）・早出・残業（Ｄ）
とした時に
Ｑ1、早出をして7：30～18：30まで残業した際には、所定労働時間（C）には８時間
　　　早出・残業（Ｄ）には２時間と出るようにしたい
　　　※早出・残業は３０分計算です
Ｑ2、8：30に出勤し12：00で退勤した場合は昼休憩を引かずに所定労働時間（Ｃ）に３時間30分
　　　と出るようにしたい

以上、よろしくお願い致します

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

• ①出社・退社とも３０分単位での計算です
  ②昼休憩は13時から発生です
  よろしくお願い致します

  No.1の回答に寄せられた補足コメントです。 補足日時：2016/08/10 08:33

• 

  午前＋午後の計算でやってみましたが
  通常8：30～17：30の場合問題ないのですが
  8：30～12：00までの場合２時間３０分とでてしまいます
  やはり午前と午後を項目分けしてやらないとダメですかね？

  No.2の回答に寄せられた補足コメントです。 補足日時：2016/08/10 09:20

No.3 回答者： 銀鱗 回答日時： 2016/08/09 19:46

30分単位でどうのとありましたね。

忘れていました。

　=FLOOR(数値,"0:30"）
これで30分単位に切り捨てされます。
切り上げしたいのでしたら
　=FLOOR(数値+"0:29","0:30"）
でOK。
数値のところへ残業時間の値や計算式を入れてください。

No.2 回答者： 銀鱗 回答日時： 2016/08/09 19:02

MAX関数とMIN関数の組み合わせで引き算をすればよい。

所定始業時間："8:30"
ならば、早出時間は
　="8:30"-（出社時間）
で、早出しているときは求められるが、早出していないときは0にしたい。
なので、"8:30"と出社時間の小さい方を"8:30"から引けばよいことになる。
すると
　="8:30"-MIN("8:30",出社時間)
のようにできる。

他の計算もこのようにして計算すればよい。

午前中の計算なら
　=MIN("12:00",退社時間)-MAX("8:30",出社時間)
こんな感じ、
午後なら、
　＝MIN("17:30,退社時間)-MAX("13:00",出社時間）
残業は、
　＝MAX("17:30,退社時間)-"17:30"

あとはこの分けて計算した結果を所定労働時間や早出・残業時間に反映させるだけ。

残りはちょっとだけ頭を使って考えてみよう。

No.1 回答者： GooUserラック 回答日時： 2016/08/09 18:22

質問です。

① 出社時間と退社時間は３０分単位に元々なっているのですか？
② 昼休憩は退社時間が何時以降発生しますか？