エクセルについてご質問があります。 お力を貸してください A1=25.0 ※残り時間(h) B1=

エクセルについてご質問があります。
お力を貸してください

A1=25.0 ※残り時間(h)
B1=2016/10/28 9:00 ※開始日

※休憩開始時間
D1=10:00 D2=12:00 D3=15:00 D4=00:00
※休憩終了時間
E1=10:10 E2=13:00 E3=15:10 E4=01:00

C1に終了時間を求めたいのですが
休憩なしの場合は、2016/10/29 10:00ですが
休憩がある場合は、求める事は可能でしょうか？

※夜勤の休憩時間がない場合は下記内容です
=B1+INT(A1/(1-SUM(E1:E3)+SUM(D1:D3))/24)+MOD(A1/24,1-SUM(E1:E3)+SUM(D1:D3))+SUMPRODUCT((D1:D3>MOD(B1,1))*(D1:D3<MOD(B1,1)+MOD(A1/24,1-SUM(E1:E3)+SUM(D1:D3)))*(E1:E3-D1:D3))

宜しくお願い致します

No.4 ベストアンサー 回答者： ママチャリ 回答日時： 2016/11/05 09:57

これでどうでしょう？

ちょっとだけテストしましたが、後は自己責任でお願いします。

Function 終了日時(開始日時 As Variant, 経過時間 As Variant, 休憩 As Range, 休日 As Range) As Variant
Dim D As Variant
Dim K As Long
Dim KS As Variant
Dim KE As Variant
Dim 休開始 As Variant
Dim 休終了 As Variant
終了日時 = 開始日時 + 経過時間 / 24
休開始 = 開始日時
休終了 = 開始日時
Do
If Not (終了日時 <= 休開始 Or 休終了 <= 開始日時) Then
終了日時 = 終了日時 + 休終了 - 休開始
End If
Call 次の休憩を求める(休開始, 休終了, 休憩, 休日)
Loop Until 終了日時 <= 休開始
End Function

Sub 次の休憩を求める(休開始 As Variant, 休終了 As Variant, 休憩 As Range, 休日 As Range)
Dim KS As Variant
Dim KE As Variant
Dim HS As Variant
Dim HE As Variant
Dim D As Variant
Dim K As Long
'次の休憩を求める
D = DateValue(Format(休終了, "yyyy年mm月dd日"))
Do
For K = 1 To 休憩.Rows.Count
KS = D + 休憩(K, 1)
KE = D + 休憩(K, 2)
If KS > 休終了 Then Exit For
Next K
D = D + 1
Loop Until KS > 休終了
'次の休日を求める
For K = 1 To 休日.Rows.Count
HS = 休日(K, 1)
HE = 休日(K, 2)
If HS > 休終了 Then Exit For
Next K
'休日と休憩の判定
If K >= 休日.Rows.Count Or KS < HS Then
休開始 = KS
休終了 = KE
Else
休開始 = HS
休終了 = HE
End If
End Sub

No.5 回答者： ママチャリ 回答日時： 2016/11/05 10:03

書き忘れました。

セルに入力する式は、次の通りです。

=終了日時(B1,A1,$D$1:$E$4,$F$1:$G$4)

$F$1:$G$4が、休日分として追加になっています。
休日の日と時間はセルを分けていません。F列が休日開始日時、G列が休日終了日時です。
休憩と同様に昇順で設定してください。

No.3 回答者： ママチャリ 回答日時： 2016/11/01 08:48

F〜I列の実際の設定イメージを教えてください。

正直、No1の回答の延長で出来るような気がしません。なので、あまり期待しないで下さい。

この回答へのお礼

返信ありがとうございます。
下記内容が実際の設定イメージです。

休日開始日
F1=2016/11/20
F2=2016/11/27

休日開始時間
G1=8:30
G2=8:30

休日終了日
H1=2016/11/21
H2=2016/11/28

休日終了時間
I1=8:30
I2=8:30

何度もすみません
宜しくお願い致します

お礼日時：2016/11/01 10:28

No.2 回答者： ママチャリ 回答日時： 2016/10/31 12:41

バグがあったようで、申し訳ないです。

終了日時が合わなかったケースの開始日、残り時間、間違って表示された終了日時、および、本来表示されるべき終了日時を教えてください。

この回答へのお礼

返信ありがとうございます。
現在試してやっているのですが自分のミスでした。 すみません。
凄く使いやすくなって感謝しています。
が、もう一つだけ教えて頂きたい事があります。
追加でF列に休日開始日
G列に休日開始時間
H列に休日終了日付
Ｉ列に休日終了時間を追加する事は可能でしょうか？
お時間がある時で本当によろしいので教えて頂けないでしょうか？お願い致します

お礼日時：2016/10/31 21:19

No.1 回答者： ママチャリ 回答日時： 2016/10/29 21:25

グランドシールさんの書いた式ですが、深夜が無ければ正しく動くって本当ですか？

例えば、9:00から残り3時間を求めると12:10と表示されてしまいます。昼休みがあるから13:10が正しいと思うのですが･･･。

それはさて置き、これを数式での求めるのは難しいと思います。そこでユーザ定義関数で実装してみました。
C1セルに、次の数式を入力してください。
なお、休憩時間は早い順に並べて入力して下さい（0:00~1:00を先頭に入力）

【C1セル】=終了日時(B1,A1,$D$1:$E$4)

Function 終了日時(開始日時 As Variant, 経過時間 As Variant, 休憩時間 As Range) As Variant
Dim D As Variant
Dim K As Long
Dim KS As Variant
Dim KE As Variant
終了日時 = 開始日時 + 経過時間 / 24
D = DateValue(Format(開始日時, "yyyy年mm月dd日"))
Do
For K = 1 To 休憩時間.Rows.Count
KS = D + 休憩時間(K, 1)
KE = D + 休憩時間(K, 2)
If Not (終了日時 <= KS Or KE <= 開始日時) Then
終了日時 = 終了日時 + 休憩時間(K, 2) - 休憩時間(K, 1)
End If
Next K
D = D + 1
Loop While D <= DateValue(Format(終了日時, "yyyy年mm月dd日"))
End Function

この回答へのお礼

ご連絡が遅れ申し訳ありません。調べてみてユーザー定義関数で上記の通りやってみたのですが終了時間が合いませんでした。
凄い簡単なやり方なので、お時間ある時でもよろしいので教えて頂きたいです。お願い致します

お礼日時：2016/10/31 11:48