ジメジメする梅雨のお悩み、一挙解決! >>

エクセルを使った就業時間の計算で質問です。
始業時間と終業時間の差し引きで拘束時間を計算して、
そこから休息時間を差し引き実働時間を計算する所までは
出来たのですが、実働時間から残業と深夜と深夜残業を自動で求めることが出来ません。


割り振りの条件は・・・
[深夜]当日0時から当日5時迄
[残業]当日5時から当日22時の間で、当日の実働が8時間を越えた部分。
[深夜残業]当日22時から29時の間で、当日の実働が8時間を越えた部分。



上記の条件で、自動計算をする為の式を教えて頂けませんでしょうか?
※画像の様な形で自動計算できれば理想です。

よろしくお願いします。

「エクセルを使った就業時間の計算について」の質問画像

このQ&Aに関連する最新のQ&A

A 回答 (7件)

回答No6です。


深夜時間は次の式になりますね。

=IF(COUNT(D5:F5)<>2,"",IF(D5<"5:00"*1,MIN(F5,"5:00")-MAX(D5,"0:00"),0))

他の時間についての式は前に示した通りです。
    • good
    • 0
この回答へのお礼

回答ありがとうございます。


完璧でした!ありがとうございました!

お礼日時:2011/04/18 08:10

最初のご質問では4時に始業で25時で終わって深夜時間は1時間となっていましたね。

0時から5時までが深夜ならば2時間が正しいのではないでしょうか? 私は1時間の答えとなるように始業の時間を考慮せずに最初の式を立てました。補足では2時の始業にしたら正解は3時間ですとなっていました。4時間が正解ではないのですか?

この回答への補足

回答ありがとうございます。


>0時から5時までが深夜ならば2時間が正しいのではないでしょうか?

最初の質問では、4時始業で25時終業です。振り分けの条件で記載しておりますが、
1.当日の0時から5時が深夜です。
2.当日の5時から22時までの間で、当日の実働が8時間(9時間の拘束)を越えた部分が残業です。
3.当日の22時から29時までの間で、当日の実働が8時間(9時間の拘束)を超えた部分が深夜残業です。

ですので、
4時始業の場合は、5時までの1時間が深夜です。
5時以降22時迄で、始業から9時間の拘束時間を越えた部分。8時間が残業です。
そして、そこから25時迄の3時間が深夜残業です。

ですので、始業が2時の場合は、5時迄の3時間が深夜となります。
深夜残業は、当時の残業が深夜に掛かる場合が条件ですので、深夜とは違う意味になります。


ややこしいのですが、お力頂けたら助かります。
よろしくお願い致します。

補足日時:2011/04/15 08:39
    • good
    • 0

回答No4です。

深夜時間について補足の中で次のような文章がありますね。
始業を2:00に変えてみたのですが、深夜が1:00と表示されました。
3:00と計算されると正しいのですが・・・。
2時から25時までの勤務で0時からの深夜時間は1時間と計算する式を使っていました。そうではなく答えは3時間ですとのことですが始業開始が深夜に始まるときの時間を考慮しておりませんでした。その時間を考慮するとなれば次のような式になります。

=IF(COUNT(D5:F5)<>2,"",MIN(F5,"29:00")-MAX(D5,"24:00")+MIN(F5,"5:00")-MAX(D5,"0:00"))
    • good
    • 0

H5セルには次の式を入力して下方にオートフィルドラッグします。



=IF(COUNT(D5:F5)<>2,"",F5-D5)

J5セルには次の式を入力して下方にオートフィルドラッグします。

=IF(COUNT(D5:F5)<>2,"",H5-"1:00")

L5セルには次の式を入力して下方にオートフィルドラッグします。

=IF(COUNT(D5:F5)<>2,"",IF(MIN(F5,"22:00")-MAX(D5,"5:00")-"1:00">"8:00"*1,MIN(F5,"22:00")-MAX(D5,"5:00")-"1:00"-"8:00",""))

N5セルには次の式を入力して下方にオートフィルドラッグします。

=IF(COUNT(D5:F5)<>2,"",MIN(F5,"29:00")-MAX(D5,"24:00"))

P5セルには次の式を入力して下方にオートフィルドラッグします。

