勤務時間・時間外の計算方法を教えて下さい

質問

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は赤字で表示出来たらうれしいです。

無理なことを言っているかもしれませんが、お願いします。

回答No.3

KURUMITO · ベストアンサー

お示しのデータで項目名がA1セルからF1セルまでに入力されており、2行目から下方に関連するデータが有るとします。
そこでD2セルには次の式を入力して下方にドラッグコピーします。

=IF(COUNT(A2:B2)<>2,"",MIN(B2-A2-C2*"1:00","8:00"))

この式の意味はCOUNT関数でA2セルとB2セルに時刻のデータ（数値のデータ）が2個無ければ空にしなさい、そうでない場合にはMIN関数でB2-A2からさらにC2*"1:00"を引き、その値と"8:00"を比べてどちらか小さい方の数値を表示しなさいということです。2行目や3行目のデータでは8時間を超しますので8:00が表示されますね。
ここでC2*"1:00"を使っていますがC2の値が時間単位になっていますので、それを時間にするために"1:00"を掛けています。

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

=IF(D2="","",IF(D2<"8:00"*1,0,B2-A2-C2*"1:00"-"8:00"))

この式の意味はD2セルが空の時は空にしなさい、そうでない場合にはD2セルの値が8時間未満の場合には0を表示しなさい、8時間以上の場合にはB2-A2-C2*"1:00"の値から"8:00"を引いた値を表示しなさいということです。
時間の計算では例えば8時から３時を引くといった計算では　="8:00"-"3:00" のような式にして計算することができます。
D２の値が８時間未満といった場合には　D2<"8:00"*1 のように使用します。"8:00"は文字列となっていますが*１で数値に変換しているのです。時間の足し算や引き算では文字列表示の時間をパソコンが自動的に数値に変換して計算しているのです。

F２セルには次の式を入力して下方にドラッグコピーします。

=IF(D2="","",IF(D2/"8:00"*1>0.5,1,0.5))

この式の意味はD２セルが空白なら空白にしなさい、そうでない場合にはD２セルの値を"８:００"*１で割った時の値が０．５を超えた値の時は１とし、０．５以下の場合には０．５としなさいとの意味です。
F列を選択して右クリックし「セルの書式設定」の「表示形式」で「標準」にします。

お礼

分かりやすい解説付きで大変役に立ちました。

希望通りの回答を頂きました。ありがとうございます。

回答No.2

x-1919

時間と時刻は間違えないように。

質問投げかけの例題だけじゃなくて、イメージしている結果も載せないと回答できないと思う。
それと会社独自のルールも。
休憩 0.5 だと残りの 0.5 をどうするのか。
勤務日数 0.5 の日は休憩があるのかないのか。
勤務日数 1 の日は何時間を超えて働けば時間外になるのか。0.5 の日は何時間を超えて働けば時間外になるのか。

> また、勤務日数を勤務時間　8時間の場合は　１　半日の場合は　0.5　というようにすぐ表示出来るようにならないでしょうか。0.5は赤字で表示出来たらうれしいです。

逆だ。
勤務時間や時間外の計算をするための元になるのが "勤務日数" の値である。
出勤時刻、退勤時刻、休憩時間、勤務日数の値がないと勤務時間と時間外を出せないと思うのだが。
勤務日数の値が 1 なら、その日の働いた時間が 8時間を超えると時間外が付く。
勤務日数の値が 0.5 なら、その日働いた時間が 4時間を超えると時間外が付く。
それとも出勤時刻と退勤時刻という 「その日その時刻にたまたま記録された情報」 から 「その日は 4時間働かなきゃならない日」 とか 「その日は 8時間働かなきゃならない日」 を決定できるのだろうか？
普通は先にそれが決まってるんじゃない？
従業員が出社後に 「今日はお昼で帰りたい」 って言ったら 「今日は 4時間の日」 ってなるの？

お礼

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

＞勤務時間や時間外の計算をするための元になるのが
"勤務日数" の値である。

勤務時間、時間外の計算には勤務日数は関係ないと思っていましたが違うんですね。

うちの会社は、出社後に午後から用事があるのでお昼で帰ります。
と言えばお昼で帰れます。
午前中用事があるので、午後から出社します。と言えば午後半日の勤務になります。

ときどきあることではないでしょうか。

回答No.1

keithin

幾つか詰めの甘い部分はまた別途改良を考えてもらうとして、とりあえず今回のご相談の範囲を解決してみます。

＞１：勤務時間数から休憩時間数を差し引く

Ｃ列には数字の１や０．５、あるいは０を記入します。
Ｄ２：
=B2-A2-C2*"1:0"
のようにしておきます。

なお、１番の人の勤務時間は８：００じゃなく８：３０の間違いですね？

先にこっちから片づけます
＞３
あなたのご相談通り、４時間（以上）なら０．５とし、８時間（以上）なら１にしてみます。（言い換えると、それだと半日で遅刻早退するとゼロになるって意味です）
Ｆ２：
=((D2-"4:0">=0)+(D2-"8:0">=0))*0.5

先に０．５と１を計算しておくと、これを利用して４：００なのか８：００なのか計算もできます。
＞２
Ｅ２：
=D2-F2*"8:0"

＞0.5は赤字で表示出来たらうれしいです。

そういうのは「条件付き書式」で設定しておきます。
ご利用のエクセル（そういえば何のソフトを使ってやりたいのかからして書かれてませんね）のバージョンも不明のご相談なので詳細の手順は省略しますが。
ホームタブの条件付き書式▼からセルの強調表示ルールで、「指定の値に等しい」（Excel2003以前では「セルの値が」）で０．５の時に書式ボタンでフォントの色を赤くしておきます。
実際に手を動かして操作してみて、練習してください。

＃幾つか補足事項
今のご相談の表では、３番の人が「ＡＭ半日勤務＋残業」をしたのか、「１日勤務で早退」したのか、区別できません（＝計算できないって意味です）。
また８時または１３時出社に統一されているのも大変奇異に見えますが、早出遅刻はどのように計算したいのでしょうか。

もしこういった部分でご相談に不足？があるようなら、必要に応じて詳しい状況（あなたのヤリタイ事の説明）をキチンと添えて、「別途ご相談を投稿して」みてください。

お礼

説明不足もありましたが回答ありがとうございます。

＞３の回答はとても参考になりました。

半日勤務の場合の計算方法は改めて相談してみます。