- ベストアンサー
勤務時間の計算（定時間内、残業の割り振り）について
他の質問を確認しましたが、当てはまるものが見つかりませんでした。すみませんが教えてください。 勤務時間の計算で、定時間内分、時間外分の割り振り方を教えてください。 定時間内勤務は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を求めたいのです。 複雑な式になるかと思いますが、よろしくお願いいたします。
- shimeji
- お礼率74% (53/71)
- オフィス系ソフト
- 回答数3
- ありがとう数3
- みんなの回答 （3）
- 専門家の回答
質問者が選んだベストアンサー
E1=IF(G1=1,0,MAX(MAX(MIN(B1*24,17.5),8.5)-MIN(MAX(A1*24,8.5),17.5)-C1,0)) F1=IF(G1=1,(B1-A1)*24-D1,MAX(MIN(B1*24,8.5)-MIN(A1*24,8.5)+MAX(B1*24,17.5)-MAX(A1*24,17.5)-D1,0))
その他の回答 (2)
- hallo-2007
- ベストアンサー率41% (888/2115)
以下は、アドバイスです。 勤務時間のルールは結構 IF文を多用します。 後から式を見ても良くわからなくなることもあると思います。 列の後ろの方に A B C D E F G H I J K 早出残業時間 午前 午後 定時後残業 休日計算 とか作業列作って E列やF列に IF文で =IF(G2=0,G2+J2,K2) ＊残業の場合 とかにしておくとメンテナンスもやりやすいかと思います。 作業列が目障りであれば、非表示にしておきます。
お礼
アドバイス恐れ入ります。 見えないところにワークエリアを作ってやる手法ですね。 今回は、この手法を使用しない前提がありました。 質問の時に書いておく必要がありましたね。 大変申し訳ありませんでした。 アドバイスいただきありがとうございました。
- DIooggooID
- ベストアンサー率27% (1730/6405)
Ｅ１ ： =IF(G1<>1,IF(B1>=8.5/24,IF(B1>=17.5/24,17.5/24,B1),0)-IF(B1>=8.5/24,IF(A1<=8.5/24,8.5/24,A1),0)-C1/24,0) Ｆ１ ： =IF(G1<>1,IF(A1<8.5/24,(8.5/24-A1),0)+IF(B1>=8.5/24,IF(B1>17.5/24,(B1-17.5/24),0),0)-D1/24,IF(A1<8.5/24,(8.5/24-A1),0)+IF(B1>=8.5/24,IF(B1>17.5/24,(B1-17.5/24),0),0)-D1/24+IF(B1>=8.5/24,IF(B1>=17.5/24,17.5/24,B1),0)-IF(B1>=8.5/24,IF(A1<=8.5/24,8.5/24,A1),0)-C1/24)
お礼
回答ありがとうございます。 解決できそうです。 どうもありがとうございました。
関連するQ&A
- 勤務時間・時間外の計算方法を教えて下さい
A B C D E F 出社時間 退社時間 休憩 勤務時間 時間外 勤務日数 １ 8:00 17:30 1 8:00 0:30 1 ２ 8:00 17:00 0.5 ☆ 0:00 1 ４ 8:00 12:30 0 4:30 ★ ５ 8:00 12:00 0 4:00 0:00 0.5 ６ 13:00 17:00 0 4:00 0:00 0.5 (1)勤務時間を計算する場合 B1-A1-C1かB１-A1-TIME（1,0,0) で計算できることは分かったのですが、休憩時間が30分のときの計算式☆が分かりません。 (2)時間外計算は=IF(D2>TIME(8,0,0),D2-TIME(8,0,0),0)だと勤務時間が８時間の場合にしか当てはまらないので半日勤務の場合の残業計算★はどういう式になるのかも教えて頂きたいです。 出来れば、8時間のときも半日のときも一度に計算したいです。 また、勤務日数を勤務時間 8時間の場合は １ 半日の場合は 0.5 というようにすぐ表示出来るようにならないでしょうか。0.5は赤字で表示出来たらうれしいです。 無理なことを言っているかもしれませんが、お願いします。
- ベストアンサー
- その他（インターネット・Webサービス）
- エクセル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）勤務時間の計算
エクセルで深夜時間をふくむ時間の計算方法を探しています。 ※8時間以上勤務で25％増 ※22：00～29：00の勤務で25％増 ↓セル番号 A1：（開始）19：00 B1：（終了）32：00 C1：（22時までの休憩）1：00 D1：（22時以降の休憩）0：00 E1：（時間内時間）2：00 F1：（25％増）9：00 G1：（50％増）1：00 上記でそれぞれのセルにどのような式を入れればいいのでしょうか？ お分かりになる方がいらっしゃいましたら、 教えていただけないでしょうか？ 宜しくお願いします。
- 締切済み
- その他(業務ソフトウェア)
- エクセル初心者 時間ごとに残業手当が違う場合
下図より Ｃ：出社時間、D：退社時間 E：休憩時間 F：勤務時間 G：残業時間(1)（17:00から15分単位で発生） H：残業時間(2)(1)（18:00から15分単位で発生）があったとします。 現在、 Fの計算は=IF(D6="","",MIN(D6,"17:00")-MAX(C6,"8:45")-"1:00") Gの計算は=IF(D6="","",IF(D6>"17:10"*1,D6-"17:00",0)) Hの計算は=IF(F6="","",IF(D6>="18:15"*1,D6-"18:00",0)) で作成してますが質問は (1)Fの休憩時間１時間引く場合、"1:00"にせずに"F4"を引くとエラーになります (2)Gの残業時間は17時から発生しHは18時から15分おきに発生するのですが式をひとつに まとめて、「Dが17時を超えてたらGに表示、18時を超えていたらHに表示」にしたいです。 ちなみにGは時給\1600 Hは時給\2000となります。 C D E F G H 4 火 8:45 17:45 1 7:15 0:45 0:00 5 水 8:45 12:15 1 2:30 0:00 0:00 6 木 8:45 18:15 1 #VALUE! 1:15 #VALUE! 勉強不足で申し訳ありません。 どうぞよろしくお願い致します。
- 締切済み
- 財務・会計・経理
- 給与計算 一日数回の入出時間ある時の計算式
タイムカードのデータをCVS出力したものを、エクセルの関数設定してあるものに張り付けて、給与計算をすることが目的です。 過去ログなどをみて、なんとかもう少し？というところまでできたのですが、どうしても式がうまくできないところがありますので、どうかご教示お願いいたします。 CVSデータは 1 2013/5/31 9:50 3 2013/5/31 12:46 4 2013/5/31 12:55 3 2013/5/31 14:24 4 2013/5/31 15:58 2 2013/5/31 18:17 1 2013/6/3 9:47 2 2013/6/3 11:25 となっております。 一日に１、2回の休憩がある日もあります。１列目は1＝出勤、2＝退勤、3＝休憩入り、4＝休憩戻りです。 「Kで勤務時間（休憩控除前）を計算」し、Lで休憩時間を１回ごと5分単位でまるめて、後に勤務時間や残業など計算しようと思っています。 できないところは「K行」です。 今の表は （添付あり） A B C D E F G H I J K L 1 2013/5/31 9:25 9:30 1 10:00 10:00 3 2013/5/31 12:46 12:46 4 2013/5/31 12:55 12:55 0:10 3 2013/5/31 14:24 14:24 4 2013/5/31 15:58 15:58 1:35 2 2013/5/31 18:17 18:30 ?? 1 2013/6/3 9:47 10:00 2 11:00 11:00 2 2013/6/3 19:25 19:15 ?? A～C・・張り付け D・・出勤丸め後（15分切り上げ）、 E・・シフト種類、 F・・シフト時間、 G・・DorFの大きい方 H・・退勤丸め後（15分切り捨て）、 I・・休憩入り、 J・・休憩戻り、 K・・勤務時間（休憩控除前） L・・休憩時間（5分丸め後） 要望としましては、K勤務時間＝Aが2の場合、（Hから、Bで同じ日のうち、Aが1のGをひく）Aが2以外は空欄）です。 色々ためして=IF(A10=2,H10-(MATCH(B10,B5:B21,0)+(VLOOKUP("1",A5:A21,7),""))) にたどりつきましたが、「正しくありません」がでます。 どうぞご返答おまちしております！宜しくお願いいたします！ バージョン Excel2010 OSwindous7
- ベストアンサー
- その他MS Office製品
- 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分
- ベストアンサー
- オフィス系ソフト
- Excel - 勤務時間計算
はじめて利用します。よろしくお願いします。 Excel2003を利用し、勤務時間の計算をしようとしています。 まずは以下のセル番地と対象時間をご覧ください。 B1 始業時間(#":"##)1000を入力 C1 就業時間(#":"##)1900を入力 D1 休憩時間(#":"##)100を入力 E1 時間内労働時間 F1 時間外労働時間 E32,F32にはそれぞれ時間内・時間外の月間合計を計算します。 お尋ねしたいのは、E1,F1の時間内・時間外のfx式です。 そしてE32,F32は単純にSUMで計算できるのか、出来ないなら fx式を教えていただきたいと思います。 是非よろしくおねがいします。
- ベストアンサー
- オフィス系ソフト
- Excelで勤務時間の集計をしたいのですが、ウマくいきません。ヘルプお願いします。
A列⇒始業時間、B列⇒終業時間、C列⇒休憩開始時間、D列⇒休憩終了時間を入力し、E列に定時（通常）時間数、F列に定時（深夜）時間数、G列に残業（通常）時間数、H列に残業（深夜）時間数を出すような形にしたいのですが、どうもウマくいきません。例えば、A⇒15：00、B⇒26：00、C⇒21：30、D⇒22：30と入力した時にE⇒6.5、F⇒1.5、G⇒スペース、H⇒2.0となるためにはE～Hまでどの様な式を入れたらよいのでしょうか？
- 締切済み
- Windows XP
- 残業時間月合計（エクセル）を正確に計算できません
エクセル２００７で，勤務時間を管理表を作成していますが，残業時間月合計（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列の計算がマイナスになることが原因で正確に計算できないのでしょうか。 簡単な関数を使って正確に残業時間月合計が計算できる方法を教えてください。 よろしくお願いします。
- ベストアンサー
- オフィス系ソフト
お礼
早速の回答ありがとうございます。 書き忘れてしまったのですが、excel2007を使用しています。 mshr1962様、察していただきましてありがとうございます。 試してみます。ありがとうございました。