Excelでの勤務表作成について

質問

エクセルにて勤務表を作成したいと思っておりますが、過去ログの
どれに当てはまるかがわからず、質問させて頂く次第です。
現在勤務表は以下のとおりとなっております。

□基本パターン
始業時間　　終業時間　　実働時間　　時間外（所定時間超え）
C　 D　　　　 E　 F　　　　　G　　H　　　　　I　　J　
８：４５　 　　１７：１５ 　 　　７：３０　　　　０：００

上記基本パターンの他に残業（時間外）を１５分単位でプラスする場合があります。
その場合、以下のようになります。

□残業ありの場合（例：１８：３０まで働いた場合）
始業時間　　終業時間　　実働時間　　時間外（所定時間超え）
　C　 D　　 　　E　 F　　 　　　G　H　　 　 I　　J　
８：４５　　　 １８：３０　　　　 ８：４５　　　１：１５

＊休憩は1時間です

現在、エクセルにて実働時間を計算する式を以下のように製作して
おります。
□時間式
IF(C4="","",IF((E4-C4)>=0,ROUNDDOWN(((E4-C4)*60-60+F4-D4)/60,0),ROUNDDOWN(((E4-C4+24)*60+F4-D4)/60,0)))

□分式
IF(C5="","",IF((F5-D5)>=0,F5-D5,F5-D5+60))

そこで、基本労働時間７：３０を超えた時間外を計算する式
（時間式・分式）はどのように作成したら宜しいでしょうか。
お手数をおかけ致しますが、宜しくお願い致します。

回答No.2

zap35 · ベストアンサー

実働時間は計算されているものとして（G4：時、H4：分）、

I4は
=IF(F4+G4/60>=7.75,INT(F4+G4/60-7.5),0)
J4は
=IF(F4+G4/60>=7.75,FLOOR(MOD(F4+G4/60-7.5,1)*60,15),0)
でどうでしょうか。FLOOR関数を使用しているのは15分単位に切り捨てするためです。詳しくはヘルプを参照してください

でもなぜ時、分を別々のセルに分けたのでしょう？

開始時刻（C4) 8:45、終了時刻（E4) 18:45　なら
実働時間(G4)の式は　=D12-B12-1/24
残業時間(I4)の式は　=IF(F12>7.5/24,FLOOR(F12-7.5/24,1/96),0)
とずっと簡単になるように思います。
（EXCEL内部では１時間は1/24で表されます。ですから7.5/24は7.5時間を、1/96は15分を意味します）

お礼

早速のご教示ありがとうございます。
私のエクセルの式は難しいものなのですか？
簡単なやり方を教えて頂き、本当にありがとうございます。
とても勉強になりました。
教えて頂いた式を参考にさせて頂きます。

回答No.4

vista2446

「時」と「分」が別のセルなんですね・・・

時間外（時）
IF(ISBLANK(C4),"",QUOTIENT((G4*60+H4)-450,60))
時間外（分）
IF(ISBLANK(C4),"",MOD((G4*60+H4)-450,60))
う～っ。。。ほかにも計算式がありそうな。。。

お礼

早速のご教示本当にありがとうございます。
時間と分が別なのは、実は元のエクセル表は友人から頂いた物でして、
友人も人から譲り受けたものだそうで、その点がよくわからないのです。
私も初心者ながら、頑張ってみたのですが、できませんでした。
教えて頂いたように試してみます。
ありがとうございました。

回答No.3

noname#46899

Ｉ、Ｊの書式設定を数値にしたうえで、
I2（仮に２行目とした場合）は、
=HOUR(TIME(G2,H2,)-TIME(E2,F2,))
J2（仮に２行目とした場合）は、
=MINUTE(TIME(G2,H2,)-TIME(E2,F2,))
となるかと思います。

お礼

早速のご教示ありがとうございました。
教えて頂いた式を参考にさせて頂きます。
本当にありがとうございました。

回答No.1

redowl

時、分を個々のセルに分けているのは、何か理由があるのでしょうか？

単純に、
時分を入力する場合、一つのセルに　　「８」「：（コロン）」「４」「５」　でよいのでは？

で、仮に下記のようなシート割り付けで、
　　　　A列　B列　　Ｃ列　　Ｄ列　Ｅ列　Ｆ列
【行１】　　　　　　始業　　終業　実働　時間外
【行２】　　　　　　8:45　17:15　7:30　0:00
【行３】　　　　　　8:45　18:30　8:45　1:15

始業時間　と　終業時間　は入力欄
実働時間　と　時間外時間　には、それぞれ下記の数式を入れる。
　　　実働時間の数式は、E2　　=D2-C2-TIME(1,0,0)
　　　時間外時間の数式、F2　　=E2-TIME(7,30,0)

なお、時間表示になるよう、セル書式表示を　　　　　時刻＞　　種類　　　○○：○○　で設定してください。

お礼

早速のご教示ありがとうございます。
時間と分が別な理由は不明ですが、とても参考になりました。
教えて頂いた式を参考にこれからもっと勉強が必要という事が
わかりました。本当にありがとうございました。