とても困っているので、わかる方教えてください。
エクセルでタイムシートの打刻時間がある時間を越えている人数と分数を調べたいと思っています。
例えば、23時以降にタイムシートを打刻してる人数を調べるのと
23時以降何分過ぎているかを調べるにはどのような関数を使えばいいのでしょうか?
(人数と分数はできれば分けて関数を組みたいです)
ただ、そのエクセルに入っているタイムシートの打刻は24時を過ぎている人も多く、
その際入力は「00:30:00」のように入力されています。
表記形式で24時と表示させている状態です。
何か補足必要であれば都度お答えします。
もしわかる方いらっしゃったらご教示ください。
よろしくお願いします。
No.1
- 回答日時:
表示形式の設定で 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が立ちません。
これはどのように関数を立てればいいかおわかりでしたら教えてください。
よろしくお願いします。
No.2
- 回答日時:
時刻と時間数は違います。
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.3
- 回答日時:
?入力は「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.4
- 回答日時:
>=IF(TIME(HOUR(A1),MINUTE(A1),SECOND(A1))>TIME(HOUR(23),MINUTE(00),SECOND(0)),1,)
>このように組んで、23時以降の人間には1を表示させる表にくみなおしました。
>無事に24時以降の人間も1が立つんですが、どうしても24時ちょうどの表記の人間に1が立ちません。
「1」立ちます?立たないんじゃないかと思うんですが・・・。要は、TIME(HOUR(A1),MINUTE(A1),SECOND(A1)) から計算される数値が、いくつになるかってことなんです。エクセルは時間の計算を「1日を1」として計算していますので。
例えば「12:23:11」と入れたセルの表示形式を「標準」に直すと「0.5161」になります。セルの中にはこの値が入っており、表示形式だけで「12:23:11」と表示させてるんですね。
この状態で「23:00:00」と入れると「0.958333」、更に「0:0:0」と入れると「0」になります。これじゃあ比較しても0時の方が前になっちゃいます。ですので、0時を入力するときに「24:00:00」と入力して下さい。こすると見事にセルの値は「1」になります。逆に言えば、セルの値がそうなるように入力データを調整しないと、計算は上手く行きませんよ。
ご回答ありがとうございます。
教えていただいたとおり、入力を「24:00:00」に直すと、他の回答者様に教えていただいた
計算式が問題なく動きます。
エクセルの時間の計算の内容がよくわかっていないので
とても初歩的な内容でつまづいておりお恥ずかしいです。
No.3の回答者様のお礼にも記載したとおり、入力を直そうとしているのですが
入力してもダメで、そちらを解消したいと思っています。
ありがとうございました。
No.5
- 回答日時:
> =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を指しているにすぎません、各引数はシリアル値(小数点以下:整数の1は1日に相当している)でないと駄目です。
数式を簡単にすると=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関数の使い方がいまいちわからないまま使用しておりました。
教えていただいた数式を参考にさせていただきます。
ありがとうございました。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Excel(エクセル) 出退勤管理の遅刻・早退時間について 3 2023/08/10 15:33
- Excel(エクセル) ユーザー定義について質問です。 2 2023/06/28 13:21
- その他(教育・科学・学問) エクセル関数について 2 2022/12/23 08:59
- Excel(エクセル) 時間差を求めるマクロコードを教えてください。 4 2022/05/17 18:22
- Visual Basic(VBA) エクセルマクロでアニメを作る方法を教えてください。 1 2023/02/07 14:27
- Excel(エクセル) エクセルで割り振りをする方法 7 2022/08/02 14:02
- Excel(エクセル) エクセルで時間の合計 5 2022/06/04 10:54
- その他(Microsoft Office) 勤務表のエクセル作成で数式を教えてください。 1 2023/01/17 03:27
- Excel(エクセル) 前の(左隣の)シートを連続参照するように、あとから変更したい 1 2023/02/22 00:51
- Excel(エクセル) エクセルのフッタやヘッダーについて 3 2023/02/04 09:45
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
EXCELで○ヶ月を○年○ヶ月に変換...
-
リンク元の日付が空白の時リン...
-
Excelのセルに数字を入れるが、...
-
エクセルの曜日表示が正しく表...
-
EXCELで勝手に約分されてしまう
-
エクセルでアポストロフィーが...
-
エクセル 日付の表示を200...
-
Excelで「20080707」を『2007/0...
-
Excel 2010で軸の書式設定の変...
-
エクセルで9と入力したら時刻...
-
西暦から、曜日を表示⇒何も入力...
-
エクセルで年だけ表示させるこ...
-
Excelのセルの書式設定(100...
-
エクセル 4ケタの数字を年月に...
-
Excel 時間の表示形式 セルと...
-
【エクセル】数値にスラッシュ...
-
Excelの時間表示
-
時刻表示を秒数に換算する関数...
-
【Excel】24:00と表示したい!
-
Excel2013 表示形式で「1,000...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
EXCELで○ヶ月を○年○ヶ月に変換...
-
リンク元の日付が空白の時リン...
-
エクセルの曜日表示が正しく表...
-
【エクセル】数値にスラッシュ...
-
Excel2013 表示形式で「1,000...
-
Excelで「20080707」を『2007/0...
-
Excelのセルに数字を入れるが、...
-
エクセルでアポストロフィーが...
-
斜線が入ったセルのカウント
-
EXCELからCSVへ変換すると0が...
-
エクセルで日付を全角にしたい
-
西暦から、曜日を表示⇒何も入力...
-
エクセルで9と入力したら時刻...
-
EXCELで勝手に約分されてしまう
-
エクセル 4ケタの数字を年月に...
-
Excel 時間の表示形式 セルと...
-
Excelのセルの書式設定(100...
-
EXCEL (0.5)の表示の仕方
-
エクセル 日付の表示を200...
-
Excel時間の入力方法
おすすめ情報