エクセルでタイムカードの遅刻回数を数えたい

質問

エクセルでタイムカードを作っているのですが、遅刻、早退の回数や時間を入れる関数を教えてください。
（補足）
・出社、退社時間の入力方法が「○：○」ではなく「○．○」とドットで入力するようになっています
・外出時間は、手動で入力になっています（30分外出→0.5、15分外出→0.25、1時間外出→1）
・F列に「1」と入れるとG列に「遅出」、「2」と入れると「夜勤」と出るようにVLOOKが入っています

（入れたい関数）
・B14～E14→遅刻、早退、外出などの回数
・B15～D15→遅刻、早退、外出、左記の合計時間
・B16～E16→例）0：15なら0.25、0：30なら0.5となるようにしたい（15行目に*24をすればOK?)
・Ｄ列→その日の、遅刻、早退の時間合計

（就業規定時間）
定時（8:30～17:15）、遅出（13:00～21:45）、夜勤（20:00～28:45）です。
上記以外の時間に出退社した場合が遅刻、早退になります

回答No.5

KURUMITO

Ｉ列からＬ列までの作業列を作ってこちらが最初に示した式を使って操作を行っていませんね。
Ｂ14セルの式にしてもお示しの式では全く正常な値とはなりません。こちらの示した通りの方法と式を使って操作を行わない限りできないでしょう。
Ｄ2からＤ13までの値にしてもそれらのセルの表示形式は時刻で設定してくださいと言っているにもかかわらずおかしな数値が並んでといっています。それらの値はシリアル値が表示されているのです。Ｄ2セルからＤ13セルを選択してから右クリックして「セルの書式設定」から「セルの表示形式」を選んでその中の時刻から0:30になるような表示法を選んでください。
出社や退社の時刻を8.30のような入力から8:30の入力に改めるといった場合にはこれまで説明してきた方法は全く適用できません。
こちらが説明した方法をそのままの形で採用することができないのでしたらこれ以上説明しても意味が有りませんね。

補足

この度は大変お世話になります。全くの素人ですので、最初に教えて頂いた通りのやり方と式、表示形式変更でやっているのですが、画像のような回答数字がでないのですが。教えていただいた内容で式を入れると、下記のような答えになってしまいます。
B14＝0、C14＝3、D14＝0、B15＝0:00、C15＝3:50、D15＝0:00、B16＝0、C16=3.8333333、D16＝0、D2～D13は、すべて0:00、K2～K13は、すべて8:30、L2＝1:00、L7=0:50、L11＝2:00
となっております。何度も、書かれた通りの内容で操作し、関数もこちらにかかれているのをコピペして使用しています。お忙しい中、本当に申し訳ございませんが、アドバイスのご協力をいただけると幸いです。

回答No.4

KURUMITO

式が抜けていましたので追加します。

=IF(B2="","",(INT(TRIM(B2))&":"&ROUND(MOD(TRIM(B2),1)*100,0)&":00")*1)

なお、前任者が時間の表示でドットを使っていたのでそれを崩したくないとのことで、出社や退社の時間の入力はそのままでよいにしても作業列を設けて対応することや、不就業時間を通常の:を使った時間表示にする、あるいはＢ15セルからＤ15セルの時間表示を:にするなどのことはエクセルを使って作業を進める上では当然のことでしょう。前任者の意向に沿った形の表示であることの方がむしろおかしいのです。あなたが改める方向でご検討ください。

補足

画像の内容に、アドバイス頂いた数式を入れるとD列も14行目から16行目までも、全ておかしな値が出てしまうのですが、B列、C列の入力を「.」ではなく「：」で入力しなおさないと、数式には反応しないということなのでしょうか？ちなみに、今、14行目から16行目まで、定時の時間に関しては、遅刻や早退の時間は拾えています。例えばB14には「=COUNTIF(B2:B13,">8.30")」と入れてあります。ただ、これだと、夜勤や遅出の時間まで遅刻と数えられてしまう為、単純にこの式に追加で「F列が1の場合は13.00以降の回数をカウントする+F列が2の場合は20.00以降をカウントする」みたいな数式ってできないのでしょうか？15行目の数式に関しても同様で「F列が1の場合は13.00以降に出社した場合の遅刻時間、+F列が2の場合は20.00以降に出社した場合の遅刻時間を表示」という感じにできれば幸いなのですが。

