エクセルにてシフト表を作成しております。

質問

教えて下さい。

今バイトのシフト表を作成しておりますが、どうにも行き詰ってしまいました。
作成した表は
H5　開始時間
I5　 終了時間
J5～AG5　6時～29時まで1時間ごとにセル
AI　勤務時間
AJ　休憩時間
と有ります。

例えば
開始時間：8時
終了時間：19時
とした場合、会社にいる時間は11時間になりますので、
J5～AG5の6時～29時まで1時間ごとのセルの8時～18時までのセルを
勤務時間の数だけセルの色を変えたいのですが、うまく出来ません。

この場合11個のセルの色を変えることになります。色は紫にしたいです。

そこでIF関数を使って見ました。
開始時間はIF関数と条件付書式で希望通りに出来たのですが、
終了時間までの11セルの色を変える動作がうまく出来ません。

説明が下手で申し訳ありませんが、よろしくお願い致します。

回答No.3

tom04 · ベストアンサー

No.1です！
補足を読ませていただきました。

まず、セルの色について・・・
1時間ごとに切り捨てと考えれば良いわけですよね？
一応そういうコトだとすると、
FLOOR関数が使えると思います。
勤怠表の場合は15分ごとや30分ごとに切り捨てまたは切り上げの関数を使う場合があると思います。
切り捨ての場合はFLOOR関数が使用できます。
仮に15分毎に切り捨てる場合は
FLOOR(対象セル,"0:15") といった感じで使います。
切り上げの場合は
=CEILING(対象セル,"0:15")
といった感じです。（どちらも15分毎の場合）
これを利用すれば前回の条件付書式の数式欄に
=AND(J$4>=FLOOR($H5,"1:00"),J$4<FLOOR($I5,"1:00"))
としてみてはどうでしょうか？

次に
＞たとえば休憩が30分しか取れなかった場合、AJの休憩時間は0.3と入力するのですが、その場合勤務時間は10：70となってしまいます・・・

の件に関しては（5行目の数式になります）
表示したいセルに

=AI5-TIME(INT(AJ5/1),MOD(AJ5,1)*100,0)

といった感じではどうでしょう？

※　敢えて言えば、30分と0.3　と入力するより、　0：30　のようにすればもっと簡単になると思います。
いままでの説明に関してはセルの表示形式はすべてユーザー定義から
[h]:mm
としておきます。

最後に
＞勤務時間は、例えば8.25、8.50、8：75というような表示にするには・・・
に関しては
表示したいセルに

=総勤務時間*24
（この場合、セルの表示形式は「標準」にします）

でいけそうな気がします。
具体的にどのセルにどのように表示させるか判らないので、こちらで勝手に判断していますので、
的外れならごめんなさいね。m(_ _)m

お礼

ご回答有難う御座います！

おかげさまで理想の形になってきました！

実はうちは24時間営業なので、出勤表は24時ではなく29時まであります。
その場合、教えて頂いた数式で大丈夫かな？と思いましたが、
うまく動いてくれました♪

ユーザー定義の[h]:mmですと24時間の表示になるので、23：00からは
0：00、1：00なりますが、小さくコメントで24：00、25：00とAB3位のセルに表示するようにしました。

そして
開始、8：15
終了、28：00
と入力してすると、
L4、8：00～AF、28：00までが条件付書式で色が変わってくれました！
勤務時間、休憩時間につきましても、教えていただいたユーザー定義で
うまく動作してくれました！

この度は大変有難う御座いました！！
とても参考になりました～^^

回答No.2

noname#204879

添付図参照
セル J5 に次の[条件付き書式]を設定して、此れを右方および下方にズズーッとドラッグ＆ペースト
数式が　　　　=AND(J$4>=$H5,J$4<$I5)
パターン色　　紫
罫線の上辺　　白

回答No.1

tom04

こんばんは！
余計なお世話かもしれませんが・・・
質問ではキリが良い時刻になっていますけど、実際問題として、8時30分とか分単位の出退時刻があると思いますので、それにも対応できるようにしてみました。
当方使用のExcel2003での一例です。

↓の画像で数値のセルの表示形式ははすべて時刻にしています。
H・I列はユーザー定義から
[h]:mm
として　8：00　のように入力します。

J4～AG4セルの表示形式はユーザー定義から
[h]
として　6：00　7：00　・・・と入力しています。

色を付けたいセルを範囲指定　→　メニュー　→　書式　→　条件付書式　→　数式が　→　数式欄に
=AND(J$4>=$H5,J$4<$I5)
として　→　書式　→　パターン　→　「紫」を選択

これで画像のような感じになります。
参考になれば良いのですが・・・m(_ _)m

補足

ご回答有難う御座います！

大変参考になりました～
そうですね、仰るとおり、出勤時間は8：15とか15分刻みになります。

その場合、J5～AG5は出来れば8時のセルも色が付くようにしたいです。
8：15出勤なのに、セルは9時から色が付いていると、ちょっと分かりにくいので、
可能であれば、8：15も8：30も8：45も同じ”8時”という事で色は付かないでしょうか？

あと、AIの勤務時間なのですが、開始時間8時、終了時間19時の場合、単純に引き算をしているので、
勤務時間は11時間と表示されますが、たとえば休憩が30分しか取れなかった場合、AJの休憩時間は
0.3と入力するのですが、その場合勤務時間は10：70となってしまいます・・・

勤務時間は、例えば8.25、8.50、8：75というような表示にするにはどのようにしたら
宜しいでしょうか？

聞いてばかりで申し訳御座いませんが、何卒宜しくお願い致します。