【Excel2003】深夜時間を割り出す方法

質問

添付の画像をご覧下さい。
タイムカード式に入力し、21時以降の深夜時間を割り出したいのです。

Ａ列に「入時刻」、Ｂ列に「出時刻」。
1日のうち出入りが2回か3回あります。
（添付した例は3回ですが、2回の場合もあります）

ちなみに、Ｃ列は、合計時間です。

こんな感じで、毎日の就業時間を出していますが、
この中から21時以降の深夜の就業時間を割り出したいのです。
添付の例で言うと、Ｄ２の「1：00」という時間を出す計算式を
どのようにすればいいのかという質問です。
時間は、15分単位です。

私の知識では、考え方自体が思い浮かびません。
どなたかご教授くださいませ。

回答No.7

KURUMITO

回答Ｎｏ2です。前回の回答で21:00を22:00に変更すればよいように思いますね。ただしセルの書式は時刻にします。
ところで深夜の時間を例えば2530のように入力しているとありましたが23：30も2330のように入力しているのでしょうか？答えが出されるごとに状況を変えているようであまりよいご質問とは思われませんね。
ところで2530を25:30のようにして計算に使えるようにするためには次のような式にすればよいでしょう。Ａ1セルに2530と入力したとして、

=TEXT(TEXT(A1,"00!:00"),"[h]:mm")*1

補足

ご丁寧にご回答いただき感謝いたします。

＞答えが出されるごとに状況を変えているようであまりよいご質問とは思われませんね。
仰るとおりです。
質問をさせえいただく前の状態を、私なりに必死で作ったものですから
結局は継ぎはぎだらけになってしまってます。
私の知識では、どこに歪みが出てくるのか想像すら出来なくなっています。

頑張って作ってみます。

回答No.6

minosennin

ANo.5です。

深夜は22時から翌日5時まで、15分刻みに端数処理として考え直してみました。

時間のセル書式を[h]:mmとして、例えば翌日1時30分は25:30と表します。

E列を作業列として

E2に　=IF(AND($A2>5/24,$B2>22/24),MIN(FLOOR($B2,"0:15"),"29:00")-MAX(CEILING($A2,"0:15"),"22:00"),0)+IF(AND($A2<>"",$A2<5/24),MIN(FLOOR($B2,"0:15"),"05:00")-MAX(CEILING($A2,"0:15"),"00:00"),0)
下へ必要数ドラッグ

E列の該当行ををD列にSUMで集計

以上です。

補足

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

ひとつ問題がありまして・・・

「25:30」と表示させるセルななのですが。
いちいち　「2 5 : 3 0」と打つのが面倒なため「2 5 3 0」と打って
「25:30」と表示させるため、そこに
「=TIMEVALUE(TEXT(B2,"00"":""00"))」という式を入れています。

そのためか、セル書式を[h]:mmにしておいても「1:30]としか表示されません。

どうにかなりますでしょうか？

回答No.5

minosennin

一例です。
深夜は22時から翌日5時までですね。
入と出は15分刻みで入力されているものとします。
時間のセル書式を[h]:mmとして、例えば翌日1時30分は25:30と表します。

E列、F列を作業列として

E2に　=IF($A2>5/24,MIN($B2,"29:00")-MAX($A2,"22:00"),"")　

F列に当日0時以降に出勤の場合の深夜時間を集計します。

F2に　=IF(AND($A2<>"",$A2<5/24),MIN($B2,"05:00")-MAX($A2,"00:00"),"")　

E列、F列を下へ必要数ドラッグ
E列とF列をD列にSUMで集計

以上です。

回答No.4

kagakusuki

不明な点が幾つかありますので、補足欄等を使用して、追加情報をお知らせ願います。

(1)深夜就業に該当する時間帯は、21:00以降との事ですが、何時以前までの就業時間が深夜就業に該当するのでしょうか？
　単純に時刻を表す数字が21以上の場合のみを集計してしまうと、真夜中以降の就業分は、深夜扱いになりませんが、それでも宜しいのでしょうか？
　例えば、20:00から深夜01:00まで就業した場合には、21:00から0:00までの3時間は深夜就業になり、0:00から1:00までの1時間は深夜就業にはならないのでしょうか？
　又、深夜1:00から3:00まで就業した場合には、深夜就業にはならないのでしょうか？
　又、1:00から朝9:30までの場合は、何時から何時までが深夜就業になるのでしょうか？
　極端な場合には、夜の20:00から翌日の10:30まで就業した場合には、21:00から何時までが深夜就業になるのでしょうか？

