• ベストアンサー

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

エクセルにて勤務表を作成したいと思っておりますが、過去ログの どれに当てはまるかがわからず、質問させて頂く次第です。 現在勤務表は以下のとおりとなっております。 □基本パターン 始業時間  終業時間  実働時間  時間外(所定時間超え) C  D     E  F     G  H     I  J  8:45    17:15     7:30    0:00 上記基本パターンの他に残業(時間外)を15分単位でプラスする場合があります。 その場合、以下のようになります。 □残業ありの場合(例:18:30まで働いた場合) 始業時間  終業時間  実働時間  時間外(所定時間超え)  C  D     E  F      G H     I  J  8:45    18:30     8:45   1:15 *休憩は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)) そこで、基本労働時間7:30を超えた時間外を計算する式 (時間式・分式)はどのように作成したら宜しいでしょうか。 お手数をおかけ致しますが、宜しくお願い致します。

質問者が選んだベストアンサー

  • ベストアンサー
  • zap35
  • ベストアンサー率44% (1383/3079)
回答No.2

実働時間は計算されているものとして(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時間は1/24で表されます。ですから7.5/24は7.5時間を、1/96は15分を意味します)

yuka_1230
質問者

お礼

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

その他の回答 (3)

  • vista2446
  • ベストアンサー率46% (131/279)
回答No.4

「時」と「分」が別のセルなんですね・・・ 時間外(時) IF(ISBLANK(C4),"",QUOTIENT((G4*60+H4)-450,60)) 時間外(分) IF(ISBLANK(C4),"",MOD((G4*60+H4)-450,60)) う~っ。。。ほかにも計算式がありそうな。。。

yuka_1230
質問者

お礼

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

noname#46899
noname#46899
回答No.3

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

yuka_1230
質問者

お礼

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

  • redowl
  • ベストアンサー率43% (2140/4926)
回答No.1

時、分を個々のセルに分けているのは、何か理由があるのでしょうか? 単純に、 時分を入力する場合、一つのセルに  「8」「:(コロン)」「4」「5」 でよいのでは? で、仮に下記のようなシート割り付けで、     A列 B列  C列  D列 E列 F列 【行1】      始業  終業 実働 時間外 【行2】      8:45 17:15 7:30 0:00 【行3】      8:45 18:30 8:45 1:15 始業時間 と 終業時間 は入力欄 実働時間 と 時間外時間 には、それぞれ下記の数式を入れる。    実働時間の数式は、E2  =D2-C2-TIME(1,0,0)    時間外時間の数式、F2  =E2-TIME(7,30,0) なお、時間表示になるよう、セル書式表示を     時刻>  種類   ○○:○○ で設定してください。

yuka_1230
質問者

お礼

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

