Excel：時間有給の正当性を判断する方法

前へ 次へ
このQ&Aのポイント
  • 勤務表に時間有給を導入する際、正しい入力を判断する方法について教えてください。
  • 有給時間の開始または終了時間の入力がうまくいかない場合、条件付き書式を使用して間違いを知らせたいです。
  • 勤務時間の頭または尻尾に時間有給の時刻を入力させ、正当性を確認したいです。
回答を見る
  • ベストアンサー

Excel：入力した時間が正当か判断する方法

勤務表を作成するにあたり、時間有給を導入する改変を行いました 入力は、勤務時間（開始と終了）と休憩時間 それに、有給時間の開始と終了を追加するというものです（添付画像参照） そこで 有給時間の開始または終了時間を入力した時のチェックをお行いたいのですが、どうもうまくいきません 有給開始時間：勤務時間内はNG、勤務終了時間はOK 有給終了時間：勤務時間内はNG、勤務開始時間以前もNG ようするに 勤務時間■の頭か尻尾に時間有給の時刻▢を入力させたいわけです イメージ：　▢▢■■■■■■　または　■■■■■■▢▢ 　　　　　　■■■■■▢▢■　はNG そんな入力をされたら、条件付き書式で間違っていることを知らせたいです（添付ファイルは誤った記入例） どのようにすれば、実現できるでしょうか？

画像を拡大する

質問者が選んだベストアンサー

  • ベストアンサー
  • kon555
  • ベストアンサー率52% (1748/3350)
回答No.3

>>私の考えと同じで、条件書式に作ったからダメだったのか？ 　多分何らかの設定ミスや入力ミスだとは思います。ただこのあたりの切り分けのためにも、別個に判定セルを置くのがオススメでもあります。 （判定部分のミスか条件付き書式のミスかすぐ分かる）

hooue100
質問者

お礼

別枠で以下のような数式を設定し、それを条件付き書式で見るようにすると、うまく動いてくれているようです ありがとうございました =IF(OR(ISBLANK(H10),OR(H10=D10,H10<C10)),1,0)　開始時間

hooue100
質問者

補足

そうですね そのほうがわかりやすいし、メンテしやすい 肝心の条件書式はどうしたらいいか？ 有給開始時間：勤務時間内はNGだけど、勤務終了時刻だけはOK 有給終了時間：勤務時間内はNGだけど、勤務開始時刻だけはOK これでいけるだろうか？

その他の回答 (3)

  • f272
  • ベストアンサー率46% (8005/17110)
回答No.4

#2です。すみません。 (有給終了時刻-勤務開始時刻)*(有給開始時刻-勤務終了時刻)≧0 の間違いでした。

hooue100
質問者

お礼

正しいパターンでは必ず有給時間は、勤務開始または終了のどちらかと一致するから0になり正しいというわけですね それ以外はどんな数字（負の数字でも）になろうが片方が0になれば、ANDだから0になるという理論？

hooue100
質問者

補足

数式にしたのですが、うまくいきませんでした 別枠で、以下の数式（有給開始時間）を設定して、それを条件書式で見る 方法で行きます（正常動作確認） =IF(OR(ISBLANK(H10),OR(H10=D10,H10<C10)),1,0)

  • f272
  • ベストアンサー率46% (8005/17110)
回答No.2

日をまたぐような入力がないこと 有給開始時刻＜有給終了時刻と入力されていること 勤務開始時刻＜勤務終了時刻と入力されていること を前提とすれば (有給終了時刻-勤務開始時刻)*(勤務終了時刻-有給開始時刻)≧0 となっていればOK

hooue100
質問者

補足

ありがとうございます しかし、その前提はあっても この条件では勤務時間内に有給時間が入力されても 正の数になり、要求は満たさない 10時～18時の勤務で、13時～15時の有給だと 5＊5＝25となる 勤務時間中の有給はNGです

  • kon555
  • ベストアンサー率52% (1748/3350)
回答No.1

　条件付き書式だけでやるよりも、if関数で判定セルを作った方がいいと思います。条件付き書式を作り込むと、何か変更になった時に修正が面倒なので。 　Excelではif関数と比較演算子で簡単に時刻の前後判定ができますから、それで計算させてしまえば判定は可能です。 　例えば条件ないなら〇、違反していれば×と表示させるようにif関数を組んで、そのセルが×なら赤くするように条件付き書式で設定、というのがベターです。 https://daitaideit.com/excel-time-compare/

