• ベストアンサー

作業時間をエクセルで計算させたい

作業時間をエクセルで計算させたいのですが、よろしくお願いします。 条件は 1.作業時間 0時~24時 2.普通作業時間帯 8時30分~17時20分(昼休み1時間で7時間50分) 3.普通時間外作業時間帯 5時~8時30分及び17時20分~22時 4.深夜時間外作業時間帯 0時~5時及び22時~24時 5.昼休みは12時~13時 6.時間は分単位 です。この条件で 作業開始時間  作業終了時間 普通時間 普通時間外 深夜時間外    A1         B1     C1     D1     E1 という項目を設け、作業開始時間A1と作業終了時間B1に時間を入力し、 普通時間C1・普通時間外D1・深夜時間外E1のセルに計算させたいのですが よろしくお願いします。作業ですのであらゆる時間帯があり、作業終了時間も決まってません。できれば、マクロでなく関数で計算させたいのですがよろしくお願いします。  

  • rori
  • お礼率27% (10/37)

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

  • ベストアンサー
  • nishi6
  • ベストアンサー率67% (869/1280)
回答No.1

     A      B      C     D      E 1 作業開始時間 作業終了時間  普通時間  普通時間外  深夜時間外 2   入力     入力     式1     式2     式3 3 と表があるとします。C2、D2、E2に式1、式2、式3を入力します。後はコピーしてください。 式1= IF( MIN("12:00",B2)>MAX( "8:30",A2) ,(MIN("12:00",B2)-MAX( "8:30",A2)),0)    +IF((MIN("17:20",B2)>MAX("13:00",A2)),(MIN("17:20",B2)-MAX("13:00",A2)),0) 式2= IF( MIN( "8:30",B2)>MAX( "5:00",A2) ,(MIN( "8:30",B2)-MAX( "5:00",A2)),0)    +IF((MIN("22:00",B2)>MAX("17:20",A2)),(MIN("22:00",B2)-MAX("17:20",A2)),0) 式3= IF( MIN( "5:00",B2)>MAX( "0:00",A2) ,(MIN( "5:00",B2)-MAX( "0:00",A2)),0)    +IF((MIN("24:00",B2)>MAX("22:00",A2)),(MIN("24:00",B2)-MAX("22:00",A2)),0) 作業終了時間が24:00を超える場合は想定していません。 作業開始時間~24:00と0:00~作業終了時間に分けて入力します。 また、24時間超の勤務時間は対応していません。(対応すれば、式がどんどん複雑になっていきます) 上式は、下のユーザー定義関数(面倒な期待値計算等を計算する時に使う方法です。)を考えた後、作り出した式です。 ある意味、調和が取れていてきれいではあります。ただ、ユーザー定義関数の方が可読性、保守性はいい様に思いました。 関数をお望みのようなので作ってみました。ご参考に。 <下の関数の使い方>  =KinmuJikan(勤務開始時刻セル、勤務終了時刻セル、計算区分) ・・・・・・・・・計算区分 1:普通、2:普通時間外、3:深夜 のように使うユーザー定義関数です。 Function KinmuJikan(srtTM As Date, endTM As Date, Kinmu As Integer)   Dim tmTable As Variant  '作業時間帯のテーブル   Dim tmKbn As Variant   '作業時間帯の区分(0:休み、1:普通、2:普通時間外、3:深夜)   tmTable = Array("0:00", "5:00", "8:30", "12:00", "13:00", "17:20", "22:00", "24:00")   tmKbn = Array(0, 3, 2, 1, 0, 1, 2, 3)   Dim srtKbn As Integer  '勤務開始の区分   Dim endKbn As Integer  '勤務終了の区分   Dim t As Integer     'カウンタ   'どの区分から勤務開始し、どれで終わったか調べる   For t = LBound(tmTable) + 1 To UBound(tmTable)     If tmTable(t - 1) < srtTM And srtTM <= tmTable(t) Then srtKbn = t     If tmTable(t - 1) < endTM And endTM <= tmTable(t) Then endKbn = t   Next   Dim srtTime As Date   '作業時間帯での計算開始時刻   Dim endTime As Date   '作業時間帯での計算終了時刻   KinmuJikan = 0   For t = srtKbn To endKbn     srtTime = Application.Max(tmTable(t - 1), srtTM)     endTime = Application.Min(tmTable(t), endTM)     '勤務時間を合算する     KinmuJikan = KinmuJikan - (endTime - srtTime) * (tmKbn(t) = Kinmu)   Next End Function

