エクセルの24時を超えた計算について

質問

とても困っているので、わかる方教えてください。

エクセルでタイムシートの打刻時間がある時間を越えている人数と分数を調べたいと思っています。

例えば、23時以降にタイムシートを打刻してる人数を調べるのと
23時以降何分過ぎているかを調べるにはどのような関数を使えばいいのでしょうか？
（人数と分数はできれば分けて関数を組みたいです）

ただ、そのエクセルに入っているタイムシートの打刻は24時を過ぎている人も多く、
その際入力は「00:30:00」のように入力されています。
表記形式で24時と表示させている状態です。

何か補足必要であれば都度お答えします。
もしわかる方いらっしゃったらご教示ください。

よろしくお願いします。

回答No.6

noname#204879 · ベストアンサー

「24時を過ぎている人」でも 09:00:00 以降はあり得ないものとすれば、添付図の方法は如何でしょうか？

C2: =B2+(B2<"9:0"*1)
　　書式 [hh]:mm
D2: =MAX(C2-"23:0",0)
　　書式 [m];;
E2: =COUNTIF(D2:D11,">0")
　　書式 標準

お礼

ありがとうございます！

こちらの表で利用させていただいたら無事計算できました！
本当に助かりました。

お礼が遅くなり申し訳ありません。
本当にありがとうございました。

回答No.5

mu2011

>　=IF(TIME(HOUR(A1),MINUTE(A1),SECOND(A1))>TIME(HOUR(23),MINUTE(00),SECOND(0)),1,)
　⇒この数式の「TIME(HOUR(23),MINUTE(00),SECOND(0))」が誤りがあります。
　　これでは、結果は0:00を指しているにすぎません、各引数はシリアル値（小数点以下：整数の１は１日に相当している）でないと駄目です。
　　数式を簡単にすると＝IF(A1>"23:00"*1,1,0)とすれば良い。
　　例えば、23:00越える人数は=SUMPRODUCT((A1:A10>"23:00"*1)+((A1:A10<>"")*(A1:A10<="8:00"*1)))

　　又、分数は、時刻入力で0:00を24:00、1:00を25:00と入力していないのであれば、0:00以降は"24:00"を加算し、"23:00"を減算すれば分数が求められます。

　　因みに時刻計算の場合、比較する場合には、"23:00"*1として数値に変換する必要があります。

お礼

ありがとうございます。

TIME関数の使い方がいまいちわからないまま使用しておりました。
教えていただいた数式を参考にさせていただきます。

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

回答No.4

FEX2053

>=IF(TIME(HOUR(A1),MINUTE(A1),SECOND(A1))>TIME(HOUR(23),MINUTE(00),SECOND(0)),1,)
>このように組んで、23時以降の人間には1を表示させる表にくみなおしました。
>無事に24時以降の人間も1が立つんですが、どうしても24時ちょうどの表記の人間に1が立ちません。

「１」立ちます？立たないんじゃないかと思うんですが・・・。要は、TIME(HOUR(A1),MINUTE(A1),SECOND(A1)) から計算される数値が、いくつになるかってことなんです。エクセルは時間の計算を「１日を１」として計算していますので。

例えば「12:23:11」と入れたセルの表示形式を「標準」に直すと「0.5161」になります。セルの中にはこの値が入っており、表示形式だけで「12:23:11」と表示させてるんですね。

この状態で「23:00:00」と入れると「0.958333」、更に「0:0:0」と入れると「0」になります。これじゃあ比較しても0時の方が前になっちゃいます。ですので、0時を入力するときに「24:00:00」と入力して下さい。こすると見事にセルの値は「１」になります。逆に言えば、セルの値がそうなるように入力データを調整しないと、計算は上手く行きませんよ。

お礼

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

教えていただいたとおり、入力を「24:00:00」に直すと、他の回答者様に教えていただいた
計算式が問題なく動きます。

エクセルの時間の計算の内容がよくわかっていないので
とても初歩的な内容でつまづいておりお恥ずかしいです。

No.3の回答者様のお礼にも記載したとおり、入力を直そうとしているのですが
入力してもダメで、そちらを解消したいと思っています。

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

回答No.3

mshr1962

？入力は「00:30:00」でなく「24:30:00」ですよね。

＞23時以降にタイムシートを打刻してる人数を調べるのと
単純に
=COUNTIF(時刻の範囲,">=23:00:00")

＞23時以降何分過ぎているかを調べるには
一人ずつなら
=MAX(時刻のセル,23/24)-23/24
合計なら
=SUMIF(時刻の範囲,">=23:00:00")-COUNTIF(時刻の範囲,">=23:00:00")*23/24

お礼

ありがとうございます。

元々入力されてる時刻方法は「00:30:00」です。
件数が結構あるのでちゃんと確認できていなかったのですが、「1900/1/1 0:09:00」と入力されているのがほとんどでした。

そこで入力を「24:30:00」直そうとしてるのですが、どうしても上記の「1900/1/1 0:09:00」に戻ってしまいます。

これが解消できないと教えていただいたのが利用できなそうです…。

回答No.2

chonami

時刻と時間数は違います。

HOUR関数は時刻を持ってくる関数ですので、24時を過ぎたらまた0に戻ります。
戻り値の範囲は0～23です。

表示形式で24時以降も表示するようにしているという事はシリアル値も日付が変わっているという事ですね。
いったん24をかけて10進数にしてから計算したほうが良いのでは？

A1→24:30:00と入力（表示形式は[hh]:mm:ss？　まぁお好きに）
B1＝A1*24（表示形式を標準にする）→24.5

この方が後々計算しやすいと思うのですが。

お礼

ありがとうございます。
HOUR関数をよくわからず利用していました。

教えていただいたとおり、
元々入力されてる時刻「0:11:00」を「24:11:00」に修正し希望の計算をすると
うまくできました。

ありがとうございます。

時刻の計算は参考にさせていただきたいと思います。

回答No.1

m_and_dmp

表示形式の設定で h または hh を [ ]でくくってやれば、24hの値が0に戻りません。
試してください。

お礼

ありがとうございます。
表示形式はその設定で24時以降表示できるようにしております。

今回答を見てふと思いついたので、私が組んでいる関数を組み直し
=IF(TIME(HOUR(A1),MINUTE(A1),SECOND(A1))>TIME(HOUR(23),MINUTE(00),SECOND(0)),1,)
このように組んで、23時以降の人間には1を表示させる表にくみなおしました。

無事に24時以降の人間も1が立つんですが、どうしても24時ちょうどの表記の人間に1が立ちません。

これはどのように関数を立てればいいかおわかりでしたら教えてください。
よろしくお願いします。