勤務時間の設定方法

質問

いつもお世話になります。
WINDOWS XP EXCELL2003

出勤時間をA1に、退社の時間をB1に入れて勤務時間をC1にしたいのです
が。

出勤は
例えば８時出勤
０８:００～０８:０５　　０８:００の出勤と見なす

退社は
例えば１７:００退社
１６:５０～１７:００　　１７:００の退社と見なす

上は例でシフト制になっていて、人それぞれに出勤時間、退社時間は固定はされていません。

※　ところでこの場合

A1に０８:００～０８:０５と入力したときは０８００に、
B1に１６:５０～１７:００と入力したときは１７００に

なるような数式はどうすればいいですか。

ご指導いただけませんか。
宜しく御願いします。

回答No.6

KURUMITO · ベストアンサー

解答Ｎｏ４です。付属のご質問についてお答えします。
出勤時間については次の式を使います。
=IF(MOD(A1,"1:00")-"0:00:1"<="0:05"*1,FLOOR(A1,"0:30"),CEILING(A1,"0:30"))
ここでMOD関数にマイナスして0:00:1を使っているのは計算上の策です。
退社時間については次の式を使います。
=IF(MOD(B1,"1:00")+"0:00:1">="0:50"*1,CEILING(B1,"0:30"),FLOOR(B1,"0:30"))

お礼

クリモトさん、本当にやっかいなことを私のために頑張っていただいて誠に有難うございました。
いつもいつもお世話になります。
こんなに難しい数式はどなたかに甘えるしか手段がありません。

小生の力の及ばないところです。
今後とも宜しく御願いします。

テストをしてお礼が送れました。

回答No.5

cafe_au_lait

要するに、5分足したり10分引いたりしてから処理すればよいということでしょうか。

=FLOOR(B1+"0:10","0:30")-CEILING(A1-"0:05","0:30")

お礼

ご協力いただき有難うございました。
今後とも宜しく御願いします。

回答No.4

KURUMITO

出勤や退社の時間付近では細かな時間指定が必要のようですが、それ以外の時間になれば30分単位で出勤の時間では切り上げる、退社の時間では切り捨てることになるでしょう。例えば30分単位で切り上げるときはCEILING関数を、切り捨てるときはFLOOR関数を使いますね。
出勤における時間は次のような式になりますね。
=IF(OR(A1<"8:00"*1,A1>"8:05"*1),CEILING(A1,"0:30"),"8:00"*1)
退社における時間は次のようになりますね。
=IF(OR(B1<"16:50"*1,B1>"17:00"*1),FLOOR(B1,"0:30"),"17:00"*1)
したがってＣ1セルへの入力の式は次のようになりますね。
=IF(OR(B1<"16:50"*1,B1>"17:00"*1),FLOOR(B1,"0:30"),"17:00"*1)-IF(OR(A1<"8:00"*1,A1>"8:05"*1),CEILING(A1,"0:30"),"8:00"*1)

なお、セルの表示形式は時刻にしておきます。

補足

いつもKURIMOTOさんにはお世話になります。
テストしました。
この場合は　０８００～１７００の時間の範囲みたいです。

下記は複雑な条件付きですが可能でしょうか。
ご苦労掛けますがもし可能ならばご教示いただけませんか。

A1(出勤時間) B1(退社時間)の値は２４時間シフトですので上の範囲に当てはまりません。
ということは　０８００～１７００以外の勤務があります。

A1の値に対して５分迄は許容範囲(時間に遅れても認める)
　但し５分以上は３０分後の出勤になる
例１
　　A1=０９０５ ０９００
例２
　　A1=２３０４　　２３００
例３
　　A1=１００６　　１０３０
例４
　　A1=２３０７　　２３３０
B1の値も１０分前は許容範囲(早く帰っても認める)
　但し１０分以前は３０分繰り上げ
例１
　　B1=１４５５　１５００
例２
　　B1=２２５３　２３００
例３
　　B1=１３５０　１４００
例４
B1=１２４９ 　１２３０

回答No.3

135ok

ご参考程度で。
使っている人がいない気がしますが、添付資料のように表を使う方法もあります。
左の表の判別は、右の表の判定の値を表示しています。

お礼

ご協力いただき有難うございました。
今後とも宜しく御願いします。

回答No.2

bin-chan

> A1に０８:００～０８:０５と入力したときは０８００に、
> B1に１６:５０～１７:００と入力したときは１７００に
８時何分までが「０８:００」なんですか？
「１６:４９」はどうします？
お昼休みはどうします？
１時間単位？０．５時間は無し？

条件提示が不十分かも。

お礼

ご協力いただき有難うございました。
今後とも宜しく御願いします。

補足

説明不足で大変ご迷惑を掛けます。
１８時何分までが「０８:００」なんですか？
　０８時０５分までです。(～０８００～０８０５)
　　０８時０５分以上は０８時３０分になります。
　　　当然、０８時００分前は０８時００分です。
２「１６:４９」はどうします？
　１６時３０分の退社です。
３お昼休みは無視です。　引きません。
４出勤、退社の時間は３０分単位です。

大変申し訳ありません。
宜しく御願いします。

回答No.1

HAL007

計算式を簡単にする為に
ｃ列を出勤時間の補正に使う、式はIF関数とAND関数を組合わせて８時を決める。
ｄ列は出勤と同様に補正に使う
簡単な計算ですが人から教えて貰ったものをそのまま使うと
後でメンテナンスが出来なくなります。
自分で計算式は入れて下さい。

お礼

ご協力いただき有難うございました。
今後とも宜しく御願いします。