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

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

「エクセル2010 深夜残業時間の計算」の質問画像

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

A 回答 (5件)

深夜部分の残業時間を計算するなら、以下のような数式で早朝(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

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

この回答への補足

昼休憩のみで後は考慮しません。
ありがとうございます。

補足日時:2013/12/22 17:09
    • good
    • 8
この回答へのお礼

回答ありがとうございます。
1,2の方のも良かったのですが、MackyNo1さんのものが、iPadやiphoneでエラー無しで見えましたのでベストアンサーに選ばせていただきます。
御陰様で助かりました。

お礼日時:2013/12/22 17:21

>昼休憩のみで後は考慮しません。



このケースだと、午前中のみ、または午後のみの勤務の時は(出社時間が1時以降または退社時間が12時以前の場合)、休憩時間は不要になるのではないでしょうか?(単純に休憩時間を引き算する数式だと勤務時間の計算に誤差が出るのでは?)
    • good
    • 1

私も、以前にエクセルで入力された作業時間を計算する仕事をしました。


それまで総務の方が行っていたのと同様な計算方法をする必要があったため、エクセルに式を入れるだけではうまくいかず、VBAのプログラムを作成し、ボタンを押すとプログラムが呼び出されて計算するようにしました。

休憩時間とか、代休とかいろいろと考慮する必要があるので、それらにすべて対応するには、やはりプログラムを記述する必要があると思います。
    • good
    • 4

>私の式(=SUM(E5-D5)-$B$1-$C$1-G5)から見れば、1だと思ったんですが、違うのですね。


その式のSUM関数は無意味で、単純にE5-E4と同等です。
尚、総勤務時間から規定勤務時間と休憩を差し引いただけなら深夜勤務が時間外の内数ですが、G5(深夜)を差し引いていますので2項の考え方になります。

>深夜残業の式をお願いいたします。
早出="8:30"-MAX("5:00",出勤時刻) から類推できませんか?
深夜に掛かる早出="5:00"-MIN("5:00",出勤時刻)
残業=MIN("22:00",退出時刻)-"17:15" から類推すれば
深夜に掛かる残業=MAX("22:00",退出時刻)-"22:00") となります。

MAX関数は引数(括弧内の値)のうち1番大きい値を返します。
MIN関数は引数の内1番小さい値を返します。
MAX関数、MIN関数の代わりにIF関数を使う方法もありますがIF関数より分かり易いように思います。
    • good
    • 0
この回答へのお礼

何回も回答ありがとうございます。
bunijiさんの回答も良かったのですが、他者様のものがipad等でスムーズに見えました。
でも、御陰様でいい勉強になりました。

お礼日時:2013/12/22 17:25

>残業→5:00~8:30 と17:15~22:00


>深夜残業→22:00~翌朝5:00
時間外(早出・残業)と深夜勤務の計算で考え方が2通りあります。

1.深夜勤務を時間外の内数で考える計算方法
2.通常の時間外と深夜勤務となる時間外を区別して計算する方法

あなたの質問は2の方法と解釈します。

>過去欄を参考にしようにも理解できなく困っています。
時間外は早出と残業に分けて計算します。

早出="8:30"-MAX("5:00",出勤時刻)
MAX関数で5:00前に出社したときは5:00~8:30(3時間30分)までが早出時間数となり、5:00以降に出社したときは出社時刻~8:30までの時間数となります。

残業=MIN("22:00",退出時刻)-"17:15"
MIN関数で22:00以降の退出のとき17:15~22:00(4時間45分)までが残業時間数となり、22:00までに退出したときは17:15~退出時刻までの時間数となります。

深夜勤務も時間外と同様に計算すれば目的に合うはずです。
他人が作成した計算式を理解もせずに利用すると思わぬ落とし穴に落ちますので注意してください。

この回答への補足

お返事ありがとうございます。

1.深夜勤務を時間外の内数で考える計算方法
2.通常の時間外と深夜勤務となる時間外を区別して計算する方法
私の式(=SUM(E5-D5)-$B$1-$C$1-G5)から見れば、1だと思ったんですが、違うのですね。

