中小企業の働き方改革をサポート>>

ご閲覧ありがとうございます。
勤務時間の計算をエクセルを使ってしたいと思っているのですが、関数をどのようにすればよいのかわからずにおります。

A1:所定開始時間、B1:所定終了時間、C1:実動開始時間、D1:実働終了時間、
E1:昼休憩時間、F1:深夜時間帯休憩時間、G1:所定時間帯実働時間、H1:残業時間、
I1:所定深夜時間、J1:深夜残業時間

上記のように表を作り、基本労働時間7.75時間で昼休憩45分。
5時~22時が昼時間でそれ以外の時間帯は深夜時間になります。

例えば、8:30出勤で17:00退社の場合は、実働7.75時間。
8:30~22:15の場合は、所定内実働時間7.75、残業5時間-休憩時間、深夜残業0.25時間
16:45~7:30の場合は、所定内実働時間5.25、深夜所定2.5、深夜残業3-休憩、残業2.5-休憩

といった具合になります。

今まで、手計算で行っていたので、簡単に表計算できたら時簡短縮にもなるので
どうぞ、ご存知の方がいらっしゃいましたらお教えください。
よろしくお願いいたします。

A 回答 (15件中11~15件)

K列以降のセルを使用しても良いですか?(セルの幅を「0」にしてしまえば見えませんので…)


「終了時刻(24時間以上の数字を持てるようにしたもの)」「昼時間」「深夜時間」を持っておくと計算が複雑にならないため
    • good
    • 0
この回答へのお礼

ありがとうございます。

もちろん問題ありません。
よろしくお願い致します。

お礼日時:2017/12/03 18:16

すみません。

No.2 の昼休と深休などの単位がもしかしたら違っていませんか?
特に[ ]で囲んだところをもう一度確認してください。
こちらでは以下のようではないかと思っていますが、いかがでしょうか?
|  A  | B  | C | D | E   |  F |  G | H   | I |  J |
|所定始|所定終|実始 |実終 |昼休 | 深休 |所定実|所定深 |残業|深残|
|8:30 |17:00|8:30 |17:00|[0:45]|[0:00]|7.75 |0.00|0.00|0.00|
|8:30 |17:00|[8:30]|[22:15]|[1:00]|[0:00]|7.75|0.00|4.75 |0.25|
|16:45 |7:30 |16:45|1:30 |[0:15]|[1:30]|5.25|2.25|2.50|3.00|
    • good
    • 0
この回答へのお礼

ありがとうございます。

申し訳ありません。
仰るとおりでございます。

お礼日時:2017/12/03 18:15

もういくつか確認です。


① どこの列を計算で出すのでしょうか?(自信はありませんがG列以降でしょうか?)
② もし昼休憩時間(E列)を計算で出すとしたら、どのような条件でしょうか?
③ もし深夜時間帯休憩時間(F列)を計算で出すとしたら、どのような条件でしょうか?
④ A列からD列は15分単位になっているのでしょうか?もしなっていない場合は開始時間は切り上げ終了時間は切り捨てにするのでしょうか?
    • good
    • 0
この回答へのお礼

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

仰るとおり、G列以降を計算で出したいと思っております。
昼休憩、深夜休憩は計算式で出さなくて大丈夫です。

A~Dも15分単位になっております。

色々な条件で申し訳ありません、よろしくお願いいたします。

お礼日時:2017/12/03 16:59

出来たらセルのレイアウトが判るようにいくつかのデータの入ったものをキャプチャした物を載せていただくか、以下のような感じで数行具体的な数字を入れてみてください。


1|A|B|C|D|E|F|G|H|I|J
2|所定開始時間|所定終了時間|実動開始時間|実働終了時間|昼休憩時間|深夜時間帯休憩時間|所定時間帯実働時間|残業時間|所定深夜時間|深夜残業時間
3|8:30|17:00||||||||
4|8:30|22:15||||||||
5|16:45|7:30||||||||
※ 今入っている時間は場所とかも違うと思いますのでそれも直して下さい。
※ キャプチャした物を貼り付ける際、縦・横とも500ピクセルを超えると縮小されますので適当に列幅を縮めてキャプチャしてください(タイトル行は一部見えなくてもかまいません)
※ 時間単位の列と時分単位で表示される部分が有るようですが、どこがどちらかも提示してください。
※ 開始時刻の方が終了時刻より後の場合は夜中を超えたと判断して良いのでしょうか?
    • good
    • 0
