エクセルと時間の計算

質問

エクセルと時間の計算

学校勤務で勤務時間計算の事務を以下のようなエクセルシートでしています。

表の上段は、残業のない通常の場合ですが、下段は、残業があった場合の
シートになっています。

Aさんは8時間勤務で休憩45分、Bさんは4時間勤務で休憩なしです。
それ以上は残業になるのですが、下段の表がその場合です。
ここで、通常の勤務時間合計と残業時間合計を分けて計算したいです。
その結果を
F列6，7とG列6，7に表示したいのですが。

Aさんは8時間を超えた時間は、G6の合計に、
Bさんは4時間を超えた時間は、G7の合計に
入れたいのですが、どんな数式を入れればいいのでしょうか。

関数初心者で申し訳ありませんが、よろしくお願いします。

この投稿のマルチメディアは削除されているためご覧いただけません。

回答No.7

tom04 · ベストアンサー

こんばんは！
横からお邪魔します。
すでに　hallo-2007さんの回答で解決していると思いますので、
余計なお世話になるかもしれませんが・・・

↓の画像のように「休憩」の「なし」（文字列）をマイナスするとエラーになりますので、
「なし」を「0」と入力しておきます。

そして、F2セルに
=IF(A2="","",IF((D2-C2-E2)*24>=B2,B2,(D2-C2-E2)*24))
G2セルに
=IF(A2="","",IF((D2-C2-E2)*24>B2,(D2-C2-E2)*24-F2,0))
という数式を入れ、F2・G2セルを範囲指定し、G2セルのフィルハンドルで下へコピーすると
画像のような感じになります。
尚、F2・G2セルの表示形式は「標準」にしておきます。

こんな感じがご希望なのですかね？m(__)m

回答No.8

KURUMITO

Ｅ２セルには次の式を入力してから、セルの表示形式は時刻にして下方にオートフィルドラッグします。

=IF(A2="","",IF(AND(C2<"12:00"*1,D2>="12:45"*1),"0:45","なし"))

この式では12時から12時45分までが休憩時間としています。適宜変更して使用してください。

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

=IF(A2="","",ROUND(MIN((D2-C2-IF(E2="なし",0,E2))*24,B2),2))

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

=IF(A2="","",ROUND((D2-C2-IF(E2="なし",0,E2)-F2/24)*24,2))

なお、Ｆ列およびＧ列の表示形式は標準にします。

回答No.6

MackyNo1

＞できれば勤務時間合計、残業時間合計ともに
8：00のような時間ではなく
契約時間のように単なる数値の形式で表示したいのですが。

この場合は「1：20」は「1.333333」となりますがOKですか？

OKなら提示した数式に「２４」を掛けてください。

「1.2」と表示したいなら以下のような式にしてください（セルの書式は標準）。

=TEXT(元の数式,"[h].mm")*1

回答No.5

MackyNo1

G6セルに以下の式を入力して下方向にオートフィルしてください。

=MAX(D6-SUM(C6,E6)-B6/24,0)

回答No.4

hallo-2007

>契約時間のように単なる数値の形式で表示したいのですが。
時間を数値に変更する方法ですが　時間の場合は　２４をかけます
ちなみに　分　の場合は＊２４＊６０　です。
エクセルに　シリアル値ですが　1日(24時間)を数値の　1　であらわしています。
試しに開いているセルに　12:00 と入れてみてください。
そして　セルの表示形式を　数値にすると　0.5　時刻にすると　12:00 なのです。
つまり
F6セルには　=(今の式)*24 書式を数値
G6セルには　=(D6-C6-E6)*24-F6
となります。

回答No.3

mshr1962

B6=8,C6="8:30",D6="18:15",E6="0:45"
F6=MIN((D6-C6-IF(E6="なし",0,E6))*24,B6)
G6=MAX((D6-C6-IF(E6="なし",0,E6))*24-B6,0)

E列の表示形式が、ユーザー定義で　[h]:mm;;"なし"　の場合は　E7=0　として
F6=MIN((D6-C6-E6)*24,B6)
G6=MAX((D6-C6-E6)*24-B6,0)

回答No.2

hallo-2007

時間の引き算ですよね。
F6セルに　=MIN(TIME(B6,0,0),D6-C6-E6)
G6セルに　=D6-C6-E6-F6
でご希望になりませんか？

補足

ありがとうございます。
追加で、できれば勤務時間合計、残業時間合計ともに
8：00のような時間ではなく
契約時間のように単なる数値の形式で表示したいのですが。

変更してすいません。

回答No.1

kmetu

> Aさんは8時間を超えた時間は、G6の合計に

=IF(D6-C6-TIME(8,45,0)>0,D6-C6-TIME(8,45,0),"")

> Bさんは4時間を超えた時間は、G7の合計に

=IF(D7-C7-TIME(4,0,0)>0,D7-C7-TIME(4,0,0),"")

でいかかがでしすか