• ベストアンサー

Excelで時間の合計が出来ない、日を跨ぐ場合！！

出勤、退社で稼働時間の集計をしたいのですが、18時より翌日の18時迄の場合、合計が00：00になり、24:00時間に成らないので、合計が出来ない、例えば、セルA1に出勤時間、セルB1に退社時間、セルC1合計時間で、セルC1に =F3-E3+IF(E3>=F3,1) を使用してます、又、G9合計欄はSUM(G3:G8)を使ってますが、49:00 になりません、どうすればいいのでしょうか？…（泣）どうか宜しくお願い致します。困っています、早急にお願いします。

画像を拡大する

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

  • ベストアンサー
  • kagakusuki
  • ベストアンサー率51% (2610/5101)
回答No.3

　回答No.1です。 >単に18:00と入力しただけでは出勤してから何日目の18:00なのかという事を区別する事が出来ません。 という事に関して、少々言葉足らずな感がありましたのでで説明させて頂きます。 　例えば18:00に出勤してその直後に再度タイムカードを押しますと出勤時刻と退社時刻はともに18:00になります。(勤務時間は0時間) 　又、18:00に出勤してその翌日の18:00に退社した場合にも出勤時刻と退社時刻はともに18:00になります。(勤務時間は24時間) 　更に極端な話を言えば、18:00に出勤してその翌々日の18:00に退社した場合にも出勤時刻と退社時刻はともに18:00になります。(勤務時間は48時間) 　これら3つの例では、どれもセルに入力されている値自体は全く同じなのですから、パソコンにその違いを区別せよというのは無理な話です。(それどころか例え人間であっても区別する事など出来ない事でしょう) 　ですから、翌日の18:00を入力する際には「18:00」と入力するのではなく、「翌18:00」とか「42:00」などの様に入力しなければならない訳です。 　因みに、退社時刻欄に 翌18:00 と入力する様にした場合にも正しい計算が行われる様にするためには、G3セルの関数を次の様なものにする必要があります。 =IF(AND(ISNUMBER($E3),$E3>=0,$E3<1,ISNUMBER(1/(SUBSTITUTE($F3,"翌",)+(LEFT($F3,1)="翌")>$E3))),SUBSTITUTE($F3,"翌",)+(LEFT($F3,1)="翌")-$E3,"")

全文を見る
すると、全ての回答が全文表示されます。

その他の回答 (2)

  • bunjii
  • ベストアンサー率43% (3589/8248)
回答No.2

>出勤、退社で稼働時間の集計をしたいのですが、18時より翌日の18時迄の場合、合計が00：00になり、24:00時間に成らないので 「合計が00：00になり」ではなく「勤務時間が00:00になり」ですよね？ 勤務時間が24時間以下の場合は提示された計算式で正しい結果を得られます。 但し、表示形式が24:00以上のときあなたが希望する表示（24:00）になりません。 目的に合わせるには表示形式を[h]:mmにすると良いでしょう。 >又、G9合計欄はSUM(G3:G8)を使ってますが、49:00 になりません、どうすればいいのでしょうか？ 計算は正しいので表示形式を[h:mm]に変更してください。

junmori2610
質問者

お礼

大変お礼が遅くなり、失礼しました。持病の狭心症の悪化で入院してました。 早速、参考にさせて頂き挑戦してみます、ありがとうございました。

全文を見る
すると、全ての回答が全文表示されます。
  • kagakusuki
  • ベストアンサー率51% (2610/5101)
回答No.1

>49:00 になりません という問題に関しては、セルの書式設定の表示形式を[ユーザー定義]の [h]:mm に設定されると良いと思います。 　それで、 >18時より翌日の18時迄の場合 という問題に関してですが、本当に24時間以上連続で勤務する様な事があり得るのでしょうか？ 　もしもその様な事もあるとしますと、48時間以上連続して勤務するという事も無いとは言えず、そうなりますと単に18:00と入力しただけでは出勤してから何日目の18:00なのかという事を区別する事が出来ません。 　ですから例えば、セルの書式設定の表示形式を[ユーザー定義]の [h]:mm に設定した上で、翌日の18:00の場合は 42:00 と入力する様にされては如何でしょうか？

junmori2610
質問者

お礼

大変お礼が遅くなり、失礼しました。持病の狭心症の悪化で入院してました。 早速、参考にさせて頂き挑戦してみます、ありがとうございました。