(2)時間は15分単位との事ですが、端数がある場合には、切り捨てにすれば良いのでしょうか、それとも切り上げにすれば良いのでしょうか、四捨五入にすれば良いのでしょうか？
　例えば、10:03入の11:13出の場合、実際の時間差は1:10になりますが、端数を切り捨てると就業時間は1:00になり、切り上げの場合では1:15になり、四捨五入（7.4捨7.5入？）の場合では1:15になります。
　又、10:03入の11:10出の場合、実際の時間差は1:07になりますが、端数を切り捨てると就業時間は1:00になり、切り上げの場合では1:15になり、四捨五入の場合では1:00になります。

(3)添付されていた画像では、A4セルが21:30、B4セルが23:00になっていて、その差は1:30であるにも関わらず、深夜が1:00になっているのは何故でしょうか？
　21:30から23:00までの途中に休憩時間が入っていて、その分を差し引いているのでしょうか？
　もし、そうであるならば、休憩時間の始まりと終わりの時刻は、どこに入力されているのでしょうか？
　それとも、規則で何時から何時までの間は休憩を取らなければならない事に決まっているのでしょうか？（もし、そうであるならば、それは何時から何時までなのでしょうか？）
　又、深夜の集計には含まれていない休憩時間の30分が、合計時間の集計には含まれているのは何故なのでしょうか？

　これらの点が不明のままでは、質問者様の就業先の規則に沿った就業時間の計算を行う事は、誰にも出来ません。
　ですから、質問者様の就業先の規則に沿っていない時間が表示されても構わないのでもない限りは、上記の不明な点に関する情報を補足して頂く様、お願い致します。

補足

そうですね。
不足な部分が多々あったようです。

(1)確かに24時を過ぎた場合のことは考慮しなければなりません。
ただ、私の実力的にそこまで出来なかったのでしていませんでした。
深夜勤務は6時までです。

(2)「入」が切り上げ、「出」が切り捨ての15分単位で計算します。

(3)21時は私の間違いです。正解は22時でした。
休憩に入るときは、タイムカードを押します。
ですので、1日3回もタイムカードを打たなければならないのです。

回答No.3

KURUMITO

お示しの表では15分単位の時間が示されていますが実際にはそうではないでしょう。入時刻と出時刻について15分単位での時間にしてから計算することが必要でしょう。それらを考慮してＤ2セルには次の式を入力します。

=IF(COUNT(A2:B2)<>2,0,IF(B2<"21:00"*1,0,FLOOR(B2,"0:15")-MAX(CEILING(A2,"0:15"),"21:00")))+IF(COUNT(A3:B3)<>2,0,IF(B3<"21:00"*1,0,FLOOR(B3,"0:15")-MAX(CEILING(A3,"0:15"),"21:00")))+IF(COUNT(A4:B4)<>2,0,IF(B4<"21:00"*1,0,FLOOR(B4,"0:15")-MAX(CEILING(A4,"0:15"),"21:00")))

同じように考えるならＣ2セルには次の式を入力します。

=IF(COUNT(A2:B2)<>2,0,FLOOR(B2,"0:15")-CEILING(A2,"0:15"))+IF(COUNT(A3:B3)<>2,0,FLOOR(B3,"0:15")-CEILING(A3,"0:15"))+IF(COUNT(A4:B4)<>2,0,FLOOR(B4,"0:15")-CEILING(A4,"0:15"))

回答No.2

GOCHISOUda

合計時間は
=SUM(B2:B10)-SUM(A2:A10)

深夜時間は
=SUMIF(B2:B10,">=21:00",B2:B10)-SUMIF(A2:A10,">=21:00",A2:A10)
例は１Ｈ３０Ｍですよね。

回答No.1

mshr1962

=IF(COUNT(A2:B2)=2,FLOOR(MAX(B2,21/24)-MAX(A2,21/24),1/96),0)
または
=IF(COUNT(A2:B2)=2,TRUNC((MAX(B2,21/24)-MAX(A2,21/24))*96)/96,0)

上記の数式は21:00=21/24と入力された時間の大きいほうの時間を抽出
要は21:00前だと21:00になり、21:00以降は入力された時間になります。
でその時間差を求めれば21:00以降の就業時間になります。
後はFLOOR関数で15分=15/60/24=1/96を基準に切り捨てするか
TRUNC関数(ROUNDDOWNでも可)で時間を96倍して15分=1として切り捨て後に96で割って時間に戻す。