[[20050324174019]]　『勤務日数を自動算出････』（ぺけぽん）　ページの最後に飛ぶ

『勤務日数を自動算出････』（ぺけぽん）

はじめまして。
Excel2000を使って勤務表を作るよう指示されたのですが、超初心者で困っています。

１．先月のタイムカードを見ながら、B5からB35までに出勤時間を記入したら、
下のB37のセルに、出勤時間を記入したセルだけを数えて出勤日数として表示させるにはどうしたらいいんでしょうか？

２．出勤時間欄B5に8:08、退勤時間欄C5に18:43と入力した時、
私の会社は30分単位で残業を計算するので残業時間欄D5に1:30と表示させたいのです。

その隣にもう一つの残業時間欄E5があり、
そこには1:30を1.5に直した数字が出勤時間欄B5と退勤時間欄C5から自動的に算出されるようにしたいのですが、教えてください。

よろしくお願いします。

 B37は=COUNT(B5:B35)
 D5は=FLOOR(TIMEVALUE(TEXT(C5-"17:00","h:mm")),"0:30")
 E5は=FLOOR(TEXT(C5-"17:00","h:mm")*24,0.5)
でどうでしょうか？
（ケン）

 衝突しました。①は同じですが、②はこんな方法でもできそうです。

 ①B37=COUNT(B5:B35)
 ②17:00までが定時なら、D5=FLOOR(C5-"17:00","00:30")
                        E5=D5*24

 書式設定で、D5は時刻に、E5は数値にする。
 (sato)

ありがとうございます｡･ﾟﾟ･o（ｉДｉ）o･ﾟﾟ･｡解決できました！

追加で誠に恐縮なのですが

３．出勤時間欄B5に8:08、退勤時間欄C5に18:43と入力した時、
小計時間欄F5に8:30と表示させたいのです。

その隣にもう一つの小計時間欄G5があり、
そこには8:30を8.5に直した数字が出勤時間欄B5と退勤時間欄C5から自動的に算出されるようにしたいのですが、教えてください。

先ほど教えていただいた式を自分なりに応用しようと思いましたが、浅はかでした。
エラー以外表示されませんでした(ToT)

４．深夜勤務欄H5があり、22:29までは普通勤務時間ですが、22:30から6:00まで深夜勤務として退勤時間欄C5をもとに、自動的に深夜勤務欄H5に残業時間欄1:30のような形式で表示されるようにしたいのですが、どうすればいいのでしょうか？

５．深夜勤務欄に#######と出て、出勤時間・退勤時間を入力していない残業時間欄と小計時間欄に#VALUE!と出てしまいます。出ないようにしたいのですが、どうすればよいでしょう？何が原因で出ているのでしょうか？

長くなりますが申し訳ありません。よろしくお願いします。

 こちらが参考になりませんでしょうか。
[[20050309110820]] 『勤務時間の集計について』（らすかる）
 (sato)

sato様、ありがとうございます。

こちらに書かせて頂く際、事前に[20050309110820]こちらも拝見させていただいたのですが、私には難しすぎたようなのです。

ですが、再度見直し、ただいま勉強中です。

上記でお伺いした小計時間欄F5とG5なのですが、ケン様に教えて頂いた数式を

=FLOOR(TIMEVALUE(TEXT(C5,"h:mm"))*12,"0:30") と
=FLOOR(TEXT(F5,"h:mm")*24,0.5)

に換えて見たところ、うまく表示されました。
ただそれを下にズラララとコピーしていくとF6、G6からは18:30と18.5になってしまうのです。

数式は同じなのに何故なのでしょうか？

上記の式自体が間違っているのでしょうか？
どうかよろしくお願いします。

 =FLOOR(TIMEVALUE(TEXT(C5,"h:mm"))*12,"0:30") 
12を掛けているのは何故ですか？残業時間を求めるのですよね？
=FLOOR(TEXT(F5,"h:mm")*24,0.5)
F5を参照してるから、当然といえば当然ですよね？
F5を参照するのならsatoさん指摘の=F5*24でいいと思います。
（ケン）

ケン様、お返事ありがとうございます。

12にしたのは安直な考えで、12をかける意味はあまりよく分かっておらず、色んな過去ログを見て入力してみたところ8:30が表示されましたので使ったという次第です。

申し訳ないです。

今作成しているのは下記のような表です。

