VBAまたは関数を使って空白セルを飛ばす時間計算

VBAや関数で、空白セルを飛ばして時間を計算したいのですが、うまくいきません。

A列に日付、D列に退勤時刻を入力し、E列に休息時間を自動計算させる式を考えています。
（休息時間とは、退勤してから次の出勤までの時間のことです。）

連日出勤の場合はE列に以下の計算式を入力すると問題無く計算できるのですが、翌日や前日が休みの場合、この式では対応できません。

E2セル
=IF(B2="","",IF(C2="翌",B3-D2,1+B3-D2))

この式をオートフィルでE列を埋めたものが添付画像になります。

VBA初心者で、色々調べながらVBAと関数の両方で考えてみましたが、どうしても解決できず相談させていただいた次第です。解決方法はVBAと関数のどちらでもOKです。
連休の日数に関係なく計算できる方法を教えてください。

よろしくお願いします。

No.2 ベストアンサー 回答者： CoalTar 回答日時： 2015/11/10 15:46

E2セルに

=IF(B2="","",SUM(INDEX(A3:B$33,MIN(IF(ISNUMBER(B3:B$33),ROW(B3:B$33)))-ROW(),))-SUM(A2,D2,C2="翌"))
[Ctrl]+[Shft] ＋[Enter] で確定、配列数式。{ }で挟まれる
下へオートフィル
日付+出勤時刻-SUM(日付,退勤時刻,翌だったら1) で計算してます

この回答へのお礼

ありがとうございます！一発で解決することができたので、ベストアンサーとさせていただきました。ISNUMBER関数というのがあるんですね。知らなかったです。本当に助かりました。ありがとうございました。

お礼日時：2015/11/11 11:47

No.3 回答者： tom04 回答日時： 2015/11/10 16:30

こんにちは！

普通に考えるとE7セルは１日（１行）飛んでいますので
37：00
という表示が正しいような気がします。

ご希望通りの表示になるかどうかわかりませんが・・・
A列には連続して数値（シリアル値でなくても構いません）が入力してあるとします。
E2セル（セルの表示形式はユーザー定義から　[h]:mm　としておきます）に

=IF(B2="","",IF(C2="翌",IF(COUNT(B:B)=COUNT(B$2:B2),"",INDEX(A3:A$100,MIN(IF(B3:B$100>0,ROW(B3:B$100)-ROW())))+INDEX(B3:B$100,MIN(IF(B3:B$100>0,ROW(B3:B$100)-ROW())))-(A2+D2+1)),1+B3-D2))

配列数式になりますのでCtrl＋Shift＋Enterで確定！
この画面からコピー＆ペーストする場合は
上記数式をドラッグ＆コピー　→　E2セルを選択　→　数式バー内に貼り付け　→　そのまま（編集可能なまま）
Ctrl＋Shiftキーを押しながらEnterキーで確定！
数式の前後に{ }マークが入り配列数式になります。

最後にE2セルのフィルハンドルで下へコピーしてみてください。

※　最終行が「翌」の場合は引き算する対象がないので、何も表示しないようにしています。

※　個人的な疑問ですが
C列が空白で、次の行のB列が未入力の場合、
＞1+B3-D2
とは夜中の０時を基準としているのでしょうか？

一発で解決とはいかないと思いますが、
とりあえずは叩き台としての回答です。m(_ _)m

この回答へのお礼

回答ありがとうございます。お恥ずかしいですがROW関数の使い方が理解できていなかったので、こういう風に使うのかと参考になりました。

個人的な疑問の件ですが、おそらく回答者さんの解釈で合っています。
私の意図としては、日付をまたぐ場合（翌がつく場合）とそうでない場合で日付ずれを調整する為に1日（24h）を加算しています。
本来なら退勤は翌2:00ならば26:00と入力した方が条件分岐させずに計算楽なのですが・・・。

お礼日時：2015/11/11 12:16

No.1 回答者： RandenSai 回答日時： 2015/11/10 15:27

ここで言う休息時間って、退勤時間から次の出勤時間までの間隔だって解釈で合ってますか？

だとしたら、4日の答えはエラーになってないけど、答えとしては間違いですよね。4日の17:00～7日の15:00まで出勤していないわけだから・・・

4日の17:00～24:00
5日の0:00～24:00
6日の0:00～24:00
7日の0:00～15:00

答えは以上の合計、70:00になるべきでしょう？

もし日付をまたいで考えないってことなら、翌の場合にその翌日が空欄なら、24:00とみなして計算すればエラーにならずに済みます。もう一個IFを足せばおｋ。

この回答へのお礼

ご指摘の通り、4日の休息時間は70:00となるのが正です。日付をまたいで計算したかったので、NO.2の方の方法で解決することができました。回答ありがとうございました。

お礼日時：2015/11/11 11:44