人様(高度な)の式を理解できる知識がありません。
回答いただいた理屈は分かるのですが、「早出="8:30"-MAX("5:00",出勤時刻)」「残業=MIN("22:00",退出時刻)-"17:15"」このような式がたてられないのです。
できましたら、深夜残業の式をお願いいたします。

補足日時:2013/12/21 20:13
    • good
    • 2

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

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

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

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

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

Qエクセル出退勤表で時間外と深夜時間外の関数

エクセルで出退勤表を作りたいと思っています。
所定労働時間は10:00~18:30で休憩1時間入り、7:30です。
18:30~22:00までは時間外労働時間、22:00~翌5:00までは深夜時間外です。例えば出勤時間10:00、退勤時間23:00とすると、時間外は3:30で深夜時間外は1:00です。
セルの縦列には
A:日付(例3/11で表示)
B:曜日
C:出勤時間(例10:00で表示)
D:退勤時間(例23:30で表示)
E:所定労働時間(7:30で固定で休憩の1時間を含んでいない)
F:時間外労働時間(例3:00で表示)
G:深夜時間外(例2:30で表示)

Q1:カレンダーの暦通り、曜日をBセルに自動表示させる方法はありますか?
Q2:Fセルの18:30~22:00までの時間外労働時間を計算する関数を教えて下さい。Q3:Gセルの22:00~翌5:00までの深夜残業時間を計算する関数を教えて下さい。
Q4:出勤時間と退勤時間を入力すれば時間外と深夜時間外が表示されるようにしたいのですが可能ですか?
よろしくお願いします。

