初めて利用させていただきます。
会社でフレックスタイム制を導入するにあたり出退勤表のエクセルも修正することになりました。
コアタイムが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の1時間は表示されなくなります。
どのように関数を設定すれば正しく表示されますでしょうか?
よろしくお願いいたします。
No.3ベストアンサー
- 回答日時:
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様の回答をもとに早退時間の数式を理解しつつ考えようと思います。
本当にありがとうございました!!!
No.2
- 回答日時:
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様
お礼が遅くなりました。
とても困っていたためご回答いただき助かりました!
ありがとうございました!!
また何か困った時がありましたら相談させていただきます。
No.1
- 回答日時:
こんにちは
添付の図がほとんど解読できません。
また、計算できない式を提示されても、何をなさりたいのかは読み取れません。
文章でも良いので、どのような入力値から、どのような結果を求めたいのかを、正しく伝わるようにすることが一番大切です。
図を添付するのなら、必要な範囲だけを切り取って、拡大した図にしないとここのサイトでは判別できません。
(大きな画像を添付しても、500×500pxサイズにされてしまうようです。)
そうすることで、適切な回答を得られやすいかと思います。
(私が回答できるとは限りませんけれど・・)
fujillin様
不慣れなものでご指摘いただき助かります!ありがとうございます。
ご指摘いただいた内容をもう一度考えなおします。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Excel(エクセル) 出退勤管理の早退時間について 4 2023/08/21 15:22
- 仕事術・業務効率化 IF関数で時間指定をして、数値を切り上げたいです 1 2022/05/01 23:37
- Excel(エクセル) マクロ/VBAについて教えてください。 10 2022/05/27 12:59
- その他(教育・科学・学問) エクセル関数について 2 2022/12/23 08:59
- その他(Microsoft Office) 勤務表のエクセル作成で数式を教えてください。 1 2023/01/17 03:27
- Excel(エクセル) TEXT関数(負の値)を集計のため数値に変換したい 5 2022/05/15 23:04
- 労働相談 勤務条件の話が違ので試用期間中に辞められるか 2 2022/11/30 08:07
- Excel(エクセル) Excel2019、2021の売り上げなどの集計表について 4 2022/11/29 14:03
- Excel(エクセル) Excel関数で日またぎの勤務時間にしるしを立てる 2 2022/04/20 17:22
- Excel(エクセル) エクセルの自動更新のタイミングについて 1 2022/07/20 16:12
このQ&Aを見た人はこんなQ&Aも見ています
関連するカテゴリからQ&Aを探す
おすすめ情報
このQ&Aを見た人がよく見るQ&A
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
ある表にフィルターをかけて出...
-
Excelのセルの色を変えた行(す...
-
エクセルの数式について教えて...
-
Excelでの記号と特殊文字につい...
-
Lookup関数
-
エクセルの数式バーのフォント...
-
MACにおけるエクセルの入力トラ...
-
IFとIFS関数
-
セルの数を求めたい
-
Excelを無料で使うには? パソ...
-
再質問です。マクロの修正箇所...
-
【マクロ】VLOOKUPにて参照元に...
-
データチェックを行うエクセル...
-
【ExcelVBA】名前を付けて保存→...
-
EXCELの散布図で日付が1900年に...
-
エクセルで「ページレイアウト...
-
スプレッドシートの関数につい...
-
エクセルでファイルの最終更新...
-
エクセルの問題です。絶対値の...
-
エクセルを使っていて2024/5/15...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
半角カタカナをヘボン式ローマ...
-
(マクロ)vlookupの元データを同...
-
エクセルで上位バイトのセルと...
-
exselの質問です
-
Excel 大小比較演算子による「...
-
Excel VBについての質問です。
-
エクセルの問題です。絶対値の...
-
非表示列の再表示に失敗
-
職場の人から聞かれており、こ...
-
Excel関数-文字列で自動作成さ...
-
Excelデータをコピペして、ペー...
-
ユーザー定義関数をアドイン登...
-
【マクロ】for next構文について
-
エクセルの日付を編集する
-
【マクロ】VLOOKUPにて参照元に...
-
exselで最小数で並び替える関数
-
libre 表計算ソフトの計算がう...
-
エクセルで表
-
エクセルの表で1年間の曜日を...
-
西暦和暦
おすすめ情報
画像が大きすぎて見えなかったためアップロードしなおします。
画像の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分単位で表示されるようにしたいです。
わかりずらくて言葉足らずかもしれませんがご教示よろしくお願いいたします。