エクセルの関数を使い、仕事の経過時間を計算をしたいです。 添付した図にあるように、開始時間と完了時間がセルに打ち込まれ…

Question

エクセルの関数を使い、仕事の経過時間を計算をしたいです。
添付した図にあるように、開始時間と完了時間がセルに打ち込まれています。
C列に関数を入れて、経過時間を自動で計算できるようにしたいです。自分の職場は3交代制の為、平日のお昼休みが1日3回あります。
非稼働時間の間は、除外して計算できるようにしたいのですが
難しくてできません。分かる方いらっしゃればよろしくお願い致します。
（非稼働時間は添付した図に記載）

添付した図に打ち込まれている「経過時間」の数字は、手打ちで自分が打ってみました。
関数を入れてこのように表示されるようにしたいです。

補足

添付した図に少し誤りがありました。すいません。
非稼働時間ですが、(土)の3:00～4:00も休憩時間の為、非稼働です。
よろしくお願い致します。

Answer 1

関数では補助セルを使っても難しいですね。ユーザー定義関数を作ってみました。
=CalcKadou(開始時刻,完了時刻)のように使います。

添付図では、D2セルに、
=CalcKadou(A2,B2) としています。表示形式を [h]:mm とします。

標準モジュールに貼り付けます。

Function CalcKadou(srtTime As Variant, endTime As Variant)
Dim TM As Variant '// 時刻の区切り
Dim cal(6) As String '// 月曜からの休憩(0)、加算(1)
TM = Split("0:00,3:00,4:00,7:05,7:40,11:00,12:00,19:00,20:00,24:00", ",")
cal(0) = "0001101011": cal(1) = "1011101011"
cal(2) = "1011101011": cal(3) = "1011101011"
cal(4) = "1011101011": cal(5) = "1011000000"
cal(6) = "0000000000"

Application.Volatile

'// 変数の最初の｢s｣は開始時刻、｢e｣は完了時刻
Dim sW As Integer, eW As Integer '// 曜日
Dim eW2 As Integer, wk As Integer '// 計算用曜日
Dim sT As Date, eT As Date '// 開始時刻、終了時刻
Dim sSG As Date, eSG As Date '// 経過時間計
Dim i As Integer

sW = WorksheetFunction.Weekday(DateValue(srtTime), 3)
eW = WorksheetFunction.Weekday(DateValue(endTime), 3)
If sW > eW Then eW = eW + 7 '// 翌週の場合
sT = TimeValue(srtTime)
eT = TimeValue(endTime)

'// 開始日の0時から開始時刻までの稼働時間
i = 0
While Not (TM(i) <= sT And sT <= TM(i + 1))
sSG = sSG + (TimeValue(TM(i + 1)) - TimeValue(TM(i))) * Mid(cal(sW), i + 1, 1)
i = i + 1
Wend
sSG = sSG + (sT - TimeValue(TM(i))) * Mid(cal(sW), i + 1, 1)

'// 開始日の0時から完了日の完了時刻までの稼働時間
i = 0: eW2 = sW
While Not (eW2 = eW And TM(i) <= eT And eT <= TM(i + 1))
wk = eW2 Mod 7
If TM(i + 1) = "24:00" Then
eSG = eSG + (1 - TimeValue(TM(i))) * Mid(cal(wk), i + 1, 1)
Else
eSG = eSG + (TimeValue(TM(i + 1)) - TimeValue(TM(i))) * Mid(cal(wk), i + 1, 1)
End If

i = i + 1
'// 日付を変える
If i = 9 Then eW2 = eW2 + 1: i = 0
Wend
eSG = eSG + (eT - TimeValue(TM(i))) * Mid(cal(wk), i + 1, 1)

CalcKadou = eSG - sSG
End Function

Answer 2

確認ですけど、
開始から完了まで1週間以上とかもあるんですか？