=IF(COUNT(D5:F5)<>2,"",IF(J5>"8:00"*1,MIN(F5,"29:00")-MAX(D5,"22:00"),0))

いずれのセルも表示形式で時刻にします。

この回答への補足

回答ありがとうございます!


早速試したみたのですが、始業時間と終業時間を変えたところ、深夜の計算が正しく計算されなく
なってしましました。


始業を2:00に変えてみたのですが、深夜が1:00と表示されました。
3:00と計算されると正しいのですが・・・。


もし、お分かりでしたら教えて頂けますでしょうか?

よろしくお願いします。

補足日時:2011/04/14 13:35
    • good
    • 0

言い忘れましたが、計算するに当たって、「実働が8時間を超えた」を「拘束が9時間を超えた」に置き換えています。

    • good
    • 0

L5に


=MAX(0,MIN(VALUE("22:00"),F5)-MAX(VALUE("5:00"),D5)-VALUE("9:00"))
N5に
=MAX(0,VALUE("5:00")-D5)
P5に
=MAX(0,MIN(VALUE("29:00"),F5)-MAX(VALUE("22:00"),D5+VALUE("9:00")))

でどうでしょう?

この回答への補足

回答ありがとうございます。


他のセルでも時間を変えて試してみました。キチンと計算されて出てきました!
助かりました。


それと、合わせてお聞きしたいのですが、始業と終業を入力しない場合でも
残業8:00 深夜5:00 深夜残業7:00 が表示されてしまいます。
時間を未入力の場合は、残業等の時間が表示されないようには出来るのでしょうか?


ご存知でしたら、教えて下さい。
宜しくお願いします。

補足日時:2011/04/14 14:00
    • good
    • 0

添付画像の、残業8時間、深夜1時間、深夜残業3時間、というのは、どのように計算したのでしょうか?

この回答への補足

表示されている時間は手入力しただけです。計算はしていません。

補足日時:2011/04/14 14:12
    • good
    • 0

このQ&Aに関連する人気のQ&A

お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!

このQ&Aを見た人はこんなQ&Aも見ています

このQ&Aを見た人が検索しているワード

このQ&Aと関連する良く見られている質問

QExcelで、おそらくCOUNTIFの関数の質問だと思うのですが教えてください。

今時間別の来場者数をCOUNTできるような表を作っているのですがG列に来場順に時間を書く欄があります。これを時間帯別に来場者の集計をとりたいのですが、9時00分~11時59分までの総来場者が何人、12時00分~14時59分までの総来場者が何人と集計できるようにしたいんです。宜しくお願いします。

Aベストアンサー

こんにちは。maruru01です。

時間範囲は、COUNTIF2つの引き算で出来ます。
例えば、9時00分~11時59分は、つまり
9:00以上12:00未満ですから、
「9:00以上の数-12:00以上の数」
で出ます。
これをCOUNTIF関数で表すと、

=COUNTIF(G:G,">=9:00")-COUNTIF(G:G,">=12:00")

となります。
で、24:00~9:00は、No.3の方の言う通り、
0:00~9:00で考えて、

=COUNTIF(G:G,">=0:00")-COUNTIF(G:G,">=9:00")

となります。
21:00~24:00の場合は、そのまま、

=COUNTIF(G:G,">=21:00")-COUNTIF(G:G,">=24:00")

とします。

QIF文に時間(何時から何時まで)の条件文を付けたい

VBAの知識が無いので本を脇に置きながら作業をしております。

図のような画面を作成しようとしておりますが、2つ質問があります。
(1)図右上のようにパソコンに設定されている時間を画面上に
 表示する方法を教えて下さい
(2)図右上に表示された時間を基にIF文の処理を行ないたい
 例えば、現在の時刻が13:00だったら、
 「12:00~14:00」、「14:00~16:00」、「16:00~18:00」
 の処理を行なう  としたいのですが、
 
 分からないのが、IF文で
 『現在時刻が12:00~14:00までの間だったら、処理Aを行なう』の
 命令文が分かりません。
 
 どのように作成すれば良いのか、お教え願います。

Aベストアンサー

通りがかりです。
>分からないのが、IF文で
> 『現在時刻が12:00~14:00までの間だったら、処理Aを行なう』の
> 命令文が分かりません。
この場合は
 Select Caseで分岐したほうが便利でしょう
