Excelで勤務表

質問

Excel(2000)で勤務表を作ることになりました。

基本勤務時間が８時間
それ以降が残業時間

例えば8：30から20：00まで働いた場合、
基本に"8"　残業に"2.5"と自動的に入る式を入れたいのですがどうしたらよいでしょう。

また、月を変えるだけで日付、曜日が自動的に変えられる式も教えていただきたいです。

使う関数の説明も付けてもらえると嬉しいです。
宜しくお願いします。

回答No.5

shiga_3 · ベストアンサー

＃１です。
残業のところに0.5と表示したいんですが０になってしまいます。
＞時刻表示で0:30となるようになってました。0.5と表示するためには24をかけてやらないといけませんでした。失礼しました。

＞後、残業が０だった時に空欄にしたいんですができるでしょうか？
＃３さんのされている通り、=IF(OR(C3="",D3=""),"",・・・)で処理してやれば良いですね。

あと＃３の回答も参考にさせてもらいながら、ちょっと別の式を立ててみました。
Ｅ３を
=IF(OR(C3="",D3="",D3-C3<0),"",MIN(MIN(MAX(12-C3*24,0),(D3-C3)*24)+MIN(MAX((D3*24-13),0),(D3-C3)*24),8))
Ｆ３を
=IF(OR(C3="",D3="",D3-C3<0),"",MAX(MIN(MAX(12-C3*24,0),(D3-C3)*24)+MIN(MAX((D3*24-13),0),(D3-C3)*24)-8,0))
とします。
Ｃ３、Ｄ３のいずれかが空白の場合は空白、Ｄ３にＣ３より前の時刻を書き込んでしまった場合も空白。また12：00～13：00の間は必ず休みで、退社時刻や出社時刻がこの間にかかった場合は、12：00から退社時刻まで、または出社時刻から13：00までが休憩時間として総勤務時間から差し引かれる、という風にしてみました。ちょっと煩雑になっていますが、昼休み前と昼休み後を分けて総勤務時間を出しています。

それから、ついでに日付の表示の方も
Ａ３：Ａ３０までには日付（１～２８）を入力、Ａ３１に
=IF(OR($A$1="",$B$1=""),A3+28,IF(DAY(DATE($A$1,$B$1,A3+28))=A3+28,A3+28,""))
と入力して以下Ａ３３までコピー。
Ｂ３を=DATE($A$1,$B$1,A3)として以下Ｂ３０までコピー、書式を (aaa)に。Ｂ３１に
=IF(OR($A$1="",$B$1=""),"(　 )",IF(A31="","",DATE($A$1,$B$1,A31)))
と入力して以下Ｂ３３までコピー、Ｂ３：Ｂ３３の行揃えを中央揃えに
という風に変えてみました。
年・月欄のＡ１またはＢ１が空欄であれば、１～３１までの日付と、その横に空白の（　　）が表示されます。Ａ１、Ｂ１が記入されれば、その年・月に応じた日数と曜日が表示されます。
良かったら試してみてください。

お礼

返事遅くなりましたが、
なんとか完成いたしました。
有難うございます。

回答No.4

mshr1962

#3のmshr1962です。
>12：00～13：00
を除く分を追加するだけです。
E3(基本)=IF(OR(C3="",D3=""),"",MIN((D3-C3-AND(C3<1/2,D3>13/24)/24)*24,8))
F3(残業)=IF(E3="","",(D3-C3-AND(C3<1/2,D3>13/24)/24)*24-E3)

-AND(C3<1/2,D3>13/24)/24
で12:00～13:00を含む場合、1時間減らします。
AND関数は()内の複数の条件をすべて満たすとTRUE(1)を返します。
それを24で割ることで1時間に換算しています。
条件を満たさない場合はFALSE(0)となるので時間はそのままです。
12:30～17:30の場合は4.5ではなく5を返しますのでご注意を

お礼

返事遅くなりましたが、
なんとか完成いたしました。
有難うございます。

回答No.3

mshr1962

