出退勤管理の遅刻・早退時間について

初めて利用させていただきます。
会社でフレックスタイム制を導入するにあたり出退勤表のエクセルも修正することになりました。
コアタイムが10:00～15:00で、午前休みの場合13：00から勤務、午後休みの場合12：00まで勤務となります。
現在遅刻時間と早退時間の関数の入力にてこずっており詳しい方にご教示いただきたいです。

遅刻時間(1分単位に表示できるようにしたい)のエクセルは現在下記の関数を入力しています。(画像のC22)
=IF(C25="午前休",IF($G$4<C15,MINUTE(C15-"13:00")),IF($B$5<C15,MINUTE(C15-"10:00")))
この関数ですと10：59までは分単位で表示されますが、11:00以降の遅刻は分単位でしか表示されないので10:00から11:00の１時間は表示されなくなります。

どのように関数を設定すれば正しく表示されますでしょうか？
よろしくお願いいたします。

質問者からの補足コメント

• 画像が大きすぎて見えなかったためアップロードしなおします。
  画像のC22セルの関数を下記に設定しています。
  =IF(C25="午前休",IF($G$4<C15,MINUTE(C15-"13:00")),IF($B$5<C15,MINUTE(C15-"10:00")))
  この関数ですとMINUTE関数で入力しているため、11時15分に出勤したとしても正しい75分ではなく15分と表示されてしまいます。
  
  コアタイムである10:00を過ぎた場合C22セルに遅刻時間を表示するように、
  午前休みの場合は13:00時を過ぎた時間を1分単位で表示されるようにしたいです。
  
  わかりずらくて言葉足らずかもしれませんがご教示よろしくお願いいたします。

  
    補足日時：2023/08/10 17:04

No.3 ベストアンサー 回答者： goomania 回答日時： 2023/08/11 17:47

EXCELでは時間を扱うとき、1日を1としたシリアル値を用いて計算を行うという原則があります。

1日が1ですから、時間計算は殆ど小数の計算になります。

EXCELでは小数点計算で誤差が発生する可能性があることが知られています。例えば1分はシリアル値では1/1440ですから、2進数では循環小数になってしまいます。循環小数ということは一定の桁数内で表記できないことを意味しますから、場合によっては近似値計算になってしまうということになります。

ご質問の解決策としては以下の2通りの考え方がありますが、上記の理由から、シリアル値の計算、つまり小数での計算はできるだけ避けたほうがよいかも知れません。
(1)シリアル値を我々が当該単位の時間と認識できるように、整数値に変換して計算し、そのまま表示する(数値そのものを変える)
(2)シリアル値を用いた計算を踏襲し、シリアル値を我々が認識できる当該の時間単位に変換して表示する(数値は変えず見せ方を変える)

ご質問者のもともとの数式もNo.2さんの回答も(1)の考えに基づくものです。No.2さんの回答は、1時間単位で計算してから60を掛けて時間単位から分単位に変換した整数値を表示するというものです。
もともと、ご質問者の数式は分単位で計算する意図のようですから、(1)の考え方でよいなら、もとの数式からMINUTEを外して、1440を掛けてしまうという方法、つまり、C22セルの数式は、
=IF(C25="午前休",IF($G$4<C15,C15-"13:00"),IF($B$5<C15,C15-"10:00"))*1440･･･①
という数式でよいことになります。

(2)のの考え方で進めるなら、C22セルの数式は、
=IF(C25="午前休",IF($G$4<C15,C15-"13:00"),IF($B$5<C15,C15-"10:00"))･･･②
という数式にしておき、表示形式を「[m];;」というユーザー定義を用いることになります。

しかし、ここで問題があります。C15の値が10:00丁度、あるいは10:00前の時刻、例えば9:59だったら②の数式は「FALSE」を返し、後々のセル参照や判別式内の引用で支障が出ます。

