勤務時間の設定方法 ～その２(追加)

質問

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

下記のアドレスですでにご教示いただいていてテストした結果、満足した内容でした。
http://oshiete1.goo.ne.jp/qa5448591.html

その後実際に運営して見ると私のミスで勤務時間は３０分単位に対応していませんでした。

毎時の００分時　(出勤／退社時間には前回の回答で適用済みです。)

※　今回、ご指導を仰ぎたいのは、追加として毎時の３０分単位にも適用した数式が可能なら宜しく御願いします。

D1／E1=　　＊＊:３０
　出勤時間(D1)　(出勤は０５分後までは認める)
例１　D1=０８:３０ ～ ０８3５　は０８:3０の出勤になります。
０８:３６～は　０９:００の出勤になります
G1=08:30
例２　A1= ２３:３０ ～ ２３:３５　は２３:3０の出勤になります。
２３:３６～は　２４:００の出勤になります
G1=23:30
退社時間(E1)　(退社は１０分前迄は認める)
例１　E1= １０:2０ ～ １０:３０　は１０:3０の退社になります。
～１０:１９は　１０:００の退社になります
H1=10:30
例２　E1= ２０:２０ ～ ２０:３０　は２０:3０の退社になります。
～２０:１９は　２０:００の退社になります
H1=20:30

※　念のためにですがシフト制ですので定まった時間ではないです。

回答No.2

KURUMITO · ベストアンサー

前回はＡ列に出勤時間、Ｂ列に退社時間でしたので、そのままの形でお答えします。
出勤時間については次の式になります。
=IF(MOD(A1,"0:30")-"0:00:1"<="0:05"*1,FLOOR(A1,"0:30"),CEILING(A1,"0:30"))
退勤時間については次の式になります。
=IF(MOD(B1,"0:30")+"0:00:1">="0:20"*1,CEILING(B1,"0:30"),FLOOR(B1,"0:30"))
なお、セルの表示形式は「ユーザー定義」で　[h]:mm　としてください。通常の時刻表示ですと24：00は0：00のように表示されてしまいますので、それを避けるためです。

お礼

クリモトさん、またまたご協力いただき誠に有難うございました。
テストさせていただきばっちり良かったです。
こんな難しい数式ができるなんて尊敬します。

今後とも御世話をかけるかも分かりませんがその時はどうか宜しく御願いします。
有難うございました。

回答No.1

Trick--o--

=TIME(時,分,秒)
指定した時間のシリアル値を返す
=HOUR(シリアル値)
=MINUTE(シリアル値)
=SECOND(シリアル値)
指定したシリアル値の時、分、秒を数値で返す

難しいこと考えなくても、この辺を組み合わせればできるのではないでしょうか

出勤時間
=IF(MINUTE(A1)>35,TIME(HOUR(A1)+1,0,0),IF(MINUTE(A1)>5,TIME(HOUR(A1),30,0),TIME(HOUR(A1),0,0)))
退社時間
=IF(MINUTE(A1)>=50,TIME(HOUR(A1)+1,0,0),IF(MINUTE(A1)>=20,TIME(HOUR(A1),30,0),TIME(HOUR(A1),0,0)))

お礼

ご丁寧に表まで表示いただき有難うございます。
今後とも宜しく御願いします。