rori
質問者

お礼

急な出張で、返事が今日になってしまいすみませんでした。上記の式1.2.3で見事できました。さっそく使い業務にいかしたと思います。ありがとうございました。

その他の回答 (1)

  • imogasi
  • ベストアンサー率27% (4737/17068)
回答No.2

苦労して1日考えました。理解していただけるか心配ですが、取り急ぎ一応載せます。テストも充分でないので、理解して・テストして、自己責任で使ってください、責任は取れません。業務用ソフトなので簡単には出来ません。頑張ってください。 (1)始業時刻はA列、終業時刻はB列に入力する。 8:30や5:30のように入力する。真夜中2時の場合も2:00と 入力してください。 始業時刻>0、終業時刻>0のチェックはしていません。 後記の変換後時刻を考えた後で、始業時刻<終業時刻はチェックしてない。 朝5時を跨いだ勤務時間の入力は、次ぎの日(次行に記入)してもらう約束とすること。別の勤務日の扱いにする。 昼の休息時間との関係で、12時台の終業時刻も12時を越える部分は1時以降に繰り越さず、休憩時間としている (各社取り扱いが違うでしょう。問題ありの点)。本来入れさせない方が簡単になる。12時30分まで続けて働いて帰った人は、1:30と記入可とするとか。 (2)朝5時が1日のスタートなので(私のやり方では、そう捕らえるので)朝5時を0時に変換する。丁度5時間時差のある国へ行ったと考える。(私の工夫) (3)したがって始業時刻を変換した時刻をC列に、終業時刻を変換した時刻をD列に入れる。変換は後記の関数式で行う。 (4)1日を、(A)早朝就業、(B)通常就業、(C)時間外就業、(D)深夜就業と名付け、4つに区分する。 AはE列、BはF列、CはG列、DはH列に出す。 (5)実際の時間外手当てでは(A)と(C)は同じ扱いだと思うが、計算と考える容易さのため、一旦別に分けている。あとで足すことになる。 (6)(B)の時間帯の場合、昼の1時間休憩時間を差し引くべきだが、これも計算の容易さのため、一旦は含めて計算している。後に別途I列を差し引く。 (7)時刻は8:30のように、:を使って入力する。(標準の時刻の入力方法) (8)A,B,C,D列の時間計算は、セルにIF関数等で式を入れると、非常に複雑で長い式になりすぎるので、別途関数を作った。(私の工夫) (9)その関数名は、AはRG1、BはRG2、CはRG3、DはRG4で、引数は2つで、C列とD列の値である。例=RG1(C2,D2)など。 (10)(3)の就業時間の変換(C列)は    =IF(A2-5/24>0,A2-5/24,A2+19/24)    (3)の終業時間の変換(D列)は    =IF(B2-5/24>0,B2-5/24,B2+19/24) で、ご覧のように勿論形は同じである。 (11)(9)のRG1~RG4と言う関数の内容は下記の通り。VBEの標準モジュールにコピーし貼りつけること。 Function RG1(s, e) Select Case s Case Is < 3.5 / 24 If e < 3.5 / 24 Then RG1 = e - s Else RG1 = 3.5 / 24 - s End If Case Else RG1 = 0 End Select End Function '---------------- Function RG2(s, e) Select Case s Case Is < 3.5 / 24 If e < 3.5 / 24 Then RG2 = 0 ElseIf e < 12.5 / 24 Then RG2 = e - 3.5 / 24 Else RG2 = 12.5 / 24 - 3.5 / 24 End If Case Is < 12.5 / 24 If e < 12.5 / 24 Then RG2 = e - s Else RG2 = 12.5 / 24 - s End If Case Else RG2 = 0 End Select End Function '--------------- Function RG3(s, e) Select Case s Case Is < 12.5 / 24 If e < 12.5 / 24 Then RG3 = 0 ElseIf e < 17# / 24 Then RG3 = e - 12.5 / 24 Else RG3 = 17# / 24 - 12.5 / 24 End If Case Is < 17# / 24 If e < 17# / 24 Then RG3 = e - s Else RG3 = 17# / 24 - s End If Case Else RG3 = 0 End Select End Function '-------------- Function RG4(s, e) Select Case s Case Is < 17# / 24 If e < 17# / 24 Then RG4 = 0 Else RG4 = e - 17# / 24 End If Case Else RG4 = e - s End Select End Function (注)12.5の0.5は30分を表す。変換前の12.5+5=17. 5(すなわち夕5時)を表す。 24で全て割っているのは、エクセルが時刻は1日=24時間を1とする数値として 表して・扱っているからです。 (12)E,F,G,Hの第2行の関数式は下記の通り。第3行目以下は複写して下さい。 E2は=RG1(C2,D2) F2は=RG2(C2,D2) G2は=RG3(C2,D2) H2は=RG4(C2,D2) I列は=KYU(C2,D2) (13)昼休憩時間を計算する関数式は(I列) Function kyu(s, e) Select Case s Case Is < 7# / 24 If e < 7# / 24 Then kyu = 0 ElseIf e > 8 / 24 Then kyu = 1 / 24 Else kyu = e - 7 / 24 End If Case Else kyu = 0 End Select End Function (14)あとはE+Gを出し、Bから昼休憩Iを引き、E+G、Hには自社の時間外料率を掛けて、Bには時給者には時給額をかけて計算してください。 ここでは省略します。ここでいう表は計算表・ワークシート(裏方表)なので別シートにした方が良いかもしれない。 (15)データサンプル(A列からI列。OKWEBで強制改行される と思いますので、修正して見て下さい。) 実際就業 実際終了 換算就業 換算終業 早朝就業 通常就業 時間外就業 深夜就業 昼休憩 7:30 18:30 2:30 13:30 1:00 9:00 1:00 0:00 1:00 1:30 3:30 20:30 22:30 0:00 0:00 0:00 2:00 0:00 3:30 4:00 22:30 23:00 0:00 0:00 0:00 0:30 0:00 4:00 4:30 23:00 23:30 0:00 0:00 0:00 0:30 0:00 5:00 5:00 0:00 0:00 0:00 0:00 0:00 0:00 0:00 6:30 8:30 1:30 3:30 2:00 0:00 0:00 0:00 0:00 9:30 18:30 4:30 13:30 0:00 8:00 1:00 0:00 1:00 10:30 17:30 5:30 12:30 0:00 7:00 0:00 0:00 1:00 23:30 24:00 18:30 19:00 0:00 0:00 0:00 0:30 0:00 7:00 8:20 2:00 3:20 1:20 0:00 0:00 0:00 0:00 8:00 8:20 3:00 3:20 0:20 0:00 0:00 0:00 0:00 (16)表のセルの書式は「時刻」を選ぶこと。すると上 表のように3:00のように、表示されます。 (17)テストデータはエクセルで、始業時間・就業時間を共に10分おきに作り、結果を入念にチェックされることをお勧めします。24*6*24*6=20736行で 不可能ではないでしょう。