エクセルで出退勤表を作りたいと思っています。
所定労働時間は10:00~18:30で休憩1時間入り、7:30です。
18:30~22:00までは時間外労働時間、22:00~翌5:00までは深夜時間外です。例えば出勤時間10:00、退勤時間23:00とすると、時間外は3:30で深夜時間外は1:00です。
セルの縦列には
A:日付(例3/11で表示)
B:曜日
C:出勤時間(例10:00で表示)
D:退勤時間(例23:30で表示)
E:所定労働時間(7:30で固定で休憩の1時間を含んでいない)
F:時間外労働時間(例3:00で表示)
G:深夜時間外(例2:30で...続きを読む

Aベストアンサー

>Q1:カレンダーの暦通り、曜日をBセルに自動表示させる方法はありますか?
B2=TEXT(A2,"aaa")

>Q2:Fセルの18:30~22:00までの時間外労働時間を計算する関数を教えて下さい。
F2=IF(OR(C2="",D2=""),"",MAX(MIN(D2+(D2<C2),22/24)-C2-17/48,0))

>Q3:Gセルの22:00~翌5:00までの深夜残業時間を計算する関数を教えて下さい。
G2=IF(OR(C2="",D2=""),"",MAX(D2+(D2<C2),22/24)-22/24)

※D2+(D2<C2) C2=10:00で、D2=5:00の場合にD2=27:00とします。
※22/24="22:00"=22時のこと
※17/48=8.5/24="08:30"=8時間半(休憩時間を含む所定労働時間)のこと

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エクセル 深夜勤務時間の算出方法

こんにちは。

今、会社でエクセルを使った勤務表を作成しています。
出社時間、退社時間をそれぞれセルに入力すると、深夜勤務時間のセルに夜22:00から朝5:00の間に勤務した時間を抽出して表示したいと思っています。

例として、出社時間を「20:00」、退社時間を「3:00」と入力した場合、深夜勤務時間に「5:00」という風に計算して表示させたいのです。

深夜勤務時間にはどういった計算式をいれればいいでしょうか?
よろしくお願い致します。

Aベストアンサー

昔私も、タイムカードの集計と手書きの集計を合わせる為に
エクセルで表を作った事がありますが・・・・
タイムレコーダーが特殊な計算方法を取っていた事と
勤務時間帯が昼夜問わずだった事、深夜勤務時間帯は手当がつくので分ける事、
昼休みは基本的に1時間引く事になっているが、昼休みを取らないで
働いたりする事があった事などなど・・・諸事情により
もっともっと面倒臭い事になりました(苦笑)。

IF関数による場合分けが必要です。
(1) 出社時間、退社時間ともに深夜にかからない場合
(2) 出社時間が深夜にかからないが、退社時間が深夜にかかる場合
(3) 出社時間が深夜にかからないが、退社時間が深夜を超えて(5時以降)いる場合
(4) 出社時間、退社時間ともに深夜時間帯の場合
(5) 出社時間が深夜で、退社時間が深夜を超えて(5時以降22時以前)いる場合
(6) 出社時間が深夜で、退社時間が昼間時間を超えて(22時以降)いる場合

つまり、深夜勤務時間は
(1)なら【0】であり、
(2)なら退社が23:59までは【退-出】、0:00以降なら【退+24:00-出】、
(3)なら【7】、
(4)なら出社が23:59までの場合、
退社が23:59までは【退-出】、0:00以降なら【退+24:00-出】、
出社が0:00以降の場合は【退-出】、
(5)なら出社が23:59までの場合、【29:00-出】、
出社が0:00以降の場合は【5:00-出】
(6)なら合算
といった感じです。どうです、面倒臭いでしょう・・・。
(というか、場合分けが多過ぎてIFの7段の限界を超えているような・・・。)
私は、昼間の勤務時間をIF関数6段重ねで算出し、深夜の時間帯は
総勤務時間から昼間の時間を引くような形にしてました。

ですから、やはり他の方がおっしゃるように27:00と入力するのが
一番面倒がないように思いますよ。
入力時に3:00を27:00と計算するのが面倒臭いというのであれば、
出社時間>退社時間なら退社時間に24時間足すセルを用意して
入力セル(「3:00」と入力する)と
退社時間の結果表示セル(「27:00」と表示させる)を分け、
退社時間は結果表示セルから引っ張るようにすれば良いように思います。

昔私も、タイムカードの集計と手書きの集計を合わせる為に
エクセルで表を作った事がありますが・・・・
タイムレコーダーが特殊な計算方法を取っていた事と
勤務時間帯が昼夜問わずだった事、深夜勤務時間帯は手当がつくので分ける事、
昼休みは基本的に1時間引く事になっているが、昼休みを取らないで
働いたりする事があった事などなど・・・諸事情により
もっともっと面倒臭い事になりました(苦笑)。

IF関数による場合分けが必要です。
(1) 出社時間、退社時間ともに深夜にかからない場合
(2) 出...続きを読む

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エクセルで勤怠管理表で休憩時間を計算させるには

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

パートさんが多いので、労働時間により休憩時間が異なり、下記のようにしています。
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)))

QExcel2003 深夜残業時間の計算

少し長い質問になりますが、よろしくお願いいたします。
出社・退社の時間から、早朝残業や夕方残業、深夜残業など、
残業時間を算出する数式を作成していますがどうにもうまく出来ません。

残業となる時間の切り分けは、平日は以下のようになっています。、
 早朝残業→5:00~8:30
 基本時間→8:30~17:00
 通常残業→17:00~22:00
 深夜残業→22:00~翌朝5:00

例えば平日、朝6:00出社→23:00退社であれば、
早朝残業が2.5、通常残業が5.0、深夜残業が1.0となります。

深夜残業を出す数式を、以下のように作成してみたのですが、うまく算出できません。
C列に出社、D列に退社、入力はコロンを挟んだ時間の形式で、表示形式は[h]:mmです。
A列に日付、B列に祝日のフラグ、数式はG列に入れています。