全文を見る
すると、全ての回答が全文表示されます。
  1. カテゴリ
  2. パソコン・スマートフォン
  3. ソフトウェア
  4. Excel（エクセル）

関連するQ&A

  • エクセル・時間の合計が合わない

    実労働時間の１ヶ月の合計をだすのに、ＳＵＭを使ったのですが、 合計がすごく小さい数字になります。 表示は※※：※※で表示しています。 表の内容は下記の通りです。 ４行目に題名 ・Ｂ出勤時刻・Ｃ休憩開始・Ｄ休憩終了・Ｅ退社時刻 Ｆ４・Ｉ４・Ｌ４・Ｎ４・Ｑ４＝０：００ Ｇ４・Ｊ４・Ｍ４・Ｏ４・Ｒ４＝１７：００ Ｆ　　=IF($E5-F$4>0,$E5-F$4,0)-IF($B5-F$4>0,$B5-F$4,0) Ｇ　　=IF($E5-G$4>0,$E5-G$4,0)-IF($B5-G$4>0,$B5-G$4,0) Ｉ　　=F5-SUM(J5:$K5) Ｌ　　=IF($D5-L$4>0,$D5-L$4,0)-IF($C5-L$4>0,$C5-L$4,0) Ｍ　　=IF($D5-M$4>0,$D5-M$4,0)-IF($C5-M$4>0,$C5-M$4,0) Ｎ　　=L5-SUM(O5:$P5) Ｏ　　=M5-SUM(P5:$P5) Ｑ　　=I5-N5　　（実労働時間１７時までの１日の計） Ｒ　　=J5-O5　　（実労働時間１７時からの１日の計） Ｑに=SUM(Q5:Q14) Ｒに=SUM(R5:R14) を入力しましたが、まったく違う時間数になります。 どうすれば正しい計算式がでるのでしょうか？ お教え願いたくお願い申し上げます。

  • エクセル　時間の合計数が適正に表示されません

    添付のようなタイムシートを作成しましたが、「合計の時間数」が適正に表示されません。 D12、E12には正しくない合計時間数が表示され、 F12、G12に至っては、表示すらされません（TT） 間違っているのではなく、適正な表示になってくれていないだけだろうなとは理解できるのですが なぜこんな表示になってしまうのか、まったくわかりません。 どなたかご教示いただけないでしょうか。 よろしくお願いいたしますm(_ _)m 各列の関数は以下のとおりです。 D列＝C-B-J E列＝MIN("8:00",D) F列＝D-K G列＝C-I 合計のセルは次のように設定しています。 D12＝SUM(D5:D11) E12＝SUM(E5:E11) F12＝SUM(F5:F11) G12＝SUM(G5:G11) 各セルの表示形式は、 [h]:mm;; です。 よろしくお願いいたしますm(_ _)m

  • エクセルの時間の合計が表示されないんですが・・

    すみません、以前も休憩時間の計算方法などでこちらで教えて頂いたのですが、 また、つまづいてしまいまして・・すみませんが教えてください。 エクセルで、勤怠管理をしています 負の表示はさせないように　 セルの書式設定の表示形式をユーザー設定で　[h]:mm;;　としたところ、 その複数セルの時間合計をSUMで自動表示させようとしたところ空白になってしまいます。 どうすれば合計が計算表示されるのでしょうか？ 時間表示は、０：００の60進法表示です。 具体的には下記のような表を作っています　（必要ない情報かもしれませんが） Aセル：出社時間 Bセル：退社時間 Dセル：延時間 Eセル：休憩時間 Fセル：就労時間（＝D-Eで自動表示) Gセル：8時間超勤務時間（＝F-("8：00"*1)で自動表示） Gセルのみ、負の表示はさせないように　セルの書式設定の表示形式をユーザー設定で　[h]:mm;;　としています そうしたところ、Gセルの時間合計をSUMで自動表示させようとしたところ空白になってしまいます。 どうすれば合計が計算表示されるのでしょうか？ 宜しくお願いいたします。

  • 残業時間月合計（エクセル）を正確に計算できません

    エクセル２００７で，勤務時間を管理表を作成していますが，残業時間月合計（G36のセル）が正確に計算されません。 社員に入力してもらうのは，出勤時間と退社時間だけです。 勤務時間（在勤時間）は８：２５～１６：５５で，途中の休憩時間は考慮しません。 項目と入力してある関数は下のとおりです。 　　A　　B　　　　　C　　　　　D　　　　　　E　　　　　　　　　F　　　　　　　　G 　　日　曜日　出勤時間　退社時間　在勤時間　　基本在勤時間　　　残業時間 ５　１　　金　　　　8:15　　　　17:00　　　8時間30分　　 8:30　　　　　　　　0時間15分 ６　２　　 土　　　　9:00　　　　10:10　 　 1時間10分　　 0:00　　　　　　　　1時間10分 36　月合計　　　　　　　　　　　　　 　　　229時間40分　　68:10 　　　　　 68時間10分　　　　　　　　　　　 E列の関数・・・「=D5-C5」（表示形式は　h"時間"mm"分"） F列（非表示）・・・平日は「8:30」と入力（8:25～16:55が8時間30分であるため） 　　　　 　　　　　　土日・休日は「0:00」と入力（勤務日でないため） Ｇ列の関数・・・「=D5-C5-F5」（表示形式は　h"時間"mm"分"） E36の関数「=SUM(E5:E35)」（表示形式は　[h]"時間"mm"分"） F36の関数「=SUM(F5:F35)」（表示形式は　[h]:mm） G36の関数=SUM(G5:G35)」（表示形式は　[h]"時間"mm"分"） 平日で，C列とD列が入力していないのに，F列に8:00が入力してあるため，G列の計算がマイナスになることが原因で正確に計算できないのでしょうか。 簡単な関数を使って正確に残業時間月合計が計算できる方法を教えてください。 よろしくお願いします。 　　　　　 　　　　　　　　

  • 一行に並んだ時間と単価をその行にて合計するには？

    下記のような表を作成しているのですが、例えば田中さんの 単価と時間の2列を合計して11列目に集計するのは、 SUMPRODUCT()で出来ますが、横に並んだ業務ごとの集計を 行なうにはどのようにすればよいのでしょうか？ 単純にSUM(B4*C4,D4*E4,F4*G4)でも出ますが、 従業員が50名を越えている為、SUM()が使えません。 また、上下のセルにも他のシートとのリンクにより行挿入による余白が 作れません。 なにか良い関数は無いのでしょうか？ A B C D E F G 1　 氏名　　　 田中　　　 佐藤　　　 鈴木 2　 項目　 単価 時間　 単価 時間　 単価 時間 3 　業務１ 500 　1.5 　　500　　　　 500　 1.0 4 　業務2 600 2.0 　　　　600　　　　 600 5 　業務3 700 0.5 　　　700 0.5 　　　700 6 　業務4 800 1.0 　　800 1.0 　　　　800 4.0 7 　業務5 900 　　　　900 0.5 　　　900 8 　業務6 1000 　　　　1000 3.0 　　　1000 9 　業務7 1100 2.0 　　1100 2.0 　　　1100 2.0 10 業務8 1200 1.0 　　　1200 1.0 　　1200 1.0

  • 表計算ソフトで時間の計算（合計）をする方法

    例えばE3のセルに8時30分・E4のセルに9時40分・E5のセルに10時00分と入力した場合、 関数のSUMを使うとEの合計が4時10分となってしまいますが、 時刻ではなく総合計時間として集計したいので28時間10分で表記させたいのですが、 どうすれば良いでしょうか？　何か方法がありましたらご教授ください。

  • 残業時間の合計が

    B F列 開始時間 C G列 退社時間 D H列　残業時間 E I列　残業手当 を入れて３１日まで入れています。 下記のような数式を(H９)に入れて３１日分まで(H３９) =IF(AND($F9<G$9,$G9>D$5),MIN($G9)-MAX($F9,D$5),0) ３１日分まで(H３９)までコピーしています 残業時間の合計時間ですが =SUM(D9:D39)　この列はちゃんとイイです =SUM(H9:H39)　この列は３０日迄はいいのですが３１日 　　　　　　　に入力されると０．００となります 　　　　　　　３０日迄はいいのです なにが問題なのか困っています どなたかご指導いただけませんか

  • エクセルの時間計算の数式を教えてください

    勤務管理をエクセルで作成しています。 19時前と、19時後の時間給を分けて支給することになり、 分けて集計をしたいのですが設定の仕方がわかりません。。。 出社１２：００　退社２２：００　とすると １２：００～１９：００　の　「７：００」 １９：００～２２：００　の　「３：００」 を自動計算したいのですが、どうしたらよいでしょうか？ ただ、出社時間と退社時間は、出勤していない日は０：００と表示するようになってしまってます。 そのため、 Aセル：出社時間　Bセル：退社時間　Cセル：１９：００　Dセル：B-A-E　Eセル：B－C にしみましたが、出社していない日の時間計算がうまくできません。。。 手計算は膨大な作業になってしまうので、自動計算させる方法を教えていただけると大変ありがたいです。 宜しくお願いします。 表示方法

  • CHOOSE関数を入れた複数セルの合計が０に

    CHOOSE関数を入れた複数セルをSUM関数で集計しましたが、結果が「0」と表示されてしまいます。集計するためにはどうしたらよいでしょうか？ エクセル初心者のため、妙な質問をしていたらすいません。 まず、重さで送料が求められるようにD３には =CHOOSE((K3>=0.3)+(K3>0.55)+(K3>0.65)+(K3>0.75)+(K3>0.85)+(K3>0.95)+(K3>1.05)+(K3>1.3)+(K3>1.55)+(K3>1.75)+(K3>2.05)+(K3>2.55)+(K3>3.05)+(K3>3.55)+(K3>4.05)+(K3>4.55)+(K3>5.05)+(K3>5.55)+(K3>6.05)+(K3>7.05)+(K3>8.05)+(K3>9.05)+(K3>10.05)+(K3>11.05)+(K3>12.05),"1200","1500","1680","1860","2040","2220","2400","2800","3200","3600","4000","4700","5400","6100","6800","7500","8200","8900","9600","10700","11800","12900","14000","15100","16200")といれて、 E3には引かれる手数料１５％で計算できるように、E1に０．１５といれて =G3*$E$1 そして、G3には合計が求められるように =SUM(C3:D3:E3:F3) といれましたが、０になってしまいます。 試しにG3に関数はいれずに、数字のみ（１２３０など）いれると、きちんと手数料の計算はできました。 SUMではダメなのかと思って =+C3+D3+E3+F3もいれてみましたが、０とでてしまします。 G3に循環参照があるとのことです。 セルの書式設定？が関係あるのかと思いG3やE3など右クリックして数値を選び「１２３４」のところを選びましたがダメでした。 どうしたら、数値の合計ができるでしょうか。 画像も添付させていただきました。どうぞよろしくお願いします。

  • CHOOSE関数を入れた複数セルの合計が０に

    CHOOSE関数を入れた複数セルをSUM関数で集計しましたが、結果が「0」と表示されてしまいます。集計するためにはどうしたらよいでしょうか？ エクセル初心者のため、妙な質問をしていたらすいません。 まず、重さで送料が求められるようにD３には =CHOOSE((K3>=0.3)+(K3>0.55)+(K3>0.65)+(K3>0.75)+(K3>0.85)+(K3>0.95)+(K3>1.05)+(K3>1.3)+(K3>1.55)+(K3>1.75)+(K3>2.05)+(K3>2.55)+(K3>3.05)+(K3>3.55)+(K3>4.05)+(K3>4.55)+(K3>5.05)+(K3>5.55)+(K3>6.05)+(K3>7.05)+(K3>8.05)+(K3>9.05)+(K3>10.05)+(K3>11.05)+(K3>12.05),"1200","1500","1680","1860","2040","2220","2400","2800","3200","3600","4000","4700","5400","6100","6800","7500","8200","8900","9600","10700","11800","12900","14000","15100","16200")といれて、 E3には引かれる手数料１５％で計算できるように、E1に０．１５といれて =G3*$E$1 そして、G3には合計が求められるように =SUM(C3:D3:E3:F3) といれましたが、０になってしまいます。 試しにG3に関数はいれずに、数字のみ（１２３０など）いれると、きちんと手数料の計算はできました。 SUMではダメなのかと思って =+C3+D3+E3+F3もいれてみましたが、０とでてしまします。 G3に循環参照があるとのことです。 セルの書式設定？が関係あるのかと思いG3やE3など右クリックして数値を選び「１２３４」のところを選びましたがダメでした。 どうしたら、数値の合計ができるでしょうか。 画像も添付させていただきました。どうぞよろしくお願いします。

注目のQ&A

カテゴリ

専門家に質問してみよう

職業から探して質問する

あなたにピッタリな商品が見つかる！ OKWAVEセレクト

質問する