残業時間の計算で２件教えてください。

質問

会社の勤務時間が朝８時３０分から夕方５時１５分(１７時１５分)です。
昼休憩が１２時から１２時４５分(４５分)
朝５時から８時３０分までと１７時１５分から２２時までは普通残業です。
稀ですが、０時から早朝５時までと２２時から２４時は深夜残業になります。

普通残業の式を　="8:30"-MAX("5:00",出勤時間)+MIN("10:00",終了時間)-"17:15"
又は　=8.5/24-MAX(5/24,出勤時間)+MIN(22/24,終了時間)-17.25/24
としました。　そして下までコピー・ペーストしていると　勤務・終了時間を入れなくとも残業時間が表示されました。
これを表示しないようにできないものかと考えています。コピーぺしなければいいのですが。
又は、別の計算式がありますでしょうか?
もう一点は　深夜残業の場合などですが、エクセルでは、　"0"表示を消すことができますが、
IphoneやIPadで "Polaris Office"を使用しています。これでは "0"表記を消す方法があれば
教えてください。　よろしくお願いいたします。

回答No.1

kagakusuki · ベストアンサー

普通残業の式に関しては、以下の様にされると良いと思います。

=IF(COUNT(出勤時間,終了時間)=2,"8:30"-MAX("5:00",出勤時間)+MIN("10:00",終了時間)-"17:15","")

お礼

ありがとうございます。ばっちりです。
同様に深夜残業も同じようにすれば行けました。
ipad等も良好です。

回答No.4

MarcoRossiItaly

未入力の場合にゼロを算出する数式を書きます。なお「Polaris Office」については知識がないので、回答を控えます。

C3　=(a3<>"")*("8:30"-MAX("5:00",a3))+(b3<>"")*(MIN("22:00",b3)-"17:15")

お礼

締め切り後に回答ありがとうございます。
サポート担当者まで掛け合ってもらえたとは恐れ入ります。
重ねてお礼申し上げます。

回答No.3

kagakusuki

済みません間違えました。

　普通残業の式は

=IF(AND(COUNT(出勤時間,終了時間)=2,OR(出勤時間-"8:30"<0,終了時間+(出勤時間>終了時間)-"17:15">0)),("8:30"-MAX("5:00",出勤時間))*(出勤時間-"8:30"<0)+(MIN("22:00",終了時間+(出勤時間>終了時間))-"17:15")*(終了時間+(出勤時間>終了時間)-"17:15">0),"")

　深夜残業の式は

=IF(AND(COUNT(出勤時間,終了時間)=2,OR(出勤時間-"5:00"<0,終了時間+(出勤時間>終了時間)-"22:00">0)),("5:00"-出勤時間)*(出勤時間-"5:00"<0)+(MIN("5:00"+1,終了時間+(出勤時間>終了時間))-"22:00")*(終了時間+(出勤時間>終了時間)-"22:00">0),"")

です。
　但し、連続して２４時間以上の時間に亘って勤務する場合には対応しておりません。
　又、８時３０分以降に出社（遅刻）して、そのまま、17:15以降まで勤務する場合や、８時３０分以前に出社して、その後、17:15になる前に早退した場合などにも対応しておりません。

お礼

ありがとうございます。最初の回答でばっちりでした。
こちらもこれから検証してみます。

回答No.2

bunjii

>又は、別の計算式がありますでしょうか?
IF関数を使って勤務開始（B列）と終了（C列）が共に空欄のとき時間外（D列）を空欄にすれば良いでしょう。

D3=IF(AND(B3="",C3=""),"","8:30"-MAX("5:00",B3)+MIN("22:00",C3)-"17:15")

B3=IF(AND(B3="",C3=""),"",8.5/24-MAX(5/24,B3)+MIN(22/24,C3)-17.25/24)

勤務開始が有って終了が無い場合と勤務開始が無くて終了が有る場合はどのように解釈するのでしょうか？
24時を跨ぐ場合の対策であれば良いのですが、入力ミスの場合は別の対策が必要です。

お礼

ありがとうございます。
これから検証してみますが、No1の方の回答でばっちりでした。