この回答へのお礼

|  A  | B  | C | D | E   |  F |  G | H   | I |  J |
|所定始|所定終|実始 |実終 |昼休 | 深休 |所定実|所定深 |残業|深残|
|8:30 |17:00|8:30 |17:00|0.45 |0.00 |7.75 |0.00 |0.00 |0.00|
|8:30 |17:00|8.30 |22.15|1.00 |0.00 |7.75 |0.00 |4.75 |0.25|
|16:45 |7:30 |16:45|1:30 |0.15 |1.5 |5.25 |2.25 |2.5 |3.0 |

ご回答ありがとうございます。
このような感じでよろしいでしょうか。
開始時刻が終了時刻が後の場合は夜中を超えた状態となっております。
実際勤務した時間(G以降)は、可能であれば15分を0.25で表示したいです。
よろしくお願いいたします。

お礼日時:2017/12/03 15:34

EXCELによる勤怠管理は、テンプレートが無料で公開されています。


http://zestyoga.net/kintai/

これらを使われるか、中の関数や計算式をご参照されるのが良いと思います。
    • good
    • 0

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

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

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残業時間の計算をするにはどのような関数あるいは計算式を設定すればいいの

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

(例)
       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エクセル勤務表で昼休憩を挟んだ時の休憩算出方法

エクセルで添付のような勤務表を作成しています。
労働時間(表中・小計)に応じた休憩時間は自動算出出来るようになったのですが、まだ課題がありまして…

それは、お昼休憩を挟んだ時の休憩時間についてです。
うちでは、お昼を挟むと、勤務時間に関係なく1時間の休憩になります。(お昼休憩)


例えば、9時~18時だと、小計9時間・休憩1時間(8時間以上なので)・実労働(表中・合計)時間8時間となるので大丈夫なのですが。

9時~15時の勤務だと、小計6時間・休憩30分・実働5時間30分になってしまいます。
これを、小計6時間・休憩1時間・実働5時間になるように計算させたいのです。


表中の各計算式は以下です。

C8→勤務開始を入力
D8→勤務終了を入力

D8の小計→=IF(D8<C8,1+D8-C8,D8-C8)
F8の休憩→=IF(E8<"4:00"*1,0,IF(E8<"6:00"*1,10,IF(E8<"7:00"*1,30,IF(E8<"8:00"*1,40,60))))/24/60

G8の合計→=E8-F8

です。

アドバイスよろしくお願いします。

エクセルで添付のような勤務表を作成しています。
労働時間(表中・小計)に応じた休憩時間は自動算出出来るようになったのですが、まだ課題がありまして…

それは、お昼休憩を挟んだ時の休憩時間についてです。
うちでは、お昼を挟むと、勤務時間に関係なく1時間の休憩になります。(お昼休憩)


例えば、9時~18時だと、小計9時間・休憩1時間(8時間以上なので)・実労働(表中・合計)時間8時間となるので大丈夫なのですが。

9時~15時の勤務だと、小計6時間・休憩30分・実働5時間30分にな...続きを読む

Aベストアンサー

であれば、F列のIF関数の最初に次の条件を追加すればいけるのでは?

「業務開始時間が12時より前かつ、勤務終了時間が13時より後ろだったら、休憩時間は1.0hとする」

=IF(AND(C8<"12:00",D8>"13:00"),1,IF(E8<"4:00"*1,0,IF(E8<"6:00"*1,10,IF(E8<"7:00"*1,30,IF(E8<"8:00"*1,40,60))))/24/60)

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エクセル不変則な休憩時間の実労働時間計算