　　A 　　　B　　　　 C　　　　D　　　　 E　　　　 F　　　　 G　　　　H

　　　　　　出勤　　退勤　　残業　　残業　　小計　　小計　　深夜残業(22:00～6:00)

5 3月21日　 8:08　　 18:39　　1:30　　 1.5　　　8:30　　　8.5　　 #VALUE!

6 3月22日　 8:08　　 18:39　　1:30　　 1.5　　　　　　　　　　　 #VALUE!

7 3月23日

8 3月24日

9 3月25日

ご指摘の通りです。そうですよね、F5を参照すればF5が8:30だから当然8.5････(i_i)

ケン様に最初教えて頂いていた通り、小計時間もC5を参照して表したいのですが、どのようにすればよいでしょうか？(ぺけぽん)

 D5=IF(C5>TIMEVALUE("22:00"),TIMEVALUE("5:00"),FLOOR(--(TEXT(C5-"17:00","h:mm")),"0:30"))
 E5=D5*24
 F5=--TEXT(D5+"7:00","h:mm")
 G5=F5*24
 H5=IF(C5>TIMEVALUE("22:00"),FLOOR(--TEXT(C5-"22:00","h:mm"),"0:30"),"")
取りあえずこんな感じだとどうなりますか？検証は余りしてないので、
自信ありません。
（ケン）

ケン様、ありがとうございます。

D5、F5、G5うまくいきました！！ただE5が12:00と表示されるので最初に教えていただいた=FLOOR(TEXT(C5-"17:00","h:mm")*24,0.5)の式を入れております。

H5なのですが、私の説明不足でした(ﾟ∀ﾟ；)退勤時間欄C5に22:00と入力された時にH5で0、22:30と入力したときに0.5とでてほしいのです。よろしくお願いしますm(_ _)m
(ぺけぽん)

 E列に表示形式は標準等にして下さい。
H5の式は24を掛ければ数値化できます。同じく表示形式は標準に。
（ケン）

ありがとうございます！！

H5なのですが、退勤時間に22:29と入力すると、深夜勤務欄に「0.479166667」と出てしまいます(ToT)

　　A 　　　B　　　　 C　　　　D　　　　 E　　　　 F　　　　 G　　　　H

　　　　　　出勤　　退勤　　残業　　残業　　小計　　小計　　深夜残業(22:00～6:00)

5 3月21日　 8:08　　 18:39　　1:30　　 1.5　　　8:30　　　8.5　　

6 3月22日　 8:08　　 18:39　　1:30　　 1.5　　　　　　　　　　　

7 3月23日

8 3月24日　　　　　　　　　#VALUE! #VALUE! #VALUE! #VALUE!

9 3月25日

出勤時間、退勤時間を記入していない日の残業・小計欄に出た#VALUE!を出ないようにするにはどうしたらよろしいでしょうか？
よろしくお願いしますm(_ _)m（ぺけぽん）

 H5=IF(C5>TIMEVALUE("22:00"),FLOOR(--TEXT(C5-"22:00","h:mm"),"0:30")*24,"")
 =IF(COUNT(B5:C5)=2,IF(C5>TIMEVALUE("22:29"),TIMEVALUE("5:00"),FLOOR(--(TEXT(C5-"17:00","h:mm")),"0:30")),"")
などとしたらどうでしょう？
（ケン）

ありがとうございます！解決しました。

多分これで完成すると思うのですが･････(ﾟ∀ﾟ；)

　　A 　　　B　　　　 C　　　　D　　　　 E　　　　 F　　　　 G　　　　H

　　　　　　出勤　　退勤　　残業　　残業　　小計　　小計　　深夜残業(22:00～6:00)

5 3月21日　 8:08　　 18:39　　1:30　　 1.5　　　8:30　　　8.5　　

6 3月22日　 8:08　　 18:39　　1:30　　 1.5　　　8:30　　　8.5　　　　　　　

7 3月23日　 8:08　　 22:14　　5:00　　 0.208333　5:00　　0.2　

8 3月24日

9 3月25日

F列に22:00以降の小計が出ないのです。残業と小計の所に0.2と出てしまうのですが･････。

どうかよろしくお願いします。(ぺけぽん)

 小計は全ての残業時間なのですね。
=FLOOR(--TEXT(C5-"17:00","h:mm"),"0:30")
としたらどうでしょう？
（ケン）

