- ベストアンサー
エクセルで残業時間計算
たくさんの質問が既出なのですが、行き詰ってしまったのでヘルプをお願いします。 B2に出勤時間 C2に退勤時間 D2に休憩時間 E2に勤務時間 F2に残業時間 を表示します。 15分刻みで時間を計算しますがB2、C2、D2には自分で15分ごとのキリのいい時間を入力します（19:50退社でも19:45と入力します）。 今、E2には =C2-B2-D2 という数式を入れてあります。 F2には =FLOOR(E2,"0:15")-"8:00" という数式を入れてあります。残業時間は単純に勤務時間－８時間と考えたからです。 ところが、ウチの会社は時として遅番や早帰りなどがあり、一日の労働時間が８時間に満たない場合があります。これは1ヶ月トータルで考えて残業時間からマイナスになります。F2はこの数式では「####」になってしまいます。マイナス時間も表示させて、最終的に表の下の方で合計を出したいのです。 この場合F2の数式はどうすれば良いのでしょうか？FLOORは相応しくないと言う事でしょうか？
- piyo1969
- お礼率68% (280/406)
- オフィス系ソフト
- 回答数7
- ありがとう数4
- みんなの回答 （7）
- 専門家の回答
質問者が選んだベストアンサー
集計の数式に誤り（重複カウント）があったので、修正します。 正しく求めるには、 =SUMIF(F2:F33,"",G2:G33)-SUMIF(F2:F33,"-",G2:G33) です。すみません。なお、日毎の累計を、H列に設けるには、 H2セルの数式： =SUMIF(F$2:F2,"",G$2:G2)-SUMIF(F$2:F2,"-",G$2:G2) これをフィルでコピーです。ただし、累計欄で日付シリアル値がマイナスに なるとやはり ##### と表示されますので、これまた同様の工夫が必要です。 管理上は他の回答者さまの方法が好ましいと思いますが。
その他の回答 (6)
- zap35
- ベストアンサー率44% (1383/3079)
＃０３です すみません先ほどの回答ではカラムがずれていましたね。非表示にするのはＦ列。追加するのはＧ列でした。 個人的には毎日の残業時間を表示した方が実務面ではベターだと思います。直感的に日々の数字が正しいかが判別できるからです。 また追加した表示用Ｇ列の一番下に =IF(SUM(F2:F32)>0,SUM(F2:F32),TEXT(SUM(F2:F32),"-hh:mm") とすれば、合計も出せますよ。
お礼
＞個人的には毎日の残業時間を表示した方が実務面ではベターだと思います。直感的に日々の数字が正しいかが判別できるからです。 はい、確かにその通りですね。皆さんのおかげでどうにか使えるものが出来上がりました。本当にありがとうございます。
- KenKen_SP
- ベストアンサー率62% (785/1258)
こんにちは。KenKen_SP です。 マイナス表記させるセルを分けるとか？ 【仕 様】 ・F2 をプラス・マイナス表示セルとする ・G2 に残業時間を表示する 【手 順】 1. F2、G2 にそれぞれ数式を入力 F2： =IF(AND(E2>0,G2>0),IF(E2<8/24,"-",""),"") G2： =IF(E2>0,ABS(FLOOR(E2,1/24/60*15)-8/24),"") 2. F2:G2 を選択し、条件付き書式 [数式が] =$F2="-" ---> 書式でフォントを赤に 3. F2 と G2 の間に罫線がある場合は消去し、セルを白で塗りつぶしで --＞これで F2:G2 が連結セルのように見えます 4. データ終端まで、F2:G2 をフィルでコピー 【集 計】 33行目までデータがあると仮定すれば、次の数式です。 =SUM(G2:G33)-SUMIF(F2:G33,"-",G2:G33)
補足
ありがとうございます。 見た目がずいぶんきれいになってきました！ 今Ｆ列を＋－表示、Ｇ列に残業時間を表示するようにしてみました。そして、Ｈ列に「残業の小計」、つまりその日までの残業時間の合計を出すようにしたんですが… マイナスの日がプラス計上されてしまいます。ここを何とか出来ればもう完成なんですが。
- merlionXX
- ベストアンサー率48% (1930/4007)
> 表示は標準だと小数点がいっぱいの数値（シリアル値というのでしょうか？）が出てしまいます。 数値は24時間を１とした場合の少数です。だから、たとえば3時間なら=3/24で0.125となってるはずです。 > ####のままで足し算をしてみましたがうまくありませんでした。 どう、うまくいかないのですか？ 足した結果が少数表示ということであれば、そのセルの書式を、ユーザー定義で[h]:mm に変えればちゃんと表示されますよ。（マイナスでない限り）
補足
度々ありがとうございます。 ＞ユーザー定義で[h]:mm に変えればちゃんと表示されますよ。（マイナスでない限り） これを忘れていました（汗） 何とかサマになりそうです！
- zap35
- ベストアンサー率44% (1383/3079)
姑息的手段ですが Ｅ列は非表示として、Ｆ列に =IF(E2>0,TEXT(E2,"hh:mm),TEXT(-E2,"-hh:mm")) としてしまうのも手です。 これだと -01:00 と表示されます。ただし合計はＥ列で行う必要があります。 ＞####のままで足し算をしてみましたがうまくありませんでした。 とのことですが、試したらできましたが…
補足
皆さん、本当にありがとうございます。今エクセル教えてgooを同時進行で見ております。 merlionXXさん、すいません。改めてやってみたら出来ました！ zap35さん、ありがとうございます。 E列はE列で合計を出したいので、マイナス表示に拘るのは止めようかと思います。最終的な残業時間が計算されればとりあえず満足なので。
- merlionXX
- ベストアンサー率48% (1930/4007)
> F2はこの数式では「####」になってしまいます。マイナス時間も表示させて、最終的に表の下の方で合計を出したいのです。 エクセルの書式ではマイナスの時間という概念がないので、表示できずに「####」となるだけで、中身はちゃんとマイナスの数値になっているはずです。書式を時刻ではなく標準に変えてみるとわかると思います。 だから最終的に合計すればプラスにはなるのでしょうから合計は正しく表示されるはずですが・・・。
補足
早速のご回答ありがとうございます。 表示は標準だと小数点がいっぱいの数値（シリアル値というのでしょうか？）が出てしまいます。####のままで足し算をしてみましたがうまくありませんでした。
- ysko614
- ベストアンサー率31% (103/329)
=IF(E2<8/24,"",E2-8/24) 残業時間を上記の通りにしてはいかがですか？ 時間がないのでご質問あればお聞き下さい。 後ほどお答えします。
補足
早速のご回答ありがとうございます！ この数式ですとマイナスの日のところの####は消えますがマイナス表示はされないんですね。 例えば11:45出社、休憩１時間、19:45退社の場合は「-1:00」と出るようにしたいのです。
関連するQ&A
- 残業時間の計算 累計
エクセルの関数は苦手なので教えてください。自分なりにしても上手くいきません。。。 Ａ Ｂ Ｃ Ｄ Ｅ Ｆ １ 日 曜日 出勤時間 退社時間 残業時間 残業時間累計 ２ １日 月 9:00 19:00 1.0 1.0 ３ ２日 火 10:00 19:45 0.5 1.5 ４ ３日 水 ５ ４日 木 9:00 18:00 0 1.5 という風にＥ欄・Ｆ欄を自動的に計算し、尚且つＣ・Ｄ欄に時間を入力しなくてもエラー（＃＃＃＃＃＃）などが出ない様にするにはどのような数式を入れると良いのでしょうか？こちらのサイトの質問例などを参考にし、自分なりに色々試してみましたが上手くできません。どなたか詳しい方、教えてください。宜しくお願い致します。 ちなみに実労働時間が８時間、休憩１時間になりますのでＥ欄の数式は＝Ｄ２－Ｃ２－”９：００”という数式を入れてみましたがＣ・Ｄ欄に時間の入力をしないとエラー（＃＃＃＃）が出てしまいます。
- 締切済み
- オフィス系ソフト
- 残業代の計算をエクセルで・・・。
エクセルで残業代の計算をしたいのですが、どのようにすればいいのかが全く分かりません。 Ａ列は日付、Ｂ列は出勤時間、Ｃ列は退勤時間、Ｄ列は勤務時間（単純に退勤時間から出勤時間を引いた値）、Ｅ列に休憩時間が入力されています。 希望としてはＦ列に実働時間が、Ｇ列に残業時間が入るようにしたいのです。 ①休憩時間は原則１時間ですが、例えば３０分しか取れなかった場合は本来取るべきだった残りの30分を残業代に含めることにしています。 ※労基法？ではあまり良しとしていないようですが、ここではあえて無視をしています。 ②勤務時間は午前8時30分から午後5時15分までで、それ以降に勤務していた場合は残業代として15分刻みで支給します。 以上の条件を考慮した残業時間がＧ列に出る関数がわかりません・・・。 また、土日祝日に勤務した場合は、Ｈ列に休日出勤時間が出るようにしたいのです。 給与計算を担当することになったのですが、前任者は一つ一つ手計算をしていたようなのですが、手間がかかり、また間違いの元にもなるかと思いエクセルの数式で何とかできないかと思い質問させていただきました。 何卒ご協力お願いいたします。
- 締切済み
- Excel（エクセル）
- エクセル２０１０ 深夜残業時間の計算
出社・退社の時間から、残業、深夜残業など、 残業時間を算出する数式を作成していますがどうにもうまく出来ません。 残業となる時間の切り分けは、平日は以下のようになっています。、 基本時間→8：30～17：15 残業→5：00～8：30 と17：15～22：00 深夜残業→22：00～翌朝5：00 休憩時間→0:45(C1) 基本勤務時間→8:00(B1) 例えば平日、朝6：00出社→23：00退社であれば、 早朝残業が2:30、通常残業が4:45、計7:15です。深夜残業が1:00となります。 F欄に時間外、 G欄に深夜残業としています。 現状は時間外を出すのに =SUM(E5-D5)-$B$1-$C$1-G5 と単純にしてます。(G5は手入力) これを自動で入力できたらと考えています。 5:00前に出勤することはほとんどありませんが、22:00を超えることはあります。 ただし、24:00を超えると翌日計算になります。 過去欄を参考にしようにも理解できなく困っています。 http://okwave.jp/qa/q8147465.html よろしくお願いいたします。
- ベストアンサー
- Excel（エクセル）
- エクセルの関数について(残業時間表を作っています）
社員の勤務残業表を作っています A-1に出社時刻9：00 B-1に退社時刻18：00を入れたとして残業時間C-1の関数式について教えて下さい 1.（18：00）から（9：00）を引いた時間から“8”をマイナスした時間“1”を表示する その時計算が“0”及びマイナスになった場合は“0”とする 2. D-1に（公休）入力があった場合計算結果は表示しない（空欄） 3. E-1に（休出）入力があった場合“8”マイナスしない（計算結果は“9”） このような計算式をC-1に入れることが出来るでしょうか？お願いいたします。
- ベストアンサー
- オフィス系ソフト
- エクセル 残業時間教えてください。
勤務時間を直接入力し6時間引いたのが残業時間なのですが、それにプラス、 県内を走った時は50分足して、県外だと55分足したいのですが、教えてください。 例） A1に10時間と記入し C1に１００と記入したときに B1に４：５０と表示したいです 。 （県外の時はD１に１００と記入してB1に４：５５と表示）＊C1とD１は数字は１００とはかぎりません 県内の時（50分） A1 勤務時間（10：00） B1 残業時間（4：50） C1 県内キロ（１００） D1 県外キロ（1００） 県外の時（55分） A1 勤務時間（10：00） B1 残業時間（4：55） C1 県内キロ（１００） D1 県外キロ（1００）
- ベストアンサー
- プレイステーション
- 勤務時間の計算（定時間内、残業の割り振り）について
他の質問を確認しましたが、当てはまるものが見つかりませんでした。すみませんが教えてください。 勤務時間の計算で、定時間内分、時間外分の割り振り方を教えてください。 定時間内勤務は8:30～17:30（休憩通常1H、通常8時間勤務)です。 8:30～17:30は定時間、それ以外は時間外とします。 割り振りは1日ごとに行い、範囲は0:00～24:00とします。 入力は下のようにします。 A1：開始時間 B1：終了時間 C1：定時間内休憩 D1：時間外休憩 E1：定時間内 F1：時間外 G1：休日 A1、B1は[hh]:mmで入力（15分単位）。C1～F1は0.00(H)を0.25(15分単位）単位で入力または表示します。 たとえば、A1:7:30、B1:22:30、C1：1.0、D1：0.5のとき、E1：8(H)、F1：5.5(H)のように、E1とF1を自動で計算させたいのです。 G1は休日フラグとし、0なら平日で時間内がある、1なら全て時間外とします。 勤務は不規則で以下のようなケースもあります。 ●定時間にかからないケース(1)↓ A1=1:00、B1=8:00、C1=0、D1=0.75、G1=0のときE1=0、F1=6.25 ●定時間にかからないケース(2)↓ A1=17:30、B1=23:30、C1=0、D1=0、G1=0のときE1=0、F1=6 ●定時間にかかるケース(1)↓ A1=5:00、B1=15:15、C1=0.75、D1=0、G1=0のときE1=6、F1=3.5 ●定時間にかかるケース(2)↓ A1=11:00、B1=24:00、C1=1.0、D1=0.5、G1=0のときE1=5.5、F1=6 ●定時間にかかるケース(3)↓（極端な例） A1=0:00、B1=24:00、C1=1、D1=2、G1=0のときE1=8、F1=13 ●休日のとき A1=6:30、B1=18:30、C1=0、D1=1、G1=1のときE1=0、F1=11 ・・・のようにE1、F1を求めたいのです。 複雑な式になるかと思いますが、よろしくお願いいたします。
- ベストアンサー
- オフィス系ソフト
- excel2000で、残業時間を求めたいんですが
Excel2000で、残業時間を求めたいのですが、 勤務時間は、C2-B2-TIME(0,D2,0)で 求めることができたのですが、残業時間（分単位）では どのようにして求めたらよいのでしょうか?又残業時間の合計も求めたいので、是非アドバイスお願いします A B C D E F 日付 開始時間 終了時間 休憩時間 勤務時間 残業 2/1 8：00 17：00 60分 8時間 0分 2/2 8：00 17：30 60分 8時間 30分
- ベストアンサー
- オフィス系ソフト
- エクセルで時間外計算の仕方
始業から終業時間がまちまちで、下記のような表を作成したいのですが上手くいきません。 何かいい方法はありましたら教えてくださいm(__)m 作りたい表↓ 超過時間は10時間以上、15分未満切捨てとし、残業代は1時間当り500円とした場合 始業時間 終業時間 稼動時間 超過時間 残業代 7:00 18:15 11:12 1:00 500 5:00 13:13 8:13 0:00 0 23:00 12:16 13:16 3:15 1625 作ってみて失敗した表↓ Ａ1に10:00 Ｂ１に500と入力し Ａ２に始業時間 Ｂ２に終業時間 Ｃ２に稼動時間 Ｄ２に残業時間 Ｅ２に残業代 として Ａ2 Ｂ２ Ｃ３ Ｄ４ Ｅ２ 7:00 8:15 =FLOOR(B2+1-A2,"1:0") =FLOOR(C3+1-A1,"0:15") =D4*B1 ※稼動時間が10時間未満で残業がつかない場合の計算ができない ※残業時間に残業代を掛けた計算の計が違う 以上、ＩＦ関数を使えばいいのかとも思ったのですが、どう使えば有効なのかも判らずにおりました。 よろしくお願いします。
- ベストアンサー
- 財務・会計・経理
- 残業時間月合計（エクセル）を正確に計算できません
エクセル２００７で，勤務時間を管理表を作成していますが，残業時間月合計（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列の計算がマイナスになることが原因で正確に計算できないのでしょうか。 簡単な関数を使って正確に残業時間月合計が計算できる方法を教えてください。 よろしくお願いします。
- ベストアンサー
- オフィス系ソフト
- エクセルで残業時間を時間帯によって＋１時間＋30分にしたい
関数がわからなくて困っています。 基本は8:30～17:30 残業（早出含む）とされる時間帯 5:00～7:30／17:30～19:30 この時間帯は1.0時間刻み （退社が18:00だと残業は0） 7:30～8:30／19:30～24:00 この時間帯は0.5時間刻み （退社が20:00だと残業は2.5） 休憩は1:00（12:00～13:00） 以上の条件でＣ列―出社時刻、Ｄ列―退社時刻、 Ｅ列―勤務時間、Ｆ列―残業時間 で表を作った場合、Ｆ列の残業時間を出す式がわかりません。 （Ｅ列の勤務時間もですね。自分で考えたのはアヤシイ） E/F列は、例えば1時間30分なら1.5と出てくるようにします。 7:30出社で20:00退社の場合、勤務時間は8.0、残業時間は3.5となる ようにしたいのです。 時間の計算が苦手で・・・。どなたか教えてください。
- ベストアンサー
- オフィス系ソフト
お礼
色々試行錯誤した結果、KenKen_SPさんの数式を採用してみました。お陰様で大助かりです。ありがとうございました！