恐らく、C14に出勤時間(タイムカード)という項目があるのに、C15に出勤時間という項目があったり、C16に退勤時間(タイムカード)という項目があるのに、C17に退勤時間という項目があるのは、上記のようにFALSEとならないようにタイムカードの時刻を補正するための項目なのではないかと思います。

このような補正セルを用いなくても、①の数式を
=IF(C25="午前休",IF($G$4<C14,C14-"13:00",0),IF($B$5<C14,C14-"10:00",0))*1440･･･③
という数式にするとか、②の数式を
=IF(C25="午前休",IF($G$4<C14,C14-"13:00",0),IF($B$5<C14,C14-"10:00",0))･･･④
という数式にすれば、補正セルであるC15はいらなくなるのではないかと思います。
※FALSEは四則演算では「0」として扱われるので「=IF(C25="午前休",IF($G$4<C14,C14-"13:00"),IF($B$5<C14,C14-"10:00"))*1440」でもOK

さらに言えば、せっかくB5セルにコアタイムの開始時刻、とG4セル午前休の場合の開始時刻を入力しているのに、"10:00"と"13:00"を数式中に固定値として記述してしまうのはもったいないことです。

コアタイムの開始時刻、午前休の場合の開始時刻に万一変更が発生しても数式を修正せず、B5セルとG4セルの値を変更すれば済むようにすべきです。
すると数式は、③の場合で補正セルを使うなら、
=IF(C25="午前休",IF($G$4<C15,C15-$G$4,0),IF($B$5<C15,C15-$B$5,0))*1440･･･⑤
補正セルを使わず、タイムカードの時刻をそのまま用いるなら、
=IF(C25="午前休",IF($G$4<C14,C14-$G$4,0),IF($B$5<C14,C14-$B$5,0))*1440･･･⑥
という数式になります。

また、④の場合で補正セルを使うなら、
=IF(C25="午前休",IF($G$4<C15,C15-$G$4,0),IF($B$5<C15,C15-$B$5,0))･･･⑦
補正セルを使わず、タイムカードの時刻をそのまま用いるなら
=IF(C25="午前休",IF($G$4<C14,C14-$G$4,0),IF($B$5<C14,C14-$B$5,0))･･･⑧
という数式になります。

この回答へのお礼

goomania様
お礼が遅くなりました。
goomania様の回答してくださった⑤の数式でうまくいきました！！！
ここ数日一人で悩んでなかなか答えにたどり着かず困っていたためとても丁寧な説明と回答をいただき心から感謝します！！
goomania様の回答をもとに早退時間の数式を理解しつつ考えようと思います。
本当にありがとうございました！！！

お礼日時：2023/08/18 10:24

No.2 回答者： tatsumaru77 回答日時： 2023/08/11 11:46

C22へ

=IF(C25="午前休",IF($G$4<C15,HOUR(C15-"13:00")*60+MINUTE(C15-"13:00")),IF($B$5<C15,HOUR(C15-"10:00")*60+MINUTE(C15-"10:00")))

と入力してください。

この回答へのお礼

tatsumaru77様

お礼が遅くなりました。
とても困っていたためご回答いただき助かりました！
ありがとうございました！！
また何か困った時がありましたら相談させていただきます。

お礼日時：2023/08/18 10:15

No.1 回答者： fujillin 回答日時： 2023/08/10 16:26

こんにちは

添付の図がほとんど解読できません。
また、計算できない式を提示されても、何をなさりたいのかは読み取れません。

文章でも良いので、どのような入力値から、どのような結果を求めたいのかを、正しく伝わるようにすることが一番大切です。
図を添付するのなら、必要な範囲だけを切り取って、拡大した図にしないとここのサイトでは判別できません。
（大きな画像を添付しても、500×500pxサイズにされてしまうようです。）

そうすることで、適切な回答を得られやすいかと思います。
（私が回答できるとは限りませんけれど・・）

この回答へのお礼

fujillin様
不慣れなものでご指摘いただき助かります！ありがとうございます。

ご指摘いただいた内容をもう一度考えなおします。

お礼日時：2023/08/10 16:53