質問: エクセルの表で、勤怠の計算をしたいのですが、18:00~29:00まで勤務し拘束時間が9時間です。9時間以降が残業になります。深夜残業は、22:00~29:00ですので、深夜残業2時間 深夜は7時間あるので、5時間は深夜
回答: 【補足より】 時間だけが必要だという事はわかりましたが、何分単位で時間を出したいのかが補足されていなかったのと、総残業時間と残業時間の区別がわからわからなかったので、残業時間というのは「深夜+深夜手当て」と解釈して作りました。 図を添付したかったのですが、編集では図が添付できないので、文字だけでの説明になります。 まずタイトルを1行目に、開始、終了、総残業時間、 残業時間、 深夜時間、 深夜手当とF列まで記入します。 そして2行目以下は、まず書式の設定をして下さい。 A列とB列:文字列 C列~F列:数値(小数点以下は1などお好みで) G列とH列:標準 この2列は作業列です。あまりに式が長くなるので使いました。 入力はA列をB列だけで「19:00」「29:00」のように24時を越えてもそのまま入れるようにして下さい。 C2 =IF((H2-G2)-9*60<0,0,((H2-G2)-9*60)/60) D2 =E2+F2 E2 =IF(H2<22*60,0,IF(H2<=24*60,(H2-24*60)/60,2)) F2 =IF(H2<24*60,0,(H2-24*60)/60) G2 =VALUE(LEFT(A2,2))*60+VALUE(RIGHT(A2,2)) H2 =VALUE(LEFT(B2,2))*60+VALUE(RIGHT(B2,2)) C列~H列は下の行へ式のコピーをして下さい。 G列とH列は見た目邪魔ですので非表示にするといいです。 本来は開始時刻が入っていなかったら空白というようなIF文を入れるのですが、今回は詳細をつかみきれていないので、入れてありません。 多分、意図されているのと違うかもしれませんので、その場合はこの質問を削除せず、再質問なさって下さい。 よろしくお願いします。 質問文の内容だけでは、関数まで回答できないです。 ・この表に時給は必要ないのでしょうか?(正社員で固定給から残業単価が決まる?) ・深夜時間は時間だけで、金額は必要ないのでしょうか? ・深夜手当ては金額だと思いますが、割り増しの条件(計算式)が書いてありません。 ・何分単位で計算するのでしょうか? 少なくとも以上の情報が必要です。 また、エクセルでは23:59までしか時刻としては入力出来ません。29:00と入れても「1900/1/1 5:00:00」を意味してしまいます。試しに入力して数式バーを見るとわかります。このあたりの事も工夫が必要になります。 補足で、以上の質問に答えて下されば、回答を考えますのでよろしくお願いします。