エクセルの時間計算について

Question

エクセルで時間計算を行いたいと思っています。
具体的には、総時間と開始時間を入れると、ある特定の時間（休憩時間）を考慮して終了時間を自動算出するというものです。
なるべく関数でやりたいと思っています。

例
総時間　開始時間　終了時間　
180　　　 13:30　16:40　（下記③の休憩時間を総時間に追加）
540　　　　8:30　19:50　（下記①、②、③、④の休憩時間を総時間に追加）
60　　　　11:00　12:00　（休憩時間追加無し）
300　　　 23:00　  4:20　（下記⑥の休憩時間を総時間に追加）


休憩時間
①10:30～10:40
②12:00～13:00
③15:30～15:40
④17:30～18:30
⑤22:00～23:00
⑥2:30～2:50
⑦5:30～6:00

tatsu99 · Accepted Answer

No7です。＞時刻表示に関しては特にこだわりはありませんので、アドバイス頂きました内容でも大丈夫です。＞マクロが簡単になるとの事ですが、№5のマクロから更に簡単に出来るという事でしょうか？もし、時刻表示を25:00のようにしても問題ないなら、マクロは以下のようになります。前回のは破棄してください。又、8時前の開始にも対応しています。（添付図の赤線で囲んだところ）（添付図の青線は従来の24時を超えたときの休憩開始時刻です）添付図の例では C２へ =EndTime(A2,B2,$E$2:$F$10) と入力し、オートフィルで下へコピーしています。（休憩時間テーブルの範囲が２行増えています） ------------------------------------------------------------------- Option Explicit 'TTime：総時間（単位：分） 'STime：開始時間（HH:MM形式） 'TTable:休憩時間テーブルの範囲（開始時間の先頭セル：終了時間の最後のセル） '上記の情報から終了時間（HH:MM）を算出する '終了時間＝開始時間＋総時間＋休憩時間テーブルの該当休憩時間の和 Public Function EndTime(ByVal TTime As Variant, ByVal STime As Variant, ByRef TTable As Range) As Variant Dim i As Long Dim etime As Variant Application.Volatile EndTime = "" '総時間と開始時刻が空白なら、終了時間は空白 If TTime = "" And STime = "" Then Exit Function EndTime = CVErr(xlErrValue) '総時間と開始時刻の何れかが空白なら、終了時間は#VALUE If TTime = "" Or STime = "" Then Exit Function '総時間と開始時刻の何れかが不正なら、終了時間は#VALUE If IsNumeric(TTime) = False Or IsNumeric(STime) = False Then Exit Function '休憩時間テーブルが２列でないなら、終了時間は#VALUE If TTable.Columns.Count <> 2 Then Exit Function etime = STime + (TTime / 1440) For i = 1 To TTable.Rows.Count If TTable.Cells(i, 1).Value > STime And TTable.Cells(i, 1).Value < etime Then '開始時間＜休憩開始時間かつ休憩開始時間＜終了時間なら、休憩時間を終了時間に加算 etime = etime + (TTable.Cells(i, 2).Value - TTable.Cells(i, 1).Value) End If Next EndTime = etime End Function

ママチャリ · Answer

No.2です。作ってみました。こんな感じです。提示されている要件では、休憩時間を指定していますが、これを稼働時間に変更しています。稼働時間は、セル範囲で指定するようにしています。ちなみに、添付画像のB9セルは、翌日の0:00なので注意してください。基本的に日を跨ぐような総時間でも対応できるようにしていますが、無限ループを防止するために10日を超えるような場合はエラーにしています。 Function 終了時刻(総時間 As Variant, 開始時刻 As Variant, 実働範囲 As Range) As Variant Dim a As Variant Dim i As Long Dim d As Long Dim f As Boolean Dim s As Variant Dim t As Variant a = 実働範囲 s = 総時間 / (24 * 60) Do For i = LBound(a) To UBound(a) If f Then t = t + a(i, 2) - a(i, 1) Else If 開始時刻 < a(i, 2) Then f = True If 開始時刻 < a(i, 1) Then t = a(i, 2) - a(i, 1) Else t = a(i, 2) - 開始時刻 End If End If End If If t >= s Then 終了時刻 = d + a(i, 2) - (t - s) Exit Function End If Next i d = d + 1 If d > 10 Then Err.Raise 1000 Loop End Function