=IF(C10="",0,IF(OR(TEXT(A10,"aaa")="日",TEXT(A10,"aaa")="土",B10="祝"),0,IF(AND(D10>=VALUE("22:00"),C10<=VALUE("5:00")),ROUND((MIN("29:00",D10)-"22:00")*24,2),IF(AND(C10>=VALUE("0:00"),C10<VALUE("5:00")),ROUND((MIN("5:00",D10)-C10)*24,2),0))))

ですが、出社13:00→退社27:00のところで、深夜残業が0.0になってしまい、
数式の IF(AND(D10>=VALUE("22:00"),C10<=VALUE("5:00"))をOR式に変更しました。

=IF(C10="",0,IF(OR(TEXT(A10,"aaa")="日",TEXT(A10,"aaa")="土",B10="祝"),0,IF(OR(D10>=VALUE("22:00"),C10<=VALUE("5:00")),ROUND((MIN("29:00",D10)-"22:00")*24,2),IF(AND(C10>=VALUE("0:00"),C10<VALUE("5:00")),ROUND((MIN("5:00",D10)-C10)*24,2),0))))

すると、出社0:00→退社18:30のところで、深夜残業が0.0になってしまいました。

両方をカバーできる数式はどのようになるのか、色々試しても出来ません。
お分かりになる方、いらっしゃいましたらお知恵を貸してください。
説明がうまく出来ていないかもしれません、分かりづらい文章で申し訳ありませんが
どうぞよろしくお願いいたします。

少し長い質問になりますが、よろしくお願いいたします。
出社・退社の時間から、早朝残業や夕方残業、深夜残業など、
残業時間を算出する数式を作成していますがどうにもうまく出来ません。

残業となる時間の切り分けは、平日は以下のようになっています。、
 早朝残業→5:00~8:30
 基本時間→8:30~17:00
 通常残業→17:00~22:00
 深夜残業→22:00~翌朝5:00

例えば平日、朝6:00出社→23:00退社であれば、
早朝残業が2.5、通常残業が5.0、深夜残業が1.0となります。

深夜残業を出す数式を、以下の...続きを読む

Aベストアンサー

回答No3です。ごめんなさい。一部を修正してください。
早朝残業のE2セルには次の式を入力して下方にドラッグコピーします。

=IF(C2="","",IF(AND(C2<D2,C2<"8:30"*1,D2>"5:00"*1),(MIN(D2,"8:30")-MAX(C2,"5:00"))*24,IF(AND(C2>D2,D2>"5:00"*1),(MIN(D2,"8:30")-"5:00")*24,"")))

通常残業のF2セルには前回同様に次の式を入力して下方にドラッグコピーします。

=IF(C2="","",IF(AND(C2<D2,D2>"17:00"*1),(MIN(D2,"22:00")-MAX(C2,"17:00"))*24,IF(AND(C2>D2,D2>"17:00"*1),(MIN(D2,"22:00")-"17:00")*24,"")))

深夜残業のG2セルには次の式を入力して下方にドラッグコピーします。

=IF(C2="","",IF(AND(C2<D2,C2>="5:00"*1),(MAX(D2,"22:00")-MAX(C2,"22:00"))*24, IF(AND(C2<D2,C2>=0),(MIN(D2,"5:00")-C2+MAX(D2,"22:00")-MAX(C2,"22:00"))*24,IF(AND(C2>D2,D2<="22:00"*1),(MIN(D2,"5:00")+"24:00"-MAX(C2,"22:00"))*24,IF(AND(C2>D2,D2>"22:00"*1),(MIN(D2,"5:00")+MAX(D2,"22:00")-"22:00")*24,"")))))

回答No3です。ごめんなさい。一部を修正してください。
早朝残業のE2セルには次の式を入力して下方にドラッグコピーします。

=IF(C2="","",IF(AND(C2<D2,C2<"8:30"*1,D2>"5:00"*1),(MIN(D2,"8:30")-MAX(C2,"5:00"))*24,IF(AND(C2>D2,D2>"5:00"*1),(MIN(D2,"8:30")-"5:00")*24,"")))

通常残業のF2セルには前回同様に次の式を入力して下方にドラッグコピーします。

=IF(C2="","",IF(AND(C2<D2,D2>"17:00"*1),(MIN(D2,"22:00")-MAX(C2,"17:00"))*24,IF(AND(C2>D2,D2>"17:00"*1),(MIN(D2,"22:00")-"17:00")*24,"...続きを読む

Qエクセルで表示形式の時刻の「0:00」を表示しないようにするには?

エクセルで残業の計算をしています。
入社時間 退社時間 残業時間
のような感じでしているのですが、
残業時間が、0:00(つまり残業0)の場合は、
何も表示しない場合は、どのような関数を書けばよいでしょうか?

ちなみに残業時間のセルには、残業時間を自動的に表示させるために、
すでに「=IF((A1="")+(B1=""),"",B1-A1-"8:30")」という式が入っています。
この式にどういう式を付け加えればよいのでしょうか?

Aベストアンサー

ただ単に表示形式を変更すればいいです。現状で書式が時刻形式の
h:mmになってると思いますので、ユーザー定義でh:mm;;と後ろにセ
ミコロンを2つ追加して下さい。負の値と0の時は何も表示しないと
いう意味になります。

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エクセル関数で、残業時間から残業代を算出する方法

残業時間から残業代を算出する場合に、
関数を使って算出したいのですが、なかなかうまくいきません。

例えば、
該当月の残業時間が3時間30分で、時間単価が1500円の場合セルに、
=3:30*1500
といれるとエラーになってしまいます。

どなたか、うまく算出できる方法をご存知の方はおられないでしょうか?
エクセル初心者の為、かなり困っております。

わかりずらい説明で恐縮ですが、
ご回答頂ければ幸いです。

宜しくお願い致します。

Aベストアンサー

エクセルの時間や時刻シリアル値(<-日付シリアル値)という、エクセルの基礎的なことを勉強しないで、質問者は課題に取りかかろうとしている。
セルに3:30と入れると、
時刻を入力したと解釈され
1日は1=24時間という日付シリアル値との連造性で、3:30(3時間30)は24時間に対し0.145833333333333と計算されている。この値に時給2500円をかけてもダメで、3.5(時間)に1500円をかけないといけない。それでは0.145833333333333から3.5をどうして出すかだが、24(時間)をかけると出てくる。
=A1*24*B1
なぜなら
3時間30分は=(3*60+30) -->分ベース
1日は24*60分
1日24時間単位では=(3*60+30)/(24*60)が上記の数字。
これに24を掛けると=(3*60+30)/(24*60)*24-->(3*60+30)/60
=3.5 ということになる。
普通はこういう計算を一々辿らず、24を掛けるように覚えてしまう。
日付シリアル値はGoogleででも「日付シリアル値 エクセル」で照会すれば説明したサイトがたくさん出てくる。

エクセルの時間や時刻シリアル値(<-日付シリアル値)という、エクセルの基礎的なことを勉強しないで、質問者は課題に取りかかろうとしている。
セルに3:30と入れると、
時刻を入力したと解釈され
1日は1=24時間という日付シリアル値との連造性で、3:30(3時間30)は24時間に対し0.145833333333333と計算されている。この値に時給2500円をかけてもダメで、3.5(時間)に1500円をかけないといけない。それでは0.145833333333333から3.5をどうして出すかだが、24(時間)をかけると出てくる...続きを読む

QエクセルのIF関数で、文字が入力されていたならば~

エクセルのIF関数で文字が入力されていたならば~、という論理式を組み立てたいと思っています。

=IF(A1="『どんな文字でも』","",+B1-C1)

A1セルに『どんな文字でも』入っていたならば、空白に。
文字が入っていなければB1セルからC1セルを引く、という状態です。

この『どんな文字でも』の部分に何を入れればいいのか教えてください。

またIF関数以外でも同様のことができれば構いません。

宜しくお願いします。

Aベストアンサー

=IF(ISTEXT(A1),"",B1-C1)

でどうでしょうか?


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

人気Q&Aランキング

おすすめ情報