excel関数で勤務時間の計算をしたいと思っています。

質問

excel関数で勤務時間の計算をしたいと思っています。
添付の画像の通り、実際の勤務実績を入力すると、右側の色つき部に早朝、早出や残業時間などを自動で表示できないかと考えています。

何とかなりませんでしょうか？

回答No.6

有田 賢治（@ARIKEN43）

hanaoyajiさん 今日は！前回の続きです。
■Excel実用編：勤務時間の計算例(関数)「Excel2002」
http://www.eurus.dti.ne.jp/~yoneyama/Excel/nyumon/kinmu/index.html
概要：はじめに　勤務時間計算の条件
一つのセルでいろんな条件を付けて計算しようとすると、長い数式になり修正が非常に大変になります。
このページでは、なるべく平易な分かりやすい数式となるように、各時間帯別に勤務時間を計算する方法で説明しています。
(作業列をちょっと多めに使用しています)
☆勤務時間計算の条件設定
1.通常勤務の時間帯　：　 8:00～17:00（休憩時間　12:00～13:00）
2.残業時間帯　　　　：　17:00～22:00
3.深夜残業時間帯　　：　22:00～30:00 (翌朝 6:00）
4.出勤時間は30分単位で切り上げ、ただし、8:00以前の出勤はすべて8:00出勤とします。
5.退勤時間は30分単位で切り捨てとします。
6.勤務時間の計算は30分単位で切り捨てとします。
計算表のスタイル
5.各勤務時間帯の時刻を6,7行目に入力します：各勤務時間帯の計算式を同一のものにするためです。
目障りならこれらの行を非表示にすればOKです。
6.計算用の出勤時間と退勤時間をF列とG列に表示することにします。
7.数式を簡潔にして、見やすくするためです。目障りならこれらの列も非表示にすればOKです。
8.24時以降は25:00，30:00の入力としています。セルの表示形式を『[h]:mm』とします。
☆計算表のスタイル設定例を参考にして下さい。
☆時間の切り上げと切り捨て　　　　　　
ここでは、まず以下の計算の条件を処理します。
(4) 出勤時間は30分単位で切り上げ、ただし、8:00以前の出勤はすべて8:00出勤とします。
(5) 退勤時間は30分単位で切り捨てとします。
(6) 勤務時間の計算は30分単位で切り捨てとします。
(4-1)出勤時間を30分単位で切り上げるには、CEILING関数を使います。
F8セル：=CEILING(D8,"0:30")　とします。
F8セル：=7:30　と30分単位で切り上げができました。しかし、8:00からの計算とするには(4-2)の処理が必要になります。
(4-2)IF関数またはMAX関数を使って、開始時刻は8:00とする条件を加えます。
8:00以前の出勤はすべて8:00出勤とし、8:00以降の出勤時間は30分単位で切り上げます。
F8セル：=IF(D8<=TIME(8,0,0),TIME(8,0,0),CEILING(D8,"0:30"))
または、最小値を8：00とすれば良いので、
F8セル：=MAX("8:00",CEILING(D8,"0:30"))
とすることもできます。
(4-3)ただし、D8セルが未入力の時も計算結果は8:00となるので、IF関数で未入力時の処理を加えます。
=IF(COUNT(D8)=1,MAX("8:00",CEILING(D8,"0:30")),"")　とします。
(5-1)退勤時間を30分単位で切り捨てるには、FLOOR関数を使います。
G8セル：=FLOOR(E8,"0:30")　とします。
(5-2)ただし、E8セルが未入力の時も計算結果は0:00となるので、IF関数で未入力時の処理を加えます。
=IF(COUNT(E8)=1,FLOOR(E8,"0:30"),"")　とします。
以降は設定が、図解されて居ますので良く読んで作成して下さい。
これで勤務時間が理解できると思います。