初めて利用させていただきます。
会社でフレックスタイム制を導入するにあたり出退勤表のエクセルも修正することになりました。
コアタイムが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はなんで先頭の0を消すん...
-
Excel元に戻す方法を教えてくだ...
-
【Microsoft Office Excel Comp...
-
Excelが固まってしまった。
-
西暦や和暦の表示をyyyymmdd表...
-
Excel 2019 のピボットテーブル...
-
【関数】スペースがいくつ入っ...
-
【Excel】セル内の時間帯が特定...
-
excelの不要な行の削除ができな...
-
Excelのオートフィル
-
別シートからの文字を変更
-
Excelのセルを飛ばして入力する
-
Excel初心者です。 詳しい方、...
-
エクセルの行の抽出について質...
-
Excel初心者です。 詳しい方、...
-
【マクロ】エクセルにかいてあ...
-
EXACT関数とIF関数の組み合わせ...
-
スプレッドシート クエリ関数 1...
-
エクセルで指定した日付、店舗...
-
Excelのグラフ軸について
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
ファイル内にある数字の出現回...
-
Excel関数の先頭に「@」が入っ...
-
エクセルの気味悪い不思議
-
Excel VBAで、実行時にsheet上...
-
表示されている人数だけを数え...
-
他人が作ったマクロの理解
-
Excelの関数について質問です。
-
Excel 集計表
-
エクセル 日時の計算式について
-
Excelの関数に関して質問です。...
-
エクセル:セル内の文字列の下...
-
絞り込み検索
-
エクセルの関数で
-
エクセルの書式設定について教...
-
余分なEXCELファイルに印刷され...
-
VBA 同一シート内での転記の仕方
-
長期休みの関数はありますか
-
Excelの空のセル
-
エクセルで入力してある文を別...
-
Excelのマクロで、セルを結合し...
おすすめ情報
画像が大きすぎて見えなかったためアップロードしなおします。
画像の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分単位で表示されるようにしたいです。
わかりずらくて言葉足らずかもしれませんがご教示よろしくお願いいたします。