回答No.3

KURUMITO

回答Ｎｏ2です。
Ｂ2セルのデータが8.30（ドットが使われている）と入力されているのにＩ2セルではセルの表示形式を時刻から選んでも8:30のように表示されないということでしょうか？
Ｉ2セルに入力する式を次のようにしてＪ2セルまで横にドラッグコピーしたのちに13行目まで下方にもドラッグコピーしてみてください。

補足

D列に式を入れるとエラーになってしまうのですが、なぜでしょうか？何度もすみません。入力方法に関しては、私も入社したばかりで、変えたいのですが、それはダメと言われており、仕方ないんです。すみません。

回答No.2

KURUMITO

小数点を使った出社や退社の時間の入力はエクセルの機能を有効に利用する上では最大の障害になっています。出来れば入力を8:30のような形にすることが望ましいでしょう。
上記の問題を解消するために作業列を設けて対応することにします。
例えばＩ1セルに出社、Ｊ1セルに退社、Ｋ1セルに遅刻、Ｌ1セルに早退の文字列をそれぞれ入力します。
Ｉ2セルには次の式を入力してＬ2セルまで横にドラッグコピーしたのちに下方にもドラッグコピーします。

=IF(B2="","",(INT(B2)&":"&ROUND(MOD(B2,1)*100,0)&":00")*1)

セルの表示形式は「ユーザー定義」で種類の窓には　[h]:mm と入力してＯＫします。

そこでＤ2セルには次の式を入力してＤ13セルまで下方にドラッグコピーします。

=IF(COUNT(K2:L2)=0,"",SUM(K2:L2))

なお、セルの表示形式は上と同じようにして　0:30 のように表示させます。

Ｂ14セルには次の式を入力して右横方向にドラッグコピーします。

=IF(COLUMN(A1)<=2,COUNT(K2:K13),IF(COLUMN(A1)=3,COUNT($K2:$L13),IF(COLUMN(A1)=4,COUNT($E2:$E13),"")))

セルの表示形式は標準です。

Ｂ15セルには次の式を入力して右横方向にドラッグコピーします。

=IF(COLUMN(A1)<=2,SUM(K2:K13),IF(COLUMN(A1)=3,SUM($K2:$L13),""))

セルの表示形式は　[h]:mm にして　8:00のように表示します。

Ｂ16セルには次の式を入力して右横方向にドラッグコピーします。

=IF(COLUMN(A1)<=3,B15*24,"")

セルの表示形式は標準にします。

補足

コメントありがとうございます。実は「：」ではなく「.」を使うのは、テンキーを使って入力しやすいようにと、昔からのやり方のようで、変更できないのです（涙）なので、いただいた関数を入れてもエラーになってしまいます。何とか方法はないものでしょうか？ちなみに、不就業時間や、残業時間など他の部分は、「・」を使っていても自動計算をされ回答では「：」で表示されています。私は初心者で、前任の方が入れている関数を見てもさっぱりわからず、なるべく現状の表を崩さずに、追加で遅刻・早退の時間が出せないものかと調べています。よろしくお願いいたします

回答No.1

K Kazz（@JazzCorp）

＞・B14～E14→遅刻、早退、外出などの回数
・B15～D15→遅刻、早退、外出、左記の合計時間
・B16～E16→例）0：15なら0.25、0：30なら0.5となるようにしたい（15行目に*24をすればOK?)
・Ｄ列→その日の、遅刻、早退の時間合計
（就業規定時間）
定時（8:30～17:15）、遅出（13:00～21:45）、夜勤（20:00～28:45）です。
上記以外の時間に出退社した場合が遅刻、早退になります

セルごとにテイギせよ！
勤務コードを全てテイギせよ！
就業時間の合計はイランの金？