お世話になります。色々と調べて見ましたが分からなかったので質問します。
今、勤務管理表を作成しています。労働条件は下記の通りです。
 ◆定時:9:30から17:30 ◆休憩時間:12:00~13:00
 ◆遅刻した場合でも勤務時間が休憩の時間をまたいだときは必ず、12時から13時までとります。

遅刻したり、早退した場合、その時間によって、実労働時間が変わります。
例えば、12:53に出勤⇒13:59に退社したときは実労働時間は59分です。(休憩時間を考慮する)
また9:30よりも早く出勤しても特に手当てはつかず、9:30出勤とみなします。

この場合、下図の「出勤時間」、「退社時間」を入力すると、「休憩時間」と「実労働時間」が
自動的に出したいのですが、どの様な数式を使えばいいのでしょうか。
列を増やす事はOKです。よろしくお願い致します。


   

Aベストアンサー

ゾーンに切ってそれぞれの時間数を数えることで,同じ数式で計算できます。

添付図:
C2:
=MAX(MIN("12:0",B2),"9:30")-MIN("12:0",MAX("9:30",A2))
D2:
=MAX(MIN("17:30:0",B2),"13:00")-MIN("17:30",MAX("13:00",A2))
E2:
=MAX(MIN("22:00:0",B2),"17:30")-MIN("22:00",MAX("17:30",A2))
必要に応じて更に「深残帯(22時以降)」を計算する事も勿論できます。


F2:
=B2-A2
G2:
=SUM(C2:E2)
H2:
=F2-SUM(C2:E2)

9:30前の出社を「ノーカウント」とした辻褄として,H3で「休憩時間」に算定されている様子が示されています。

Q複数の休憩時間がある場合の休憩時間の算出方法

Excel で勤務表を作っていますが、
休憩時間が複数ある場合の休憩時間の算出方法が分かりません。

D5 10:00 (出勤時刻)
E5 19:00 (退勤時刻)
F5 休憩時間
H5 =E5-D5-F5 (実稼働時間)

D6 12:00 (出勤時刻)
E6 22:00 (退勤時刻)
F6 休憩時間
H6 =E6-D6-F6 (実稼働時間)

休憩時間1
D38 12:00 (休憩開始時刻)
E38 13:00 (休憩終了時刻)
F38 1:00 (休憩時間1のトータル)

休憩時間2
D39 17:15 (休憩開始時刻)
E39 17:30 (休憩終了時刻)
F39 0:15 (休憩時間2のトータル)

休憩時間2の休憩時間帯が変わってもカスタマイズできるように
(休憩時間3、4、5と実際にはあります)
D38,D39,E38,E39は使わず
F38とF39を使って、F5、F6セルに、休憩時間を算出したいです。

また、13時以降の出社の場合は、12時から13時の休憩時間を含めないようにもしたいです。

どんな関数を用いれば実現可能でしょうか?
アドバイスよろしくお願い致します。

Excel で勤務表を作っていますが、
休憩時間が複数ある場合の休憩時間の算出方法が分かりません。

D5 10:00 (出勤時刻)
E5 19:00 (退勤時刻)
F5 休憩時間
H5 =E5-D5-F5 (実稼働時間)

D6 12:00 (出勤時刻)
E6 22:00 (退勤時刻)
F6 休憩時間
H6 =E6-D6-F6 (実稼働時間)

休憩時間1
D38 12:00 (休憩開始時刻)
E38 13:00 (休憩終了時刻)
F38 1:00 (休憩時間1のトータル)

休憩時間2
D39 17:15 (休憩開始時刻)
E39 17:30 (休憩終了時刻)
F39 0:15 (休憩時間2のトータル)

休憩時間2の休憩時間帯が変わってもカスタマ...続きを読む

Aベストアンサー