昼休みの設定はどうなるのですか？
考えないでいいなら
C3に8:30,D3に20:00として
E3(基本)=IF(OR(C3="",D3=""),"",MIN((D3-C3)*24,8))
F3(残業)=IF(E3="","",(D3-C3)*24-E3)
でできます。
IF文は判りますよね条件で表示する内容を変えるもの、今回は未入力の場合空白にしています。
MIN関数は参照する値の最小を返す関数です。
就業時間を24倍して整数にしたものと8を比べて小さいものを表示しています。
------------------------------------------------
年がA1,月がA2として
A3=IF(A1=MONTH(DATE($A$1,$A$2,ROW()-2)),"",DATE($A$1,$A$2,ROW()-2))
B3=A3
A3の表示形式をユーザー定義で d
B3の表示形式をユーザー定義で (aaa)
として下方にコピーしてください。
DATE関数はDATE(年,月,日)を日付形式(シリアル値)にする関数
ROW関数は行の位置を返しますA3の場合は3になるので1からはじめるため-2しています
MONTH関数は日付から月を抽出する関数です。
IF文で月が変わったら表示を消すようにしています。
表示形式 d は日付（シリアル値）の日だけを表示
表示形式 (aaa) は日付の曜日を漢字で表示します。

補足

有難うございます。

昼休み分忘れてました。
すいません。
12：00～13：00です。

回答No.2

neKo_deux

質問に書かれている事はすべて可能です。

Excelで作成・公開されているオンラインソフトがありますので参考にしてみては？

Excel勤務時間報告書2003(フリー版)
http://www.vector.co.jp/soft/win95/business/se286691.html
エクセル時給計算 Ver6
http://www.vector.co.jp/soft/win95/business/se285071.html

参考URL：

http://www.vector.co.jp/soft/win95/business/se286691.html

お礼

有難うございます。

式を参考にしたかったのですが、
式は表示されないんですね。

回答No.1

shiga_3

例えば
Ａ1が西暦、Ｂ１が月の記入欄とし、
３行目から３３行目までの３１行が日付と時間の記入欄で、
Ａ列から日付、曜日、出勤時刻、退社時刻、基本勤務時間、残業時間とＦ列まで割り当てるとします。
Ａ３：Ａ３３までには日付を入力しておきます。
Ｂ３を=DATE($A$1,$B$1,A3)として、その後セルを右クリック→セルの書式設定で分類をユーザー定義にして種類のところに「aaa」（“曜日”なし）または「aaaa」（“曜日”あり）と入力します。その後、Ａ３をＡ３３までコピーします。
なおＤＡＴＥ関数は=DATE(年,月,日)で日付や曜日を表示させる関数です。

Ｃ３：Ｄ３３は時刻入力欄ですが「8：00」のようにコロンで区切って入力すると、自動的に時刻の書式になりますが、もし初めに別の書式が設定されていたら時刻として認識されない可能性もありますので、そのときはセルの書式設定で分類が時刻になっているか確認してください。

あとは
Ｅ３を=IF(D3-C3<9/24,D3-C3,8/24)
Ｆ３を=IF(D3-C3>9/24,D3-C3-9/24,0)
として、それぞれＥ３３、Ｆ３３までコピーします。
なお、勤務時間＝退社時刻－出勤時刻－１時間（休憩時間）として計算しています。
関数の意味は
Ｅ３：もしD3-C3が9/24日（９時間）未満ならば,D3-C3,そうでなければ8/24日（８時間）とする（Ｃ３、Ｄ３の値の単位は実際は「日」なので「時間」に直すために２４で割っています）
Ｆ３：もしD3-C3が9/24日（９時間）より大きければ,D3-C3から9/24日（９時間）を引く,そうでなければ０とする。
ということになります。

それぞれの関数についてはＥｘｃｅｌのヘルプで関数名で検索するとより詳しく分かると思います。

補足

どうも有り難うございます。

この式を入れると8：30～18：00働いたとすると
残業のところに0.5と表示したいんですが０になってしまいます。
なぜでしょう？

後、残業が０だった時に空欄にしたいんですができるでしょうか？