excelの時間計算

質問

excel 2007使用。
時間計算の式についてお聞きしたく、詳しい方ご教授願います!!

<1>
8時30分から17時20分まで就業し、うち1時間の休憩をひいた時間を
セルに表示させたい。
この場合、拘束時間は8:50ですが、休憩を引いた値「7:50」を出したい。
セルは　「830」「1720」で入力していますが、結果は750ではなく、
":"が入った状態(7:50)と出したい。

<2>
上で算出した休憩を引いた勤務時間の小計(週ごと)、合計(月)を作りたい(SUM?)

<3>
個々に入力した残業時間の小計(週ごと)、合計(月)を作りたい(SUM?)
残業時間個々のセルは「1:00」(1時間)、「0:40」(40分)と":"が入った状態で入力しています。

回答No.3

MackyNo1 · ベストアンサー

＞F3　手動で7:50と入力　【[h]:mm】

ここだけ「7:50」と入力するなら、出勤時間と退勤時間も時刻形式で入力したほうがよいと思うのですが、そのままのレイアウトを使うなら、以下のような数式を入力したほうが簡単です（1日の実労働時間が7：50を下回るときにも対応できる）。

=IF(E3="","",MＩＮ(TEXT(E3,"0!:00")-TEXT(D3,"0!:00"),"7:50"))

＞G3　残業時間　【=TEXT(E3,"0!:00")-TEXT(D3,"0!:00")-"8:50"】

#########エラー表示をなくしたいなら以下のようにします。

=IF(E3="","",MAX(TEXT(E3,"0!:00")-TEXT(D3,"0!:00")-"8:50",0))

＞G　お休みの日(土・日などは式を設定せず、セル空白)　★ここが不満

上記の式をそのままオートフィルコピーしてください。

＞N2　週の残業　【=SUM(G1:G7)】　※セル書式は"[h]:mm"　★ここが狂います

上記の数式で計算してみてください。

ちなみにＫ列に入っている値が集計範囲と違うように思うのですが、集計時の1週目は週の途中の1日からスタートするのですよいのですね。

また、Ｂ列は日曜から開始し、12月1日が3行目から始まっていますが、実際はどのような運用をしているのでしょうか？

たとえば上記の条件ならどこか1つのセルに12/1などと月の初めの日を入力すればＡ列とＢ列に自動的に日付と曜日のリストを作成することができますので月ごとの集計表が簡単に作成できます。

お礼

上記方法のIF関数でMIN,MAXを指定することにより
未入力箇所がある場合の「######」エラーおよび
火曜日までしか入力していなくても残業時間の小計が出せるように
なりました。

これで実際に使えるレベルに達することが出来ました。
今回は誠にありがとうございます!!

さて、表のはじまりですが、
1週目(11月はもちろん集計しません)～5週目
という週の区切りがあること、
1月以降もカレンダーのみ書き替えて
このひな形を使うつもりでしたので
あえて日曜日開始にしました。

ただ、すべての式の有効性がわかった以上、
わざわざ不要なセルを残す必要もないと考えます。

さて、祝日設定の件なのですが
たまに土曜出勤のときもありまして、
「土・日はぜんぶ休み」という設定にしてしまうと
整合性がとれないことから「C1」に1という数字を入れることにより、
出勤日の小計・合計を算出していました。

例えば祝日設定、「日曜日は全休、土曜は12/5.12,19のみ休み(12/26は出勤)」
といったようなリストは作成できるのでしょうか?
ご教授いただきたく思います。

回答No.2

MackyNo1

<1>
8時30分から17時20分まで就業し、うち1時間の休憩をひいた時間を
セルに表示させたい。
この場合、拘束時間は8:50ですが、休憩を引いた値「7:50」を出したい。
セルは　「830」「1720」で入力していますが、結果は750ではなく、
":"が入った状態(7:50)と出したい。

A1セルに830、B1セルに1720と入力されているなら以下の式を入力して、セルの書式をユーザー定義で「h:mm」にしてください。

=TEXT(B1,"0!:00")-TEXT(A1,"0!:00")-"1:00"

<2>
上で算出した休憩を引いた勤務時間の小計(週ごと)、合計(月)を作りたい(SUM?)

ごく普通にデータ範囲をSUM関数で集計し、セルの書式をユーザー設定で「[h]:mm」にしてください。

<3>
個々に入力した残業時間の小計(週ごと)、合計(月)を作りたい(SUM?)
残業時間個々のセルは「1:00」(1時間)、「0:40」(40分)と":"が入った状態で入力しています。

本当は出勤時間と退勤時間だけを入力すれば自動的に残業時間が計算できるシステムにできるのですが、すでに残業時間が入力されているデータを使用するなら、これも基本はSUM関数を利用することになりますが、日付などがセルに入力されていれば、そのデータから集計範囲を自動的に判別して、月や週毎の合計を表示させることができます。

元データのリストのレイアウト（各列の項目名と入力例）と、集計したいリストのレイアウト例などを提示されれば具体的な数式が提示できると思います。

補足

画像はイメージであり、若干下記のセルと異なります

【セルの内訳】
C3　出勤日に手動で「1」と入力　
D3　出勤時間　(":"なし)
E3　退勤時間　(":"なし)
F3　手動で7:50と入力　【[h]:mm】
G3　残業時間　【=TEXT(E3,"0!:00")-TEXT(D3,"0!:00")-"8:50"】
H3　計算用　【=G3】　★このセルは解決したので削除しました

G　お休みの日(土・日などは式を設定せず、セル空白)　★ここが不満

L2　出勤日数　【=SUM(C1:C7)】
M2　週の実労働　【=SUM(F1:F7)】
N2　週の残業　【=SUM(G1:G7)】　※セル書式は"[h]:mm"　★ここが狂います

回答ありがとうございます。
ベースは出来たのですが、
土日などお休みの日の空白セルが存在したり、
火曜日に1時間残業しても
今作った式だと
N2の週の残業セルに「##############」と表示されてしまい
エラーになります。

まだ入力していない日、お休みの日のセルデータを強制的に0にする方法か、
金曜日にならなくても、残業時間が表示される式を教えてください。

回答No.1

osamuy

Excelですと、数値の1.0が日付値の86400秒＝1440分＝24時間に相当するので、例えば「830」なら、

(1)数値830を100で割る＝8.3
(2)小数部を切捨てて時間の値を求める＝8
(3)元の830から(2)を100倍したものを引いて分の値を求める＝30
(4)(3)を60で割って分から時間にする＝0.5
(5)(2)と(4)を足して時間を求める＝8.5
(6)休み時間を1時間引く＝7.5
(7)時間を24で割って、セルの表示形式を時間に設定＝7:30

――みたいのでいけるかと。実際の式は省略。

参考URL：

http://www.excel.studio-kazu.jp/mwiki/index.php/%E3%82%B7%E3%83%AA%E3%82%A2%E3%83%AB%E5%80%A4