hooue100
質問者

お礼

いいアイデアありがとうございます たしかに、面倒ですね 段階的にやってやったほうが、変更は数式だけですみますね ところで リンク先を閲覧すればわかるかと思ったのですが 私の考えと同じで、条件書式に作ったからダメだったのか？

  1. カテゴリ
  2. [技術者向] コンピューター
  3. 業務ソフトウェア
  4. その他(業務ソフトウェア)

関連するQ&A

  • Excel：勤務時間の計算がうまくいかない。

    No.1468024の質問をした者です。 勤務表を作成しています。（Excel2002を使用） 終了時刻（D列）ー開始時刻（C列）ー休憩時間（E列）=当日の勤務時間を計算しています。 No.1468024の質問でアドバイスをいただき、以下のような計算式を記述しました。 ＜例＞ =AND(C13*D13)*(MIN(IF(AND(D13>="12:00"*1,D13<="13:00"*1),"13:00",D13),"17:40")-MAX(IF(AND(C13>="12:00"*1,C13<="13:00"*1),"12:00",C13),"09:00")-E13+FLOOR(MAX(D13,"18:00")-"17:59:59","0:15")) ほぼうまくいくのですが、開始時刻9:00終了時刻12:00とした場合、勤務時間4:00となり、間違ってしまいます。（正しくは勤務時間3:00。また開始時刻9:00終了時刻が12時から13時までの間の時刻で入力された場合も勤務時間3:00と出力したいです。） 休憩時間セルに以下のような計算式を書いています。 ＜例＞ =IF(COUNT(C13:D13)<2,0,IF(D13-"12:00"*1<=0,0,IF(C13-"12:00"*1>=0,0,IF(D13<"18:00"*1,"1:00","1:20")))) 休憩時間は12時から13時までの1時間。 終了時刻が17:40以降なら20分休憩時間が加算されます。 開始、終了が入力されていないと0:00表示。 言葉足らずでしたら、進んで補足させていただきますので、よろしくお願いします。

  • Excel：勤務時間計の計算式がうまくいかない

    Excel2002を使用し、勤務表を作成しています。 以前こちらでアドバイスをいただき、作成することができました。 今回修正で、開始8:30終了17:05の場合、休憩45分、当日勤務時間7時間 50分と計算させたいのですが、何かアドバイスあればよろしくお願いし ます。 現在使用している計算式では以下のように計算されます。 開始8:00終了16:35の場合、休憩45分、当日勤務時間7時間50分 開始8:30終了17:05の場合、休憩60分、当日勤務時間7時間35分 ■8時-16時35分（定時）の場合 開始時刻：C列 終了時刻：D列 開始時刻を15分単位で切り上げる：P列：=CEILING(C7,"0:15") 終了時刻を15分単位で切り上げる：Q列：=CEILING(D7,"0:15") ■休憩時間 =IF(COUNT(C7:D7)<2,0,IF((P7<"12:30"*1)*(Q7>"13:15"*1),"0:45","0: 00"))+IF(D7>="16:50"*1,"0:15","0:00") 休憩時間は12:30から13:15までの45分。 終了時刻が16:35以降なら15分の休憩時間が加算。 開始、終了が入力されていないと0:00表示。 ■当日 勤務時間 =AND(C6*D6)*(MIN(IF(AND(D6>="12:30"*1,D6<="13:15"*1),"12:30",Q6), "16:35")-MAX(IF(AND(C6>="12:30"*1,C6<="13:15"*1),"13:15",P6),"08 :00")-IF((P6<"12:30"*1)*(Q6>"13:15"*1),"0:45","0:00")+FLOOR(MAX (D6,"16:50")-"16:49:59","0:15")) 開始、終了が入力されていないと空白 12:30～13:15の場合は12:30、16:35以降の場合は16:35、それ以外は終 了時刻のまま。 8:00以前は8:00、12:30～13:15の場合は13:15、それ以外は開始時刻の まま。 残業していないケースをIF文を使わずに計算したいため終了時刻が 16:50以前の場合は16:50と補正して、計算結果が00分になるように細工 。 言葉足らずでしたら、進んで補足させていただきますので、よろしくお 願いします。

  • Excel：特定の条件でFLOOR関数を使うには？

    Excelを使って、勤務表を作成しています。 開始時刻、終了時刻を入力すると、 終了時刻ー開始時刻ー休憩時間という具合に当日の勤務時間を出力させるようにしています。 当日の勤務時間を出力するセルには以下の条件が必要です。 (1)開始、終了時刻が入力されていない時、0:00と表示する。 (2)開始時刻8:30（もしくはその時刻より前）、 終了時刻17:05と入力された時、7:50と表示する。 (3)(2)の条件以外は15分単位で勤務時間を表示する。 15分単位で丸めるということでFLOORを使うのですが、 そうすると(2)の条件がまったく無視されてしまい、うまくいきません。 FLOORを特定の条件の時のみ、適用する方法があれば教えて下さい。 よろしくお願いします。 使用ソフト：Excel2002 ------------------------------------------------- ちなみに以下のような計算式を当日勤務時間が出力されるセルに書いて試してみました。 =IF(D9="17:05","7:50",IF(COUNT(C9:D9)<2,"0:00",FLOOR(D9-P9-E9,"0 :15"))) 計算式の意味↓ 終了時刻に17:05と入力されたら、当日勤務時間のセルに7:50と表示。 開始、終了が入力されないと、0:00と表示。 そうでなければ、終了ー開始(8:30以前を8:30と考えるセル)ー休憩で計算し、15分単位で表示。 とこのように書いてみたものの…うまくいきませんでした。

  • Excel：当日勤務時間の計算式のアドバイスを下さい

    Excel2002を使用し、勤務表を作成しています。 修正を繰り返しだいぶ計算ができるようになりました。 今回は、開始13:15終了16:15の場合、休憩なし、当日勤務時間3時間と計算させたいのですが、何かアドバイスあればよろしくお願いします。 現在使用している計算式では以下のように計算されます。 開始8:30終了17:05、休憩45分、当日勤務時間7時間50分※定時 開始7:30終了16:05の場合、休憩45分、当日勤務時間7時間50分 開始8:00終了16:35の場合、休憩45分、当日勤務時間7時間50分 開始13:15終了17:05の場合、休憩なし、当日勤務時間3時間50分 開始13:15終了17:50の場合、休憩なし、当日勤務時間3時間50分 -------------------------------------------------------- 開始時刻：C列 終了時刻：D列 開始時刻を15分単位で切り上げる：P列：=CEILING(C6,"0:15") 終了時刻を15分単位で切り上げる：Q列：=CEILING(D6,"0:15") 休憩時間を45分として勤務時間を計算する：R列 -------------------------------------------------------- ■休憩時間 =IF(COUNT(C6:D6)<2,0,IF((P6<"12:30"*1)*(Q6>"13:15"*1),"0:45",IF ((C6>="13:15"*1)*(D6>="17:20"*1),"0:15","0:00")))+IF(R6>="8:00"*1,"0:15","0:00") ■当日 勤務時間 =IF(COUNT(C6:D6)<2,0,IF((C6>="13:15"*1)*(D6>="17:20"*1),R6-"0:15",IF(R6>="8:00"*1,R6-"0:15",R6))) ■休憩時間を45分として勤務時間を計算する =IF(COUNT(C6:D6)<2,0,AND(C6*D6)*(MIN(IF(AND(D6>="12:30"*1,D6<="13:15"*1),"12:30",Q6),"16:05")-MAX(IF(AND(C6>="12:30"*1,C6<="13:15"*1),"13:15",P6),"07:30")-IF((P6<"12:30"*1)*(Q6>"13:15"*1),"0:45","0:00")+FLOOR(MAX(D6,"16:05")-"16:04:59","0:15")))

  • Excel：特定の時刻から15分単位で表示させるには？

    勤務表を作成しています。 終了時刻ー開始時刻ー休憩時間という具合に当日の勤務時間を出力させるようにしています。 当日の勤務時間を出力するセルには以下のような条件で計算式を書いています。 ＜例＞ =IF(COUNT(C13:D13)<2,0,IF(AND(C13<="9:00"*1,D13>="17:40"*1,"18:1 4"*1>=D13),"7:40"*1,IF(AND(C13>="12:00"*1,"13:00"*1>=C13,D13>="1 7:40"*1),"4:40"*1,IF(AND(D13>="12:00"*1,"13:00"*1>=D13),"3:00"*1, FLOOR(D13-P13-E13,"0:15"))))) (1)開始、終了時刻が入力されていない時、0:00と表示。 (2)開始時刻9:00（もしくはその時刻より前）、 終了時刻17:40から18:14の間の時刻で入力された時、7:40と表示する。 (3)開始時刻12:00から13:00の間の時刻、 終了時刻17:40で入力された時、4:40と表示する。 (4)終了時刻12:00から13:00の間の時刻で入力された時、3:00と表示す る。 (5)(1)～(4)の条件以外は終了時刻ー開始時刻ー休憩時間という計算を 行い、15分単位で丸める。 (1)～(4)の条件は変更なしで、(5)の条件を変えます。 (5)終了時刻が18:15以降、当日勤務時間が7時間40分以上になる。 7時間40分から15分単位で丸める。 ＜例＞ 開始時刻9:00、終了時刻17:40の時、勤務時間7時間40分。（定時） 開始時刻9:00、終了時刻18:14の時、勤務時間7時間40分。 開始時刻9:00、終了時刻18:15の時、勤務時間7時間55分。 （一番上に書いたの計算式だと勤務時間が7時間45分になる） 開始時刻9:00、終了時刻18:16の時、勤務時間7時間55分。 ※P13は9:00以前の開始時間を9:00と考えさせる、 12-13時の時間を13時と考えさせる、 上記以外はそのまま表示させるという計算式を書いたセル。 (5)のような条件を加えた計算式が可能であれば教えて下さい。（アドバイス、ヒントでもかまいません。） よろしくお願いします。 使用ソフト：Excel2002

  • 複数の休憩時間がある場合の休憩時間の算出方法

    Excel で勤務表を作っていますが、 休憩時間が複数ある場合の休憩時間の算出方法が分かりません。 D5 10:00 (出勤時刻) E5 19:00 (退勤時刻) F5 休憩時間 H5 =E5-D5-F5 (実稼働時間) D6 12:00 (出勤時刻) E6 22:00 (退勤時刻) F6 休憩時間 H6 =E6-D6-F6 (実稼働時間) 休憩時間1 D38 12:00 (休憩開始時刻) E38 13:00 (休憩終了時刻) F38 1:00 (休憩時間1のトータル) 休憩時間2 D39 17:15 (休憩開始時刻) E39 17:30 (休憩終了時刻) F39 0:15 (休憩時間2のトータル) 休憩時間2の休憩時間帯が変わってもカスタマイズできるように (休憩時間3、4、5と実際にはあります) D38,D39,E38,E39は使わず F38とF39を使って、F5、F6セルに、休憩時間を算出したいです。 また、13時以降の出社の場合は、12時から13時の休憩時間を含めないようにもしたいです。 どんな関数を用いれば実現可能でしょうか？ アドバイスよろしくお願い致します。

  • 【EXCEL】で残業時間を計算する方法

    1日の勤務時間が、8時間とされている場合の残業時間の算出方法をお知らせください。但し、勤務開始時刻及び終了時刻に決まりはありません。 【例】　　8月1日 出勤時間　10：35 退勤時間　21：45 休憩時間　　1:00（固定） 労働時間　11:10（出勤時間-退勤時間） 実働時間　10：00（労働時間-休憩時間） 残業時間　　2:10（実働時間-8時間） 決定残業時間　2:00（30分単位の切捨ての為） とした場合の、「残業時間」及び「決定残業時間」の求め方（式）をお知らせ頂けます様、お願いいたします。ただ、決定残業時間は、「-2」の場合もあります。これは早退した場合に該当しますが、これがネックになっております。どうぞ宜しくお願いいたします。

  • エクセルを使った残業時間の計算方法を教えてください

    エクセルを使って、就業開始時間と就業終了時間と休憩時間を入力すれば、自動的に残業時間(契約外時間)と契約内時間が表示されるようにしたいのですが、どういう計算式を使えばいいのかがわかりません。 添付した図の黄色セルの部分です。 定時が9時～17時半、休憩が12～13時の1時間です。 休憩を除いた勤務時間が7時間半を超えた分は残業としてカウントされます。 計算は15分単位です。 開始時刻が9時前になったり、休憩時間が増えたり、15分以下で繰り上げ／繰り下げ計算をしたりというような複雑になることはありません。 F3に7:30、F4に0:30、G6に7:30、G4に0:00と自動で表示させたいのですが、どうすればいいでしょうか。

  • Excel2010で時間を入力・計算する方法

    Excel 2010について、質問です。 会社で賃金台帳のデータ入力をしています。あらかじめ上司から渡された一覧表の内容を移す形で入力しているんですが、勤務時間等、時間の入力がうまくいきません。 例えば○月の勤務時間が144時間の場合、表には「144:00」と入力したいのですが、セルの書式設定で表示形式を「[h]:mm」とした場合、セルに「144:00」と入力すると「3456:00」になってしまいます。数式バーの表示は「1900/5/23 0:00:00」となっています。「表示形式⇒時刻⇒13:30」の場合はセルの表示は「0:00」で、数式バーは「1900/5/9 0:00:00」となります。 セルの表示形式を「0":"00」にした場合は、セルの表示は「144:00」になりますが、最後に勤務時間を合計すると、例えばA1に「1:30」、A2に「1:45」と入力した場合、A1+A2が「3:15」となるはずが、「2:75」となってしまいます。 セルの表示と、計算結果を正しく表示するにはどうしたら良いでしょうか。データ量が多いので、できればコロンを省略して入力したいです。 アドバイスをお願いします。

  • Excelで勤務時間の管理表を作成したい

    Excel初心者です。 Excelに詳しい方、どうかご教示いただきたく思います。 現在、タイムカードを元に、Excelで勤務時間の管理をしようとしていますが、 思うように出来ず、大変困っております。 作成したい勤務管理表としては、添付の画像のような形式になるのですが、 出社時刻も退社時刻も、昼休憩の開始時間や昼休憩の時間の長さも 人によってまちまちで特殊な働き方のため、管理が難しいです。 そのため、添付画像のように、 タイムカードの出社時刻と退社時刻を記入するだけではなく、 実際に業務を行った時間の内訳がわかるよう、 午前と午後の開始時間と終了時間の枠を設け(E列～J列)、 それを元に勤務時間合計(K列)を出すようにしています。 そこで、勤務が午前～午後にまたがり昼休憩を挟む場合は、 午前の終了時間(G列)と午後の開始時間(H列)は手入力をするようにし、 それ以外は、タイムカードの出社時刻(C列)と退社時刻(D列)が、 午前の開始(E列)および午後の終了(J列)の欄に反映されるようにしたいです。 なおその際に、以下の4点を考慮して作成したいと考えています。 (1)出社時刻(業務開始時間)は30分単位で切り上げる。 (2)退社時刻(業務終了時間)は30分単位で切り下げる。 (3)タイムカードの出社時刻欄(C列)や退社時刻欄(D列)に何も入力されていない場合は、 　午前・午後の開始欄・終了欄(E列～J列)は空欄にさせる。 　同様に、午前だけの勤務時は、午後の開始欄・終了欄(H列～J列)を空欄、 　午後だけの勤務時は、午前の開始欄・終了欄(E列～G列)を空欄にさせる。 (4)タイムカードの出社時刻(C列)に合せて、その時刻が午前ならば午前の開始欄(E列)に、 　午後ならば午後の開始欄(H列)に、(1)で丸めた時間を表示させる。 　同様に、退社時刻(D列)に合わせて、その時刻が午前ならば午前の終了欄(G列)に、 　午後ならば午後の終了欄(J列)に、(2)で丸めた時間を表示させる。 (※但し、添付画像の6行目の例の様に、午前だけの勤務の際に、終了時間が12時を過ぎていた場合は、午前の終了欄(G列)に表示させる。) 現在は勤務時間合計(K列)だけ計算式を入れており、それ以外は全て手入力のため、 手間がかかるだけでなく、入力ミス等にも繋がるため、 出来るだけミスの少ない管理表を作成したいと思っています。 条件の多い質問で大変恐縮ではありますが、 なるべく簡単な方法を教えていただけると大変助かります。 どうぞ宜しくお願い致します。

注目のQ&A

カテゴリ

専門家に質問してみよう

職業から探して質問する

あなたにピッタリな商品が見つかる！ OKWAVEセレクト

質問する