E7セルとG7セルの表示が解決しません｡（ｉДｉ）

=FLOOR(--TEXT(C5-"17:00","h:mm"),"0:30")どこに入れればよろしいのでしょうか？

　　A 　　　B　　　　 C　　　　D　　　　 E　　　　 F　　　　 G　　　　H

　　　　　　出勤　　退勤　　残業　　残業　　小計　　小計　　深夜残業(22:00～6:00)

5 3月21日　 8:08　　 18:39　　1:30　　 1.5　　　8:30　　　8.5　　

6 3月22日　 8:08　　 18:39　　1:30　　 1.5　　　8:30　　　8.5　　　　　　　

7 3月23日　 8:08　　 22:14　　5:00　　 0.208333　12:00　　0.2　

8 3月24日 8:08　　 22:31　　5:00　　 5.0　　 12:00 12.0　　 0.5

9 3月25日

F7は=IF(COUNT(B5:C5)=2,IF(C5>TIMEVALUE("22:29"),TIMEVALUE("5:00"),FLOOR(--(TEXT(D5+"7:00","h:mm")),"0:30")),"") という、教えて頂いた数式にしたのですが、22:00となっていたところを深夜残業の時間となる22:30の手前までということで22:29にしてしまいました。

行8のように22:29までは残業欄に残業時間のみ表示されて小計欄に22:29までの小計が出されて、22:30を越えたら深夜残業欄Hに越えた時間が表示されるようにしたいのですが、可能でしょうか？

申し訳ありません。進んでいくとどんどん疑問が出てきて････。

どうかよろしくお願いします。

どうかよろしくお願いします。(ぺけぽん）

 こんな感じの表で良いですか?
http://ryusendo.no-ip.com/~ken/cgi-bin/uploader/src/0011.xls
（ケン）

 書いているうちにフォローがあったようですが、せっせと一杯書いたので・・・
 おじゃまします。　フォローがないようなので、私が解読できたところを書いてみます。
 残業は17:00～22:00の勤務時間とします。
 =FLOOR(TEXT(MIN(C5,"22:00")-MAX(B5,"17:00"),"h:mm"),"0:30")
 B5:C5が未入力、出勤時刻が22:00以降、退勤時刻が17:00より前の時は計算せずに空白とする条件を加え、
 D5=IF(OR(COUNT(B5:C5)<2,B5>=TIMEVALUE("22:00"),C5<TIMEVALUE("17:00")),"",FLOOR(TEXT(MIN(C5,"22:00")-MAX(B5,"17:00"),"h:mm"),"0:30"))
 表示形式は「h:mm」とします。

 E列の残業は=D5*24　で、D5セルが空白でないときは計算し、空白時は空白を返すように
 E5=IF(D5<>"",D5*24,"")　として、表示形式は「標準」

 深夜残業は残業と同じように計算しますが、表示が数値とのことで*24を加え、表示形式は「標準」で、
 =IF(OR(COUNT(B5:C5)<2,B5>=VALUE("30:00"),C5<TIMEVALUE("22:00")),"",FLOOR(TEXT(MIN(C5,"30:00")-MAX(B5,"22:00"),"h:mm"),"0:30")*24)
 ただし、退勤時刻は翌日の3:00なら、27:00と入力することとし、表示形式は「[h]:mm」とします。

 小計はどういう計算なのか理解できませんでした。
 出勤時刻～22:00(または退勤時刻)の勤務時間から2:00を引いた時間？と推測して、
 =TEXT(FLOOR(MIN(C5,"22:00")-B5-"2:00","0:30"),"h:mm")*1
 としたら、7,8行目が11:30となり計算の仕方が異なるようです。
 # 混乱するようでしたら、無視してください。(^^;)
 　　(Hatch)

ケン様、Hatch様、sato様

超初心者の私に、丁寧に教えていただきありがとうございました。

みなさんに教えていただいた内容で勤務表が仕上がりました｡･ﾟﾟ･o（ｉДｉ）o･ﾟﾟ･｡

(ぺけぽん)

コメント返信：

[ 一覧(最新更新順) ]

[[20050324174019]] 『勤務日数を自動算出････』（ぺけぽん） ページの最後に飛ぶ

[[20050324174019]]　『勤務日数を自動算出････』（ぺけぽん）　ページの最後に飛ぶ