関連するQ&A

  • エクセル 時刻(時間)計算について

    はじめて記載させていただきます。 エクセルで時刻の計算式を作りました。 添付画像も添付しておきます。ご精査していただきご回答をお願い致します。 日中作業分は○○時間 夜間作業分は○○時間と分けたい C3 直接入力 D3 直接入力 E3 D3-C3+(C3>D3) F3 E3-G3 G3 IF(COUNT(C3:D3)<2,0,MAX(MIN(D3,"8:00"+(C3<D3))-MAX(C3,"20:00")+(C3>D3),0)+IF(C3<"8:00"*1,"8:00"-C3,0)) 条件 1 8:00~20:00 日中作業分 2 20:00~8:00  夜間作業分 ★下記表の通り、開始4:00 終了7:00等になった場合うまく表示(計算されません) ※いろいろためしたのですが終了時間が8:00前の場合反映しないかもしれません ■現在の式では↓のように表示(計算)になってしまいます。 開始時刻 終了時刻  total  日中作業  夜間作業   4:00   7:00  3:00  #### 4:00 ■↓このように計算表示させたい   開始時刻 終了時刻 total 日中作業 夜間作業 4:00 7:00 3:00 0:00 3:00 よろしくお願い致します。

  • エクセルで日付をまたいだ作業時間を計算したい

    例えば、Aという作業を4月1日(土)13時から開始して、4月3日(月)の14時に作業が終了した場合。2日(日)は休日だとします。この間の実質作業時間を算出したい。 日付をまたいだ場合、又、休日や昼休みなどを差し引いた時間を算出するには、どのような計算式でできるのか知りたいです。 初心者です。なるべく簡単な式があれば教えてください。

  • Excel で作業時間を自動計算

    過去の投稿を検索したのですが見つからなかったので質問します。Excel 2000で作業時間を自動計算させるという単純な処理です。 B1に開始時刻(8:15など)、C1に終了時刻(16:45など)を入力すると、D1にその時間が自動で入力されるようにするには、どういう関数をどう使えばよいのでしょうか。ただし、休憩時間は考える必要はありません。

  • エクセルを使っての実務勤務時間計算を教えて下さい。

    エクセル初心者のため、何の知識もなく困っています。 実務勤務時間の計算をしたいのですが、どうすればいいのか・・・ よろしくお願いします。 まず、A1に早出の開始時間、B1に早出の終了時間(=9:00、固定)、C3に早出の実務労働時間、D1に残業の開始時間(=17:30、固定)、E1に残業の終了時間、F1に残業時間数、G1に休日出勤をした場合の開始時間、H1に休日出勤の終了時間、I1に休日出勤をした日の実務労働時間が入るようにしたいです。 休日出勤をした場合の条件としては、12:00~13:00は昼休みの為実務労働時間から1時間を引くことと、もし休日出勤の開始時間や終了時間が昼休みの時間帯であれば、その時間は出社していたとしても、労働時間に加算されないようにしたいです。 うまく説明ができてない気もしますが、どうかよろしくお願いします。

  • 【Excel】時間計算について

    Excel2003を使用しています。 残業時間の計算について、下記のような場合、F2とG2には、どのような数式を入力すればいいでしょうか? 22時以降は深夜残業となりますので、終了時刻が22時を過ぎた場合は、その部分の時間をG列の深夜残業に表示し、22時まではF列の残業に表示させたいのです。表示形式は、C列とE列は『時刻』、F列とG列は『h"時間"mm"分"』になっています。    C列     E列        F列      G列 1  開始時刻  終了時刻  残業      深夜残業 2  17時30分  23時30分  4時間30分  1時間30分 IFで条件分岐して入力してみたり、過去の質問も検索してみましたが、思うように表示させることができませんでしたので、質問させていただきました。 よろしくお願いします。

  • 休憩時間の変動を含むExcelでの作業時間計算

    こんばんは、skireです。 Excelで作業時間を計算するための表を作ろうと思っています。 そこで少々行き詰まってしまいましたのでお知恵をお貸し下さい。     A      B      C     D        E   開始時間 終了時間  居残り 作業時間 居残り作業時間 1  8:00   16:00   2 10:00   22:00  1:00 3  9:00   13:00 このD1以降のセルに休憩時間を差し引いた作業時間を自動で表示されるようにしたいです。 ですが、この休憩時間がちょっと面倒でして、 B1-A1が8:45以上なら1時間 B1-A1が6:00より多く、8:45未満の場合には45分 B1-A1が6:00以下なら休憩はなし という計算をします。 (21:00より遅くなると居残り→居残り作業時間。 21:00以降に作業をしに来た人に関しては今やると混乱しそうなので省いています。 いつかは挑戦したいのですが……) 自分で組んだ式は以下です。 D1= IF(TEXT(B1-A1,"h:mm")>"8:44",TEXT(B1-A1,"h:mm")-"1:00",IF(TEXT(B1-A1,"h:mm")>"6:00",TEXT(B1-A1,"h:mm")-"0:45",TEXT(B1-A1,"h:mm")))-TEXT(E1,"h:mm") E1=B4-"21:00" この式で、 B1-A1が6:00より多く、8:45未満の場合には45分 B1-A1が6:00以下なら休憩はなし の部分と居残り時間についてはうまくいくのですが、 8:45以上の休憩だけがどうしてもうまくいきません。 1:00マイナスされていない数字が入力されてしまいます。 小さなミスだとは思うのですが、見つけることが出来ません。 よろしくお願いします。

  • Excel:勤務時間の計算がうまくいかない。

    No.1468024の質問をした者です。 勤務表を作成しています。(Excel2002を使用) 終了時刻(D列)ー開始時刻(C列)ー休憩時間(E列)=当日の勤務時間を計算しています。 No.1468024の質問でアドバイスをいただき、以下のような計算式を記述しました。 <例> =AND(C13*D13)*(MIN(IF(AND(D13>="12:00"*1,D13<="13:00"*1),"13:00",D13),"17:40")-MAX(IF(AND(C13>="12:00"*1,C13<="13:00"*1),"12:00",C13),"09:00")-E13+FLOOR(MAX(D13,"18:00")-"17:59:59","0:15")) ほぼうまくいくのですが、開始時刻9:00終了時刻12:00とした場合、勤務時間4:00となり、間違ってしまいます。(正しくは勤務時間3:00。また開始時刻9:00終了時刻が12時から13時までの間の時刻で入力された場合も勤務時間3:00と出力したいです。) 休憩時間セルに以下のような計算式を書いています。 <例> =IF(COUNT(C13:D13)<2,0,IF(D13-"12:00"*1<=0,0,IF(C13-"12:00"*1>=0,0,IF(D13<"18:00"*1,"1:00","1:20")))) 休憩時間は12時から13時までの1時間。 終了時刻が17:40以降なら20分休憩時間が加算されます。 開始、終了が入力されていないと0:00表示。 言葉足らずでしたら、進んで補足させていただきますので、よろしくお願いします。

  • パート用 労働時間の時間外計算をエクセルでしたいのですが・・・

    エクセルで、パートタイム用の時間計算表を作成しております。 単純にタイムカードの様式をエクセルで作成し、項目毎に時間を入力し労働時間を算出することが目的です。※賃金の計算は不要。 私が作成した項目は、一行に 出勤(B2) ・ 時間内退勤(C2) ・ 時間内出勤(D2) ・ 退勤(E2) ・ その他(F2、G2) ・ 労働時間小計(H2,I2) ・ 時間外(J2) です。 ※時間内退勤・出勤はお昼休みに該当します。 労働時間小計欄、H2にはC2-B2を入れ、I2にはE2-D2を設定しました。 問題は時間外計算です。 時間外対象の条件ですが、基本労働時間 8時から17時を基準とし、 1.出勤→8時前に出勤した場合の時間差。 2.退勤→17時以降の時間差。 3.上記条件に該当しても、それぞれ5分未満の場合は対象外。 例; 出勤7:30  退勤17:15   時間外(J2) 0:45     出勤7:56  退勤17:30   時間外(J2) 0:30 このように、J2欄に出勤・退勤の時間外合計時間が表示されるようにしたいのですが、何か良い方法があれば教えて下さい。対象外の場合、空欄になるように設定したいと考えております。 補足:深夜24時を過ぎる事はありません。    時間の入力方法は1つのセル内に8:00というように入力します。 

  • エクセルでの時間外計算について

    時間外計算についてですが、 (1) 8:30~17:15までは休憩時間を引いて8時間とします。 (2) 17:15を超えてから、終業時間までは15分引きます。 (3) 早朝の6:30~8:30まではそのまま2時間とします。 (4) (1)の条件は平日のみとし、数値は求めません。 Excelで開始時間と終了時間を入れるだけで、4つの条件を満たす数値をだすことはできるのでしょうか?IF式を使うのでしょうか?

  • エクセルで勤怠時間(一部分の)計算方法

    エクセルで社員の勤怠時間の計算を行いたいのですが 始業から終業までの、全ての時間ではなく 始業から終業までの間で行っている、一部の時間(開始時間~終了時間)だけを取り出し計算し さらにそれを、日中時間・残業時間・深夜残業時間に振り分けを行いたいです。 始業時間は、バラバラで午前の人もいれば午後の人もいます。 また、始業=開始でない場合もあります。 色々と類似質問を参照し、式を入れているのですが振り分けがうまくいきません 以下に振り分けの際の条件と例を記載します。 C1:始業時間(手入力) D1:終業時間(C1+TIME(9,0,0)) E1:開始時間(手入力) F1:終了時間(手入力) G1,H1,I1:各時間帯から控除する休憩時間(手入力) J1:日中時間(始業時間~終業時間内の、8時間までの開始時間~終了時間) ※ここの式 K1:残業時間(22時までの終業時間を超過した、開始時間~終了時間) ※ここの式 L1:深夜残業時間(22時以降の終業時間を超過した、開始時間~終了時間) ※ここの式 例) (1)始業/9:00 終業/18:00 開始/13:00 終了/23:00 休憩1/0:30 休憩2/0:15 休憩3/0:00 結果→日中/4:30 残業/3:45 深夜残業/1:00 (2)始業/13:00 終業/22:00 開始/13:00 終了/24:00 休憩1/1:00 休憩2/0:00 休憩3/0:15 結果→日中/8:00 残業/0:00 深夜残業/1:45 お知恵をお貸し下さい

専門家に質問してみよう