関連するQ&A

  • エクセルで勤務表作成について

    昨日もご相談させていただき、色んな方からご回答をいただき、やったぁ~と喜んでいたんですが、色んな検証をすすめていくと、修正が必要だと判明したので、また助けていただけますでしょうか? 以下のような項目で表を作成しているんですが、G7:普通残業の部分でずっとひっかかっております。 宜しくお願いいたします。 A7:月日 B7:区分(土日・祝日に1を入力) C7:始業時間 D7:終業時間 E7:休憩時間 F7:所定時間(7:30までしか表示しない)   =IF(B7>0,"",IF(D7-C7-E7>7.5/24,$F$1,IF(C7="","",D7-C7-E7))) G7:普通残業(実働7:30を超え、8時間までの時間表示) ※ ここが上手く出来ません =IF(B8>0,"",IF(D7-C7-E7>7.5/24,IF(D7-C7-E7-7.5/24>=0.5/24,0.5/24,D7-C7-E7-7.5/24),""))という式を入れたんですが、C7:始業時間が9:30、D7:終業時間が18:30(18:30以外の時間は全て表示するようです)、E7:休憩時間が1:00の時、G7:普通残業には0:30とはいらないんです。 =IF(D7-C7-E7>7.5/24,IF(D7-C7-E7-7.5/24>=0.5/24,0.5/24,D7-C7-E7-7.5/24),"")という式では上の問題は解決するが、B7:区分に「1」を入力した場合に表示されてしまう。 B7:区分に「1」が入力された場合はJ7:休出残業とK7:休出深夜に数字が飛ぶようにしてあるんです。 H7:時間外(実働8時間を超え、10時間までの時間表示) =IF(B7>0,"",IF(D7>"22:01",D7-C7-E7-"8:00"-I7,IF(D7-C7-E7>8/24,D7-C7-E7-"8:00",""))) I7:深夜残業(実働10時間を超えた場合の時間を表示)   =IF(B7>0,"",IF(D7>"22:00",D7-"22:00","")) J7:休出残業=IF(B7="","",IF(B7=1,IF(C7="","",IF(K7="",D7-C7-E7,D7-C7-E7-K7)),"")) K7:休出深夜=IF(B7="","",IF(B7=1,IF(D7>"22:01",D7-"22:00",""),""))

  • 勤務表の作成について

    勤務表の作成について 始業時間・終業時間・休憩時間を入力すれば、実働時間・定時時間・残業時間・深夜時間・深夜残業が 自動的に出るような表を作成しようとしています。 作成はしてみたのですが、どうも上手くいきません。 始業時間・・・手入力 終業時間・・・手入力 休憩時間・・・手入力 実働時間・・・終業-始業-休憩 定時時間・・・5:00~22:00の間の業務時間 残業時間・・・実働時間が8時間を超えていた場合の5:00~22:00の間の業務時間 深夜時間・・・5:00~22:00の間以外の業務時間 深夜残業・・・実働時間が8時間を超えていた場合の5:00~22:00の間以外の業務時間 今は以下のような(間違っている)計算式を入れています。 D8=曜日(休日には休と表示) D10=始業時間 D11=終業時間 D12=休憩時間 実働時間(D13)=IF(D10="","",D11-D10-D12) 定時時間(D14)=IF(D8="","",IF(AND(D8<>"休",D10=""),"休",IF(OR(D10="",D8="休"),"",IF(D13<TIME(8,0,0),D13,TIME(8,0,0))))) 残業時間(D15)=IF(D10="","",IF(D14="","",IF(D13-D14=0,0,IF(D11>TIME(22,0,0),IF(TIME(22,0,0)-D10-D12-D14<0,0,TIME(22,0,0)-D10-D12-D14),D11-D10-D12-D14)))) 深夜時間)D17)=IF(D10="","",IF(AND(D11>TIME(22,0,0),D11<TIME(5,0,0)),D11-D18-TIME(22,0,0),"")) 深夜残業==IF(D10="","",IF(D13=D14,0,D13-D14-D15)) どのような式を入れれば良いかご教授お願いします。

  • Excelの関数式について

    以前、こちらで勤務時間表の作成について質問をさせて頂きました。 この度勤務時間表のとなりに給料の早見表を作成することを考えております。 現在は、以下のようになっております。 C4=始業時間[基本8:45 変動なし]  D4=終業時間[基本17:15だが、残業に応じて15分単位で増える]   E4=実働時間[D4-C4-休憩(1時間)の合計]   =IF(C4="","",D4-C4-TIME(1,0,0)) F4=残業時間[基本労働時間(7時間30分)-実働時間の合計]   =IF(E4="","",E4-TIME(7,30,0)) この隣に以下内容を追加する事を考えております。 G4に実働時間分の給料[実働時間(E4)×1530円] H4に残業時間分の給料[残業時間(F4)×382円] I4に合計の給料[G4+H4] 下部のI35にI4からI35までの合計給料 を表示させたいのですが、今一上手に出来ません。 ご迷惑をおかけしますが、どのような式を入れたらよいのか、 ご教授頂けますよう、お願い致します。

  • 会社で勤務表を作成しなくてはならなくなりましたが、エクセル(2003)

    会社で勤務表を作成しなくてはならなくなりましたが、エクセル(2003)関数の知識がありません。 こちらでいろいろ検索しましたが、求めているものがなかったので質問をさせていただくことにしました。 ご教授お願いいたします。 現在、実働時間⇒=IF(B1="","",A1*24-B1*24-D1)と入力しています。 始業時間と終業時間を入力すると実働時間、実働時間が17:45を超えたら普通残業、22:00~翌5:00は深夜残業としたいのです。 日勤勤務は9:00~17:45、夜勤は17:30~翌9:00です。夜間勤務では1.25の休憩があるため実働時間は5.75となります。 また遅刻早退の時間数を求める関数も入れたいです。 24時間体制での仕事なので深夜の時間に対応したものを求めたいのです。 すべての欄の合計値は7:45とかの表示ではなく7.45という表記にしたいです。  始業時間 終業時間 休憩時間 実働時間 普通残業 深夜残業 遅刻/早退  9:00   17:45   1:00  17:30   翌9:00   休憩時間は標準の書式設定[1]になっています。 初めて質問させていただくので、わかりにくいかと思いますがどうかよろしくお願いいたします。

  • エクセル勤務表を作成したい

    色々調べてみたのですが、力が足りずうまく行かないので教えてください。 (1)9:00~18:00が基本の労働時間です。 (2)18:00以降は残業 (3)昼休憩は12:00~13:00の一時間で、その分実働から引きたい (4)12:00までの勤務は昼休憩を引きたくない (5)13:00からの勤務は昼休憩を引きたくない (12:00からの勤務は13:00からと同じになります) 今までは以下のような式を入れてました。 A列:日付(問題なし) B列:曜日(問題なし) C列:勤務開始時間(勤務する方が入力予定) D列:勤務終了時間(勤務する方が入力予定) E列:実働時間 =IF(C11="","",(D11-C11)-F11-G11) F列:休憩時間(1時間)・・・・ここは無くてもいいです。 =IF(C11="","",IF(AND(D11<=TIME(12,0,0)),TIME(0,0,0),TIME(1,0,0))) G列:残業時間(18:00以降) =IF(D11<=TIME(18,0,0),"0:00",D11-TIME(18,0,0)) これが、いいレベルのものだとは思ってませんが なんとか形になったのですが・・・ (6)9:00前に出勤も残業入れる という条件を出されまして、困っています。 皆さんのお知恵を貸してください! 休憩時間は表示しなくても大丈夫です。 実働が、(4)、(5)の条件を満たしていて表示されることと 残業が9:00前までと18:00以降の合算で出ると良いのですが・・。 よろしくお願いします!

  • エクセル関数で深夜勤務の計算

    エクセルで社内の勤務表を作成しています。深夜勤務の時間計算の関数がわかりません。教えていただけないでしょうか? 条件 1.深夜時間は22:00~05:00 2.始業、終業時間が深夜時間のみになる事もある 3.休憩時間も含めて計算したい。 例 1|   A |  B  |  C |  D  |  E |  F |  G | 2| 始業| 終業 | 再入| 再終|休憩| 実働|深夜 3| 9:00 |23:00 | 2:00 | 6:00 |2:00 |16:00| 宜しくお願い致します。

  • エクセル2003で勤務時間計算をしたいのですが、

    エクセル2003で勤務時間計算をしたいのですが、 現状は下記のように、H列の計算式が30分単位なのですが、 5分単位で給与計算して、小数点以下を切り捨てするには、 H列の計算式のどこを訂正するとよろしいでしょうか? ■現状(時給880円の場合) C列     D列     E列     F列     H列 始業時刻  終業時刻  休憩時間  実働時間  日給 10:00    17:30     0:45    6:45     5720円 H列の計算式 =IF(OR(C9="",D9=""),"",G9*HOUR(F9)+IF(AND(MINUTE(F9)>=0,MINUTE(F9)<=29),0,IF(AND(MINUTE(F9)>29,MINUTE(F9)<=59),G9/2,G9)))

  • Excelで勤務表を作りたいのですが

    Excelで勤務表を作りたいのですが、なかなか関数がうまく使えません。 時間計算をご存知の方、教えて下さい。 入力列 A:出勤時刻 B:退勤時刻 C:勤務開始時間 *始業9:00のため、9時より以前の出勤時刻は9:00、9:00以降の出勤はその時刻を表示する。 D:休憩時間 E:実働時間(始:C/終:B/マイナス:D) F:時間内勤務(8時間まで) G:時間外勤務(8時間を超過したところから・22時迄) H:深夜残業(22時以降の勤務時間) このような説明でお分かり頂けるでしょうか。 よろしくお願いします。

  • 勤務表を作りたいのですが。

    Excel97で勤務表を作る事になったのですが、 始業時間 終業時間 休憩時間 時間内 時間外 合計時間  8:00    18:30    1:00    8.0   1.5    9.5 とゆう形式で作りたいのですが、始業時間、終業時間だけ入力するだけで他が計算されるようにできないでしょうか? あと、合計で何日働いたかカウントしたりは出来ないのでしょうか? 初心者なので、どうしたら良いのかぜんぜん分かりません(T_T) どなたか良いアドバイスをお願いします。m(__)m

  • Excelでの勤務表作成 *夜勤がある場合の計算式

    Excelで勤務表を作成しているのですが 関数の作成に困っています。 私の職場には日勤(9:30~18:00)と夜勤があります 夜勤は 宿直(17:00~24:00)、宿明け(0:00~10:00) の時間帯です。 セルA 開始時間 セルB 終了時間 セルC 休憩時間 セルD 稼働時間 セルE 所定時間 セルF 時間外残業 セルDの稼働時間を求める関数に =IF(OR(A1="",B1=""),0,B1-A1-C1) セルEの所定時間の関数を =IF(D1>$N$44,$N$44,D1)  としました。*N44には7:30を投入 この場合は日勤の場合の勤務時間は正しく反映されますが 夜勤(宿直、宿明け)の勤務時間を投入した場合 時間外残業が正しく反映されません。 所定時間を日勤の場合は7:30(7時間30分) 宿直の場合は6:00(6時間) 宿明けの場合は9:00(9時間) と設定すればうまくいくと思うのですが どのように設定したらよいでしょうか?