>休憩3、休憩4、休憩5、休憩6まである場合は、関数はどう変更すればいいですか?
始めにSUM関数の引数に不備がありましたので訂正します。
序に、出勤時刻と退勤時刻が未入力のとき空欄とするためのIF関数を追加しました。
=IF(COUNT(D5:E5)=2,SUM(MIN(MAX(E5,$D$38),$E$38)-MAX(MIN(D5,$E$38),$D$38),MIN(MAX(E5,$D$39),$E$39)-MAX(MIN(D5,$E$39),$D$39),MIN(MAX(E5,$D$40),$E$40)-MAX(MIN(D5,$E$40),$D$40),MIN(MAX(E5,$D$41),$E$41)-MAX(MIN(D5,$E$41),$D$41),MIN(MAX(E5,$D$42),$E$42)-MAX(MIN(D5,$E$42),$D$42),MIN(MAX(E5,$D$43),$E$43)-MAX(MIN(D5,$E$43),$D$43)),"")
SUM関数の内容を確認してください。
第1引数(休憩1) MIN(MAX(E5,$D$38),$E$38)-MAX(MIN(D5,$E$38),$D$38)
第2引数(休憩2) MIN(MAX(E5,$D$39),$E$39)-MAX(MIN(D5,$E$39),$D$39)
     ・
     ・
     ・
第6引数(休憩6) MIN(MAX(E5,$D$43),$E$43)-MAX(MIN(D5,$E$43),$D$43)
各引数は休憩時間帯を定義した行を参照しますがMAX関数とMIN関数およびセルの列記号は同じパターンになっています。
応用力で指定された休憩時間帯を増減できますので実情に合わせて変更すると良いでしょう。
これはExcelの関数を利用してどのような論理で計算するかの応用問題です。
数学が不得手の方には難しいかも知れません。

>休憩3、休憩4、休憩5、休憩6まである場合は、関数はどう変更すればいいですか?
始めにSUM関数の引数に不備がありましたので訂正します。
序に、出勤時刻と退勤時刻が未入力のとき空欄とするためのIF関数を追加しました。
=IF(COUNT(D5:E5)=2,SUM(MIN(MAX(E5,$D$38),$E$38)-MAX(MIN(D5,$E$38),$D$38),MIN(MAX(E5,$D$39),$E$39)-MAX(MIN(D5,$E$39),$D$39),MIN(MAX(E5,$D$40),$E$40)-MAX(MIN(D5,$E$40),$D$40),MIN(MAX(E5,$D$41),$E$41)-MAX(MIN(D5,$E$41),$D$41),MIN(MAX(E5,$D$42),$E$42)-MAX(MIN(D5,$E$42),$D$4...続きを読む

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エクセルで表示形式の時刻の「0:00」を表示しないようにするには?

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

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

Aベストアンサー

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

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

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

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

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

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

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

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

 

Aベストアンサー

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

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

Qエクセルでの計算式もしくはシートの作り方 1日を7時間45分に

エクセルで年次有給休暇簿を作ろうとしています。
 作り方を教えてください

年休は時間単位で修得できます。 しかし,今年から勤務時間が8時間から7時間45分に変更になり累計の計算が煩雑になりました。

 今までは年休を1時間取る時は1/8と書いて
それを 累計していけば簡単にできました。
例えば 4/8の次に6/8をとると1と2/8
次に7/8をとると2と1/8と簡単でした。

今年度からは 4時間とって 次に5時間取ると合計9時間となり,
勤務時間が7時間45分なので 年休の累計は1日と1時間15分となると思います。
 何かいい計算式がありませんか?
(時間を分になおしたり いろいろしましたが,分かりませんでした。)

Aベストアンサー

日数で管理するのではなく、時間で管理したらいかがでしょう。
1は1時間、0.25を15分として計算します。
従って、1日分の勤務時間7時間45分は7.75となります。

例えば、A1に「休暇時間」、B1に「累計時間」、C1に「年休日数」、D1に「年休時間」と入れます。
A列に休暇時間を入力したら、B列にそれまでの累計が出るように式を入力しておきます。
B3が10の時、
C3に「=ROUNDDOWN(B3/7.75,0)」と入れて時間から日数を計算します。(1)
D3に「=B3-7.75*C3」とすれば、日数を除いた半端時間が計算できます。(2.25)
あとはA列に休暇時間を入力するごとに、B列~D列の計算式をコピーするだけです。


人気Q&Aランキング

おすすめ情報