tom04 · Answer

何度もごめんなさい。
No.6・9・10です。

前回は勘違いしていました。
終了時刻が休憩時間内にかかった場合は、休憩時間分をその休憩時刻終了以降に回す必要があるのですね。
前回のコードは消去し、↓のコードに変更してみてください。

Sub Sample3()
 　Dim i As Long, k As Long, myTime
　　For i = 2 To Cells(Rows.Count, "A").End(xlUp).Row
　　　myTime = Cells(i, "B") + Cells(i, "A") / 24 / 60
　　　　For k = 2 To Cells(Rows.Count, "E").End(xlUp).Row
　　　　　If Cells(k, "E") > Cells(i, "B") Then Exit For
　　　　Next k
　　　　　Do
　　　　　　If Cells(k, "F") > myTime Or Cells(k, "F") = "" Then Exit Do
　　　　　　　myTime = myTime + Cells(k, "G") / 24 / 60
　　　　　　　k = k + 1
　　　　　Loop
　　　　If myTime > Cells(k, "E") And myTime < Cells(k, "F") Then
　　　　　myTime = Cells(k, "F") + (myTime - Cells(k, "E"))
　　　　End If
　　　　　Cells(i, "C") = myTime
　　Next i
 End Sub

今度はどうでしょうか？m(_ _)m

tom04 · Answer

No.６・9です。

＞例えば、11:30開始時間で総時間60分としたら、終了時間は13:30になりますが、12:30の結果になりました。

すなわち、C列表示の時刻が休憩時間内になった場合は、休憩時間の最終までにすれば良いのですかね？
↓のコードにしたらどうなりますか？

Sub Sample2()
　Dim i As Long, k As Long, myTime
　　For i = 2 To Cells(Rows.Count, "A").End(xlUp).Row
　　　myTime = Cells(i, "B") + Cells(i, "A") / 24 / 60
　　　　For k = 2 To Cells(Rows.Count, "E").End(xlUp).Row
　　　　　If Cells(k, "E") > Cells(i, "B") Then Exit For
　　　　Next k
　　　　　Do
　　　　　　If Cells(k, "F") > myTime Or Cells(k, "F") = "" Then Exit Do
　　　　　　　myTime = myTime + Cells(k, "G") / 24 / 60
　　　　　　　k = k + 1
　　　　　Loop
　　　　If myTime > Cells(k, "E") And myTime < Cells(k, "F") Then
　　　　　myTime = Cells(k, "F")
　　　　End If
　　　　　Cells(i, "C") = myTime
　　Next i
 End Sub

今回も細かい検証はしていません。m(_ _)m

tom04 · Answer

No.6です。

＞例えばですが、
＞総時間300　開始時間2:00にするとエラーになります。

前回のコードの
＞If Cells(k, "F") > myTime Then Exit Do
を
＞If Cells(k, "F") > myTime Or Cells(k, "F") = "" Then Exit Do
に変更したらどうなりますか？

「2：00」はどちらの「2：00」になるのでしょうか？
AMの2：00の場合か26：00の場合なのか？
それによって結果が変わると思います。
仮に26：00の場合、B列には26：00と入力していますよね？

B列が30：00を超えた場合と「B列+A列（分）」の時刻が0：00～8：30までの場合、
休憩時間は全く考慮されませんが、それで良いのでしょうか？m(_ _)m

tatsu99 · Answer

No5です。老婆心からですが、時刻の表示は２４：００を超えた場合、２５：３０のように表示したほうが良いのではないでしょうか。
２４：００以降を２５：００の用に表示するには、
セルの書式設定で、ユーザー定義の[h]:mmを指定します。添付図参照（赤線の部分）

