エクセルについてご質問があります。
お力を貸してください
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ベストアンサー
- 回答日時:
これでどうでしょう?
ちょっとだけテストしましたが、後は自己責任でお願いします。
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
- 回答日時:
書き忘れました。
セルに入力する式は、次の通りです。
=終了日時(B1,A1,$D$1:$E$4,$F$1:$G$4)
$F$1:$G$4が、休日分として追加になっています。
休日の日と時間はセルを分けていません。F列が休日開始日時、G列が休日終了日時です。
休憩と同様に昇順で設定してください。
No.3
- 回答日時:
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
何度もすみません
宜しくお願い致します
No.2
- 回答日時:
バグがあったようで、申し訳ないです。
終了日時が合わなかったケースの開始日、残り時間、間違って表示された終了日時、および、本来表示されるべき終了日時を教えてください。
返信ありがとうございます。
現在試してやっているのですが自分のミスでした。 すみません。
凄く使いやすくなって感謝しています。
が、もう一つだけ教えて頂きたい事があります。
追加でF列に休日開始日
G列に休日開始時間
H列に休日終了日付
I列に休日終了時間を追加する事は可能でしょうか?
お時間がある時で本当によろしいので教えて頂けないでしょうか?お願い致します
No.1
- 回答日時:
グランドシールさんの書いた式ですが、深夜が無ければ正しく動くって本当ですか?
例えば、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
ご連絡が遅れ申し訳ありません。調べてみてユーザー定義関数で上記の通りやってみたのですが終了時間が合いませんでした。
凄い簡単なやり方なので、お時間ある時でもよろしいので教えて頂きたいです。お願い致します
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
おすすめ情報
- ・漫画をレンタルでお得に読める!
- ・街中で見かけて「グッときた人」の思い出
- ・「一気に最後まで読んだ」本、教えて下さい!
- ・幼稚園時代「何組」でしたか?
- ・激凹みから立ち直る方法
- ・1つだけ過去を変えられるとしたら?
- ・【あるあるbot連動企画】あるあるbotに投稿したけど採用されなかったあるある募集
- ・【あるあるbot連動企画】フォロワー20万人のアカウントであなたのあるあるを披露してみませんか?
- ・映画のエンドロール観る派?観ない派?
- ・海外旅行から帰ってきたら、まず何を食べる?
- ・誕生日にもらった意外なもの
- ・天使と悪魔選手権
- ・ちょっと先の未来クイズ第2問
- ・【大喜利】【投稿~9/7】 ロボットの住む世界で流行ってる罰ゲームとは?
- ・推しミネラルウォーターはありますか?
- ・都道府県穴埋めゲーム
- ・この人頭いいなと思ったエピソード
- ・準・究極の選択
- ・ゆるやかでぃべーと タイムマシンを破壊すべきか。
- ・歩いた自慢大会
- ・許せない心理テスト
- ・字面がカッコいい英単語
- ・これ何て呼びますか Part2
- ・人生で一番思い出に残ってる靴
- ・ゆるやかでぃべーと すべての高校生はアルバイトをするべきだ。
- ・初めて自分の家と他人の家が違う、と意識した時
- ・単二電池
- ・チョコミントアイス
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
音楽ソフトについて
-
clipSTUDIOの、 ツールやレイヤ...
-
gimpで文字の細工をしたい
-
jtrim 1.53cが正常な動きをし...
-
discordのビデオ通話でカメラ映...
-
Windows 11 に対応する作業
-
ファイル名の長短について
-
GIMPで直線が書けない
-
スプレッドシートの作業範囲
-
googleスプレッドシートのキャ...
-
フォルダ内にある全ファイルの...
-
PCでポップアップされるので削...
-
コピー元とコピー先を同じにす...
-
フォルダー内のファイル名をテ...
-
GIMP2で1つのレイヤーでテキス...
-
パソコンソフトのシリアルナンバー
-
mp3 ファイルをCDに焼く無料ソ...
-
音声合成AIと動画編集ができる...
-
Ghosteryのプライバシー 広告ブ...
-
時間差でのオートクリック
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
webメールの表示について
-
jtrim 1.53cが正常な動きをし...
-
自分だけの辞書(データベース...
-
ゆうちょ認証アプリが使えません
-
STEAM版BF4が起動しない
-
IMEは使い難くなってきた
-
google日本語入力
-
ホームページビルダーのバージ...
-
PowerDVDでブルーレイを再生で...
-
このフォントは何ですか?
-
googleスプレッドシートのキャ...
-
win10からwin11へのIME登録単語...
-
LDPlayerのマクロの編集方法を...
-
写真の管理用ソフト
-
filezillaの転送
-
Tor Browserアンインストールに...
-
google日本語入力
-
GIMPで直線が書けない
-
Winrarの移行
-
フォルダパスと実際のフォルダ...
おすすめ情報