Select Case Range("C1").Value
Case Is < TimeSerial(12, 0, 0)
MsgBox "処理A"
Case Is < TimeSerial(14, 0, 0)
MsgBox "処理b"
Case Is < TimeSerial(16, 0, 0)
MsgBox "処理c"
Case Is < TimeSerial(18, 0, 0)
MsgBox "処理d"
End Select
といった具合で考えてみてください。

Q残業時間の計算をするにはどのような関数あるいは計算式を設定すればいいの

残業時間の計算をするにはどのような関数あるいは計算式を設定すればいいのですか?
私、エクセルについてはまったくの初心者です。よろしくお願いします。

(例)
       A      B
 
1行目 出勤時間  9:00

2行目 退社時間  19:30

3行目 残業時間  2:00 ←ここの計算式を教えていただきたいのです。

             ※ ただし、定時の終了時間は17:30で、拘束時間は休憩時間を含めて
               8:30とした場合でお願いします。

Aベストアンサー

勤務時間=退社時間-出勤時間
残業時間=勤務時間-8:30
で数式を組み立てると

残業時間 B3=IF(OR(B1>0,B2>B1),MAX(B2-B1-"8:30",0),"")

時間を30分単位で切り捨てにする場合
残業時間 B3=IF(OR(B1>0,B2>B1),FLOOR(MAX(B2-B1-"8:30",0),"0:30"*1),"")

Qエクセルでの時間計算(2時間30分→2.5と表示するには?)

開始時刻15:00、終了時刻17:30の場合の所要時間の計算ですが、15分=0.25で表示したいのです。この計算式を教えて下さい。
また、15分=0.25の場合、10分はいくつになるのですか?この計算方法も教えて下さい。
おバカでお恥ずかしいのですが、宜しくお願いします。

Aベストアンサー

A1セルに「15:00」と、B1セルに「17:30」と、C1セルに「=(B1-A1)*24」と入れてみて下さい。C1セルに「2.5」と表示される筈です。

エクセルでは、日付時刻は「1日間なら1、2日間なら2、12時間なら0.5」として記憶しています。つまり1時間を示す値は「1/24」として記憶しています。

そこで「日付時刻の差」を24倍すると「1時間を1にした数値」になります。

15分を0.25と決めた時、15分の場合は
15/60
=1/4
=0.25
と計算するのですから、同様にして10分の場合は
10/60
=1/6
=0.166666666…
と計算します。

Qエクセルで勤怠管理表で休憩時間を計算させるには

エクセルで、社員の出勤時間の計算をしたいのですが、
休憩時間の控除の計算式がよくわからずに困っています。

パートさんが多いので、労働時間により休憩時間が異なり、下記のようにしています。
4時間30分未満=0分
4時間30分以上=30分
6時間以上=45分
8時間以上=60分

現在使用しているデータの時間表示は、出社「9:00」退社「17:00」延べ時間「8:00」というような、60進法の時分表示です。
これを、10進法表示にはしない方法で計算したいです。

現在の表では、
Aセル=出社時刻、Bセル=退社時刻、Cセル=述べ就労時間、Dセル=休憩時間、Eセル=勤務時間(←述べ就労時間-休憩時間)
にしているのですが、

Dセルの休憩時間が自動計算設定の仕方がわからず、述べ就労時間を見ながら手入力しています。(かなり手間なのと、間違いのもとなので)
これを自動計算できるようにしたいのですが・・

教えて頂けたら大変助かります。
宜しくお願いします。

Aベストアンサー

IF文で
=IF(C2<"4:30"*1,0,IF(C2<"6:00"*1,"0:30"*1,IF(C2<"8:00"*1,"0:45"*1,"1:00"*1)))

Qエクセル2010 深夜残業時間の計算

出社・退社の時間から、残業、深夜残業など、
残業時間を算出する数式を作成していますがどうにもうまく出来ません。

残業となる時間の切り分けは、平日は以下のようになっています。、
 基本時間→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://oshiete.goo.ne.jp/qa/8147465.html
よろしくお願いいたします。

出社・退社の時間から、残業、深夜残業など、
残業時間を算出する数式を作成していますがどうにもうまく出来ません。

残業となる時間の切り分けは、平日は以下のようになっています。、
 基本時間→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欄に深夜残業としています。
現状...続きを読む

Aベストアンサー

深夜部分の残業時間を計算するなら、以下のような数式で早朝(5:00以前)部分と夜間(22:00以降)部分の深夜残業を足し算するのが簡単です。
(退出時間が翌日になる場合は25:00のような形式で入力した場合)。

=5/24-MIN(D5,5/24)+MAX(E5,22/24)-22/24

退出時間を「25:00」ではなく通常の「1:00」のように入力する場合は、以下の式で計算することができます。

=5/24-MIN(D5,5/24)+MAX((E5<D5)+E5,22/24)-22/24

ところで、深夜部分がメインの勤務がある場合、休憩時間などは考慮しなくてもよいのでしょうか?

Qエクセルの深夜時間計算について

エクセルを使って勤務時間表を作っていますが
22:00から29:00までが深夜時間になっていて その計算方法がわかりません
例1出勤-退勤-残業-深夜
  10:00-25:00-7,0-3,0
例2 3:30-19:30-8,5-1,5
深夜時間計算のみ教えていただきたくお願い致します。

Aベストアンサー

こんにちは。maruru01です。

A2に出勤時間、B2に退勤時間を入力する場合、深夜時間のセルに、

="5:00"-MIN("5:00",A2)+MIN("29:00",B2)-MIN(MAX("22:00",A2),B2)

と入力します。
質問の例だと、それぞれ「3:00」「1:30」と表示されます。(表示形式は時刻にします。)
時間数で表示する場合は、数式を24倍して、表示形式を標準にして下さい。

Qエクセル 残業時間の計算

いつもお世話になります。

エクセルで勤怠表を作っています。

出勤時間・・・D8
退勤時間・・・D9  に書く事にした時、

残業時間をD13に表示させたいのですが上手くいきません。

関数を教えて頂けると助かります。

定時時間:8:00~17:00
残業時間:17:01~22:00、5:30~8:00
深夜残業時間:22:01~5:30

尚、時間は30分単位で切り下げです。


宜しくお願い致します。

Aベストアンサー

補足回答です。

深夜時間は以下のような数式にするのが正しいと思います。

すなわち、開始時間が6:30より前の(5:30分までの)深夜時間と、6:30以降(22:00以降)の深夜時間を合計します。

=(D8<"5:30"*1)*(MIN(D9,"5:30"*1)-D8)+(D8>"5:30"*1)*MAX(D9-"22:00",0)

前半の式はすでに提示した式で、開始時間が5:30より前の場合は、終了時間と5:30の小さい方の値から開始時間を引いた値を返し、後半の式は終了時間から22:00を引いたものがプラスならその値、マイナスなら0を変える数式を加算したものです。

なお前回の回答で「*1」などの処理に対する説明が不足していたかもしれないので補足すると、文字列の時刻でも引き算や掛け算などの演算をすると、「*1」などの処理をしなくてもクセルが気を利かせて時刻(=シリアル値)と判断するため、数式内で演算しているような部分では省略することができるわけです。

QExcel関数での勤務時間並びに残業、深夜残業時間の計算方法

[A1]出社時間 9:00
[B1]退社時間 23:45
の値から
かなりややこしい条件があるのですが。。。
昼休憩が1時間
勤務時間が8時間で15分の休憩(9:00出社の場合18:00~18:15)
勤務時間が10時間で15分の休憩(9:00出社の場合20:15~20:30)
休憩時間は結果から引いている
出社時間には変動があります。
出社時間にかかわらず夜間作業は22:00以降となってます(退社時間が22:15に0.25)

これらの条件で
[C1]総稼動時間 13.25
[D1]時間外 5.25
[E1]夜間作業 1.75
を出せる関数を教えていただきたいのです。出来れば出社、退社に時間が入ってない場合はそれぞれ空白になるようにしていただきたいです。
ややこしいことを質問していますが、よろしくお願いします。

Aベストアンサー

●「勤務時間が8時間で」を、「昼休憩を除く勤務時間が 8 時間に達した時点で」という
 意味に捉えました。
●「総稼動時間」にも休憩は含まないと解釈しました。
●「総稼動時間」のうち 8 時間を超えた部分を「時間外」とするものと解釈しました。
●「夜間作業」も「総稼動時間」の内数(うちすう)であると解釈しました。

添付図に記入している数式は、下のとおりです。

なお ROUND 関数を適用しているのは、「オートフィルや数式で作製された時刻データを扱う場合などに小数点第 14 位、15 位あたりに発生するケースがある計算誤差」を補正するためです。小数点以下に 7 桁を残せば、0:01 単位で時刻を記録して日給 100,000 円であっても、誤差 1 円未満になると考えられます。

C3 =(b3-a3)/"1:00"
D3 12:00 など
E3 =min($B3,d3+"1:00")
F3 =($C3>1+8)*($A3+"1:00"+"8:00")
G3 =min($B3,($C3>1+8)*($A3+"1:00"+"8:15"))
H3 =($C3>1+0.25+10)*($A3+"1:00"+"0:15"+"10:00")
I3 =min($B3,($C3>1+0.25+10)*($A3+"1:00"+"0:15"+"10:15"))
J3 =round(c3-sum(e3-d3,g3-f3,i3-h3)/"1:00",7)
K3 =max(0,j3-8)
L3 =round(max(0,b3-"22:00")/"1:00",7)

●「24:00 以後の時刻を表示する可能性のある列」のセルの書式は、「h:mm」ではなく、
 「[h]:mm」を設定してください。
 「セルの書式設定 > 表示形式タブ > ユーザー定義 > 種類ボックス」に書式記号を
 入力します。
●退社が翌日になったときは、午前 1 時であれば 25:00 というふうに記入してください。
●ゼロであるセル(「0.00」、「0:00」)を空白(のような見かけ)にするには、
 「ゼロ以下の値のみを非表示にするセルの書式」として、「0.00;;」、「h:mm;;」、
 などをセル範囲に設定しておきます。
 操作の方法は、「退社時間」の書式と同じです。
● J 列などに数式を記入した瞬間に、そのセルの書式が勝手に時刻になってしまう
 ことがありますが、気にせず「0.00;;」などを設定すれば、正常な値が表示されます。

●「勤務時間が8時間で」を、「昼休憩を除く勤務時間が 8 時間に達した時点で」という
 意味に捉えました。
●「総稼動時間」にも休憩は含まないと解釈しました。
●「総稼動時間」のうち 8 時間を超えた部分を「時間外」とするものと解釈しました。
●「夜間作業」も「総稼動時間」の内数(うちすう)であると解釈しました。

添付図に記入している数式は、下のとおりです。

なお ROUND 関数を適用しているのは、「オートフィルや数式で作製された時刻データを扱う場合などに小数点第 14 位、15 位あたりに...続きを読む

QIF関数の論理式に時刻を使いたいが・・・

 例えば宅配便の配達希望時間を羅列した表があって、「20:00以降は“夜間配達”」と戻したいとき、IF関数を使用すると思います。

 解説書によると、IFの式の条件には時刻の表示形式は使えないので「”」で囲み、「1」をかける。これで時刻表示がシリアル値になるため設定が早くなる。

とあるのですが、これだけでは意味がわからず質問させていただきました。下記の2点ほどご教示いただければ助かります。

(1)どうしてIFのの条件に時刻の表示形式は使えないのか?他の関数には使えるような気がするのですが。

(2)「”」で囲むのはなんとなくわかるとして、なぜ「1」をかけるのか。

シリアル値の知識(時刻の場合24時間を1で管理する)は一応あります。

 

Aベストアンサー

(1)IFの関数というより、数式に時刻を使用することができないのではないでしょうか?
これは想像するに、時刻表示には「:」(コロン)を使いますが関数では「:」は範囲を指定する時に使用しますよね(例:A1:A10)。ですから数式に時刻の「:」を認めると範囲指定なのか、時刻なのか、収拾がつかなくなってしまいそうです。
(2)””で囲んで1を掛ける、についてですが。
””で囲むと文字列として認識されますよね。しかし””の中が本来数値ならば1を掛けることによって、文字列ではなく数値として認識されることになります。そのために1を掛けて数式として成り立つようにするのです。

想像の部分もあり間違ってたらすいません。


このQ&Aを見た人がよく見るQ&A

このカテゴリの人気Q&Aランキング

おすすめ情報