私がNo5で提示したものは、８時前の開始はないという前提でしたが、
このように表示してあれば、８時前の開始も可能になります。
追加①2:30～2:50
追加②5:30～6:00
①10:30～10:40
②12:00～13:00
③15:30～15:40
④17:30～18:30
⑤22:00～23:00
⑥26:30～26:50
⑦29:30～30:00
のように定義します。
追加①②が、８時前の開始に対応します。
もし、このようにされるなら、マクロも簡単になります。
No5のマクロは、８時前の開始があると誤動作の可能性があります。

tom04 · Answer

こんにちは！

A列は「分」の数値ですよね。
VBAになりますが、一例です。

↓の画像のE～G列のように対応表を作成しておきます。
E・F列のセルの表示形式は　h:mm　としておき
E7　→　26：30
E8　→　29：30
F7　→　26：50
F8　→　30：00
のように入力しています。
（G列は単に整数だけの入力）

以上の下準備ができたうえで・・・

Sub Sample1()
　Dim i As Long, k As Long, myTime
　　For i = 2 To Cells(Rows.Count, "A").End(xlUp).Row
　　　myTime = Cells(i, "B") + Cells(i, "A") / 24 / 60
　　　For k = 2 To Cells(Rows.Count, "E").End(xlUp).Row
　　　　If Cells(k, "E") > Cells(i, "B") Then Exit For
　　　Next k
　　　Do
　　　　If Cells(k, "F") > myTime Then Exit Do
　　　　　myTime = myTime + Cells(k, "G") / 24 / 60
　　　　　k = k + 1
　　　Loop
　　　Cells(i, "C") = myTime
　　Next i
End Sub

※　当然C列の表示形式は「時刻」にしておいてください。

※　細かい検証はしていませんので
不具合が出たらごめんなさい。m(_ _)m

tatsu99 · Answer

No4です。前回のマクロは破棄してください。一部誤りがありましたので、訂正します。 ------------------------------------------------------ Option Explicit 'TTime：総時間（単位：分） 'STime：開始時間（HH:MM形式） 'TTable:休憩時間テーブルの範囲（開始時間の先頭セル：終了時間の最後のセル） '上記の情報から終了時間（HH:MM）を算出する '終了時間＝開始時間＋総時間＋休憩時間テーブルの該当休憩時間の和 Public Function EndTime(ByVal TTime As Variant, ByVal STime As Variant, ByRef TTable As Range) As Variant Dim i As Long Dim etime As Variant Application.Volatile EndTime = "" '総時間と開始時刻が空白なら、終了時間は空白 If TTime = "" And STime = "" Then Exit Function EndTime = CVErr(xlErrValue) '総時間と開始時刻の何れかが空白なら、終了時間は#VALUE If TTime = "" Or STime = "" Then Exit Function '総時間と開始時刻の何れかが不正なら、終了時間は#VALUE If IsNumeric(TTime) = False Or IsNumeric(STime) = False Then Exit Function '休憩時間テーブルが２列でないなら、終了時間は#VALUE If TTable.Columns.Count <> 2 Then Exit Function etime = STime + (TTime / 1440) For i = 1 To TTable.Rows.Count If TTable.Cells(i, 1).Value > STime And TTable.Cells(i, 1).Value < etime Then '開始時間＜休憩開始時間かつ休憩開始時間＜終了時間なら、休憩時間を終了時間に加算 etime = etime + (TTable.Cells(i, 2).Value - TTable.Cells(i, 1).Value) ElseIf TTable.Cells(i, 1).Value + 1 > STime And TTable.Cells(i, 1).Value + 1 < etime Then '開始時間＜休憩開始時間かつ休憩開始時間＜終了時間なら、休憩時間を終了時間に加算（休憩開始時間は24H以降を考慮） etime = etime + (TTable.Cells(i, 2).Value - TTable.Cells(i, 1).Value) End If Next EndTime = etime End Function

tatsu99 · Answer

No3です。補足ありがとうございました。１．休憩時間の ⑥2:30～2:50 ⑦5:30～6:00 については、翌日の2:30、5:50と理解しました。２．又、開始時間ですが、日勤が8:30ということなので開始時間4:00のようなケースもないという前提です。もし、あるとすると、⑦を休憩時間の範囲に含めるかどうかも検討の余地がありますが、このようなケースはないものとします。３．以下のマクロを標準モジュールに登録してください。 --------------------------------------------------------------- Option Explicit 'TTime：総時間（単位：分） 'STime：開始時間（HH:MM形式） 'TTable:休憩時間テーブルの範囲（開始時間の先頭セル：終了時間の最後のセル） '上記の情報から終了時間（HH:MM）を算出する '終了時間＝開始時間＋総時間＋休憩時間テーブルの該当休憩時間の和 Public Function EndTime(ByVal TTime As Variant, ByVal STime As Variant, ByRef TTable As Range) As Variant Dim i As Long Dim etime As Variant Application.Volatile EndTime = "" '総時間と開始時刻が空白なら、終了時間は空白 If TTime = "" And STime = "" Then Exit Function EndTime = CVErr(xlErrValue) '総時間と開始時刻の何れかが空白なら、終了時間は#VALUE If TTime = "" Or STime = "" Then Exit Function '総時間と開始時刻の何れかが不正なら、終了時間は#VALUE If IsNumeric(TTime) = False Or IsNumeric(STime) = False Then Exit Function '休憩時間テーブルが２列でないなら、終了時間は#VALUE If TTable.Columns.Count <> 2 Then Exit Function etime = STime + (TTime / 1440) For i = 1 To TTable.Rows.Count If etime < 1 Then '終了時刻が24H以前なら If TTable.Cells(i, 1).Value > STime And TTable.Cells(i, 1).Value < etime Then '開始時間＜休憩開始時間かつ休憩開始時間＜終了時間なら、休憩時間を終了時間に加算 etime = etime + (TTable.Cells(i, 2).Value - TTable.Cells(i, 1).Value) End If Else '終了時刻が24H以降なら If TTable.Cells(i, 1).Value + 1 > STime And TTable.Cells(i, 1).Value + 1 < etime Then '開始時間＜休憩開始時間かつ休憩開始時間＜終了時間なら、休憩時間を終了時間に加算（休憩開始時間は24H以降を考慮） etime = etime + (TTable.Cells(i, 2).Value - TTable.Cells(i, 1).Value) End If End If Next EndTime = etime End Function -------------------------------------------------- ４．添付図のようなレイアウトの場合ですが、 C2へ =EndTime(A2,B2,$E$2:$F$8) と入力し、オートフィルで下へコピーしてください。 EndTimeはユーザー定義関数です。第1パラメータ：総時間のセル第2パラメータ：開始時間のセル第3パラメータ：休憩時間テーブルの範囲（見出しは含めない）となっています。５．なお、以下のようなケースは特別な結果を返します。 ①総時間と開始時間が空白の場合、結果は空白(添付図の赤線で囲んだところ) ②総時間と開始時間の何れかが空白の場合、結果はエラー(添付図の赤線で囲んだところ) ③総時間と開始時間の何れかが不正の場合、結果はエラー(添付図の赤線で囲んだところ) ④休憩時間テーブルの範囲が２列でない場合、結果はエラー以上、不明点があれば補足してください。

tatsu99 · Answer

１．入力される総時間の最大値はいくつでしょうか。（極端な話ですが2日間＝2880などもありえますか）

２．今後、休憩時間が24時をまたがることもありますか。
23:30～0:30のようなケースです。

エクセルの時間計算について

No7です。

No.2です。

何度もごめんなさい。

No.６・9です。

No.6です。

No5です。

こんにちは！

No4です。

No3です。

１．入力される総時間の最大値はいくつでしょうか。

似たような質問が見つかりました

関連するカテゴリからQ&Aを探す

デイリーランキングこのカテゴリの人気デイリーQ&Aランキング

マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング