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


割り振りの条件は・・・
[深夜]当日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"勤務は8時間"B

エクセルでの勤務表時間計算についてですが、例えば"A"勤務は8時間"B"勤務は6時間とします。
一ヶ月を通してA・B勤務が10日ずつあったとして勤務表に勤務を入力していくと月のトータル勤務時間数が自動的に出る関数はないでしょうか?別のシートにあらかじめ時間を登録しておいてそこから引き出すような格好になるのでしょうか?わかりにくい質問ですみませんがよろしくお願い致します。

Aベストアンサー

一例です。
仮に入力を文字列「A」、「B」とすれば、
=COUNTIF(一ヶ月範囲,"A")*8+COUNTIF(一ヶ月範囲,"B")*6
で時間数を計算できますが如何でしょうか。

QIFRSの読み方について

IFRSの読み方について教えてください。

アイエフアールエス、アイファス、アイファース、イファースなど読み方があるようですが、日本では標準的な読み方はありますか?また、グローバルで共通した読み方はありますか?

全社で共通した読み方で統一したいと考えております。

Aベストアンサー

IFRSの読み方は、現時点の日本では様々です。
各種セミナーに行きましたが、講師をされる方によってまちまちです。

ひとりの講師の方の説明では、IFRSがすでに導入されているヨーロッパ(EU)では、主にアイエフアールエスと読んでいるようです。

http://ifrs-yomikata.cocolog-nifty.com/blog/

ご参考に。

参考URL:http://ifrs-yomikata.cocolog-nifty.com/blog/

Qエクセル2007 月/日 時/分の書式で日付、時間を入力 & 経過時間を計算

いつもお世話になっております。
よろしくお願いいたします。

エクセル2007を使用して以下は可能でしょうか?

・セルA1 と セルB1に MM/DD HH/MM(月/日 時/分) のフォーマットで日付&時間を入れB1からA1を引き、経過時間を割り出す。

セルの書式設定で、年 を省いて MM/DD HH/MM とする書式が見つかりませんがこれは不可なのでしょうか。
もし、上記不可でしたら、代替え案とその具体的な方法(式など)をおしえていただけますでしょうか?

よろしくお願いいたします。

Aベストアンサー

》 エクセル2007を使用して以下は可能でしょうか?

私の Excel 2002 で可能なので、大丈夫だと推測します。


》MM/DD HH/MM とする書式が見つかりませんが…

(Excel 2002 の場合ですが)それは“日付”の場所にないということでしょう?“ユーザー定義”の場所で貴方自身で作れば好い。

Q麗の読み方

「麗」という読み方はレイ、ウララ意外読み方がありますか?
名前付けなので、あて字でもいいです。
役所に認められればどんな読み方でもいいのですが・・・

Aベストアンサー

 http://dictionary.goo.ne.jp/srch/jn/%E3%81%86%E3%82%89%E3%82%89/m0u/
では“うら”ら、うら”らか
 という読みがありますね。
 これは85になる姉の名前が使っています。最初はカナでしたが、成人して漢字に改名したんです。
 小さい時は書くのがたいへんですね。
 私も苗字もちょいと画数が多いし、名前も“藏”って字はとても書き難くかった思い出があります。

Qエクセルの時間計算でコロン(:)を省略した定義を設定し、時間差分の計算

エクセルの時間計算でコロン(:)を省略した定義を設定し、時間差分の計算をする方法を教えて下さい。
例えば・・・
入力文字200→(2:00)となる様に設定して
さらに以下の様に時間差分の計算をする方法です。
17:00-16:15=0:45となる様に計算したいです。
コロン省略設定をしなければ簡単なのですが・・・・設定すると差分の計算がおかしくなります。

Aベストアンサー

C2に
=TEXT(B2,"0!:00")-TEXT(A2,"0!:00")
として,計算結果を無駄に省略表記する必要はないので,
C列のセルにはセルを右クリックして書式設定の表示形式のユーザー定義で
[h]:mm;;;
 後ろはセミコロンが3個なので間違えないこと
と設定しておけばOKです。


#掲示されたサンプルはなんだか時間が逆ってますが,Aに開始,Bに終了の前提です。

Q記号∀,∃の読み方(発声)を教えてください

記号∀,∃の読み方(発声)を教えてください
出来れば解析学と論理学とにおける読み方を
一般的な読み方がなければ個人的な読み方でもいいです

Aベストアンサー

大学では数学をかじっていた者である。
一般論はNo.1に詳しいので、ここでは個人的なことを述べさせて頂く。

大学の講義でそう聞いたからだと思うが、私はどのような場合でも大概
「全ての」「或る(ある)」
と読んでいる。
また、Wikipediaの記事にはないが、こんな話をする相手はガンダムの話も嫌がらない人が多いので、
「ターンエー(turn A)」「ターンイー(turn E)」
ということもあろうかと思う。

QEXCELで就業時間の計算方法について

EXCELで就業時間の計算方法について
各セルに「8:30」~「20:00」と入力するとこの日の就業時間が「10.5」と算出する方法を教えてください。

Aベストアンサー

例えばA1セルから下方に出社時間がありB1セルから下方に退社時間があるとして、勤務時間が8時間以上では1時間の休憩時間があるとしたら次の式をC1セルに入力して下方にオートフィルドラッグします。

=IF(COUNT(A1,B1)<>2,"",IF(B1-A1>="8:00"*1,(B1-A1-"1:00")*24,(B1-A1)*24))

QFAQの読み方は?

WindowsFAQ-Windows処方箋のページはここでもよく紹介されています。そこで疑問に思ったのがFAQの読み方です。
手元には2種類のPC用語辞典しかないのですが、2種類が違う読み方をしています。「ファーク」と読む場合とあとは恥ずかしい読み方をする場合とです。正しい読み方はどちらなんでしょうか?

後者だとヤだな。

Aベストアンサー

Cid さん、こんばんは~☆♪

ASCIIのデジタル用語辞典では、『ファーク』・『エフ・エー・キュー 』と
なっておりますね。
私は、後者の『エフ・エー・キュー 』と読んでおります。

> とあとは恥ずかしい読み方をする場合とです。

『ファック』とは、読まないですよ。 ご安心を。。。

http://yougo.ascii24.com/gh/49/004900.html


ではでは☆~☆~☆

参考URL:http://yougo.ascii24.com/gh/49/004900.html

Qエクセルの「時間」を計算に使う方法

 エクセルで、
たとえば「2:34」と入力すると、時間として認識されます。
この値を、計算に用いるにはどうしたらいいのでしょうか?
この場合ですと、2+34÷60、または60×2+34といった値として認識させ、
時間率を求めること等に利用したいと思っています。
方法をご存知の方、よろしくお願いします。

Aベストアンサー

「2:34」と入力して時間として認識したのなら、中身は0.106944..
という数値で「h:mm」という書式が適用されています。つまり一日
が1となるわけです。

ということは、時間単位の十進数にしたければ24を掛けるし、分単
位の十進数にしたければ24*60で1440を掛ければオッケー。


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

おすすめ情報