時間範囲判定応用: A列に日付、B列に時間、C列に値が設定されている履歴データの指定時間範囲内のC列の値の平均値を取得する
<分類:値取得>
<使用例:対象範囲(開始)の日付(2019/8/16)時間(02:00:00)~対象範囲(終了)の日付(2019/8/16)時間(13:00:00)の平均値(30.375)を取得する>
<注意:サンプルデータを作成するプログラムになっていますので、Sheet1は何もデータがないシートをご用意ください>
<プログラム例>
Sub 使用例() Dim fdate As Date Dim ftime As Date Dim gdate As Date Dim gtime As Date Dim tdate As Date Dim ttime As Date Dim i As Long Dim goukei As Single Dim gcnt As long Worksheets("Sheet1").Select 'サンプルデータ作成 Cells(1, 1) = "2019/8/16" Cells(1, 2) = "00:00:00" Cells(1, 3) = "28.7" Cells(2, 1) = "2019/8/16" Cells(2, 2) = "01:00:00" Cells(2, 3) = "29.0" Cells(3, 1) = "2019/8/16" Cells(3, 2) = "02:00:00" Cells(3, 3) = "29.1" Cells(4, 1) = "2019/8/16" Cells(4, 2) = "03:00:00" Cells(4, 3) = "29.1" Cells(5, 1) = "2019/8/16" Cells(5, 2) = "04:00:00" Cells(5, 3) = "29.2" Cells(6, 1) = "2019/8/16" Cells(6, 2) = "05:00:00" Cells(6, 3) = "29.1" Cells(7, 1) = "2019/8/16" Cells(7, 2) = "06:00:00" Cells(7, 3) = "29.1" Cells(8, 1) = "2019/8/16" Cells(8, 2) = "07:00:00" Cells(8, 3) = "29.5" Cells(9, 1) = "2019/8/16" Cells(9, 2) = "08:00:00" Cells(9, 3) = "30.1" Cells(10, 1) = "2019/8/16" Cells(10, 2) = "09:00:00" Cells(10, 3) = "31.6" Cells(11, 1) = "2019/8/16" Cells(11, 2) = "10:00:00" Cells(11, 3) = "32.5" Cells(12, 1) = "2019/8/16" Cells(12, 2) = "11:00:00" Cells(12, 3) = "31.7" Cells(13, 1) = "2019/8/16" Cells(13, 2) = "12:00:00" Cells(13, 3) = "32.2" Cells(14, 1) = "2019/8/16" Cells(14, 2) = "13:00:00" Cells(14, 3) = "31.3" Cells(15, 1) = "2019/8/16" Cells(15, 2) = "14:00:00" Cells(15, 3) = "31.6" Cells(16, 1) = "2019/8/16" Cells(16, 2) = "15:00:00" Cells(16, 3) = "30.9" Cells(17, 1) = "2019/8/16" Cells(17, 2) = "16:00:00" Cells(17, 3) = "30.8" Cells(18, 1) = "2019/8/16" Cells(18, 2) = "17:00:00" Cells(18, 3) = "26.9" Cells(19, 1) = "2019/8/16" Cells(19, 2) = "18:00:00" Cells(19, 3) = "27.7" Cells(20, 1) = "2019/8/16" Cells(20, 2) = "19:00:00" Cells(20, 3) = "28.0" Cells(21, 1) = "2019/8/16" Cells(21, 2) = "20:00:00" Cells(21, 3) = "28.3" Cells(22, 1) = "2019/8/16" Cells(22, 2) = "21:00:00" Cells(22, 3) = "28.8" Cells(23, 1) = "2019/8/16" Cells(23, 2) = "22:00:00" Cells(23, 3) = "29.0" Cells(24, 1) = "2019/8/16" Cells(24, 2) = "23:00:00" Cells(24, 3) = "28.7" fdate = "2019/8/16" ftime = "02:00:00" tdate = "2019/8/16" ttime = "13:00:00" goukei = 0 gcnt = 0 i = 1 Do While Cells(i, 1) <> "" gdate = Cells(i, 1) gtime = Cells(i, 2) If 時間範囲判定(fdate, ftime, gdate, gtime, tdate, ttime) = "内" Then goukei = goukei + Cells(i, 3) gcnt = gcnt + 1 End If i = i + 1 Loop MsgBox ("平均は" & goukei / gcnt & "です") End Sub Function 時間範囲判定(fdate As Date, ftime As Date, gdate As Date, gtime As Date, tdate As Date, ttime As Date) Dim fr As String Dim tr As String Dim ftr As String If DateDiff("d", fdate, gdate) > 0 Then fr = "大" End If If DateDiff("d", fdate, gdate) = 0 Then If DateDiff("s", ftime, gtime) >= 0 Then fr = "大" Else fr = "小" End If End If If DateDiff("d", fdate, gdate) < 0 Then fr = "小" End If If DateDiff("d", gdate, tdate) > 0 Then tr = "大" End If If DateDiff("d", gdate, tdate) = 0 Then If DateDiff("s", gtime, ttime) >= 0 Then tr = "大" Else tr = "小" End If End If If DateDiff("d", gdate, tdate) < 0 Then tr = "小" End If ftr = fr & tr Select Case ftr Case "大大" 時間範囲判定 = "内" Case Else 時間範囲判定 = "外" End Select End Function
時間の範囲判定をする→開始(FROM)~終了(TO)の日付と時間、比較対象の日付と時間をセットして範囲に入っているかどうかを判定する
<分類:判定>
<使用例:対象範囲(開始)の日付と時間、対象日付と時間、対象範囲(終了)の日付と時間(13:00:00)をセットして、範囲内の場合は内、範囲外の場合は外と判定する>
<具体例:対象範囲(開始)の日付(2019/8/16)時間(02:00:00)≦対象の日付(2019/8/16)時間(01:59:59)≦対象範囲(終了)の日付(2019/8/16)時間(13:00:00)は成立しないので範囲外と判定>
<具体例:対象範囲(開始)の日付(2019/8/16)時間(02:00:00)≦対象の日付(2019/8/16)時間(02:00:00)≦対象範囲(終了)の日付(2019/8/16)時間(13:00:00)が成立するので範囲内と判定>
<具体例:対象範囲(開始)の日付(2019/8/16)時間(02:00:00)≦対象の日付(2019/8/16)時間(13:00:00)≦対象範囲(終了)の日付(2019/8/16)時間(13:00:00)が成立するので範囲内と判定>
<具体例:対象範囲(開始)の日付(2019/8/16)時間(02:00:00)≦対象の日付(2019/8/16)時間(13:00:01)≦対象範囲(終了)の日付(2019/8/16)時間(13:00:00)は成立しないので範囲外と判定>
<プログラム例>
Sub 使用例() Dim fdate As Date Dim ftime As Date Dim gdate As Date Dim gtime As Date Dim tdate As Date Dim ttime As Date Dim rtn1 As String fdate = "2019/8/16" ftime = "02:00:00" gdate = "2019/8/16" gtime = "01:59:59" tdate = "2019/8/16" ttime = "13:00:00" rtn1 = 時間範囲判定(fdate, ftime, gdate, gtime, tdate, ttime) MsgBox (gdate & " (" & gtime & ") は、" & Chr(13) & fdate & " (" & ftime & ") ~ " & tdate & " (" & ttime & ") の範囲" & rtn1 & "です") fdate = "2019/8/16" ftime = "02:00:00" gdate = "2019/8/16" gtime = "02:00:00" tdate = "2019/8/16" ttime = "13:00:00" rtn1 = 時間範囲判定(fdate, ftime, gdate, gtime, tdate, ttime) MsgBox (gdate & " (" & gtime & ") は、" & Chr(13) & fdate & " (" & ftime & ") ~ " & tdate & " (" & ttime & ") の範囲" & rtn1 & "です") fdate = "2019/8/16" ftime = "02:00:00" gdate = "2019/8/16" gtime = "13:00:00" tdate = "2019/8/16" ttime = "13:00:00" rtn1 = 時間範囲判定(fdate, ftime, gdate, gtime, tdate, ttime) MsgBox (gdate & " (" & gtime & ") は、" & Chr(13) & fdate & " (" & ftime & ") ~ " & tdate & " (" & ttime & ") の範囲" & rtn1 & "です") fdate = "2019/8/16" ftime = "02:00:00" gdate = "2019/8/16" gtime = "13:00:01" tdate = "2019/8/16" ttime = "13:00:00" rtn1 = 時間範囲判定(fdate, ftime, gdate, gtime, tdate, ttime) MsgBox (gdate & " (" & gtime & ") は、" & Chr(13) & fdate & " (" & ftime & ") ~ " & tdate & " (" & ttime & ") の範囲" & rtn1 & "です") End Sub Function 時間範囲判定(fdate As Date, ftime As Date, gdate As Date, gtime As Date, tdate As Date, ttime As Date) Dim fr As String Dim tr As String Dim ftr As String If DateDiff("d", fdate, gdate) > 0 Then fr = "大" End If If DateDiff("d", fdate, gdate) = 0 Then If DateDiff("s", ftime, gtime) >= 0 Then fr = "大" Else fr = "小" End If End If If DateDiff("d", fdate, gdate) < 0 Then fr = "小" End If If DateDiff("d", gdate, tdate) > 0 Then tr = "大" End If If DateDiff("d", gdate, tdate) = 0 Then If DateDiff("s", gtime, ttime) >= 0 Then tr = "大" Else tr = "小" End If End If If DateDiff("d", gdate, tdate) < 0 Then tr = "小" End If ftr = fr & tr Select Case ftr Case "大大" 時間範囲判定 = "内" Case Else 時間範囲判定 = "外" End Select End Function
時間がジャストかどうかを調べる(30分もジャストで判定するバージョン)
<分類:判定>
<使用例:時間が何時ジャストであるか判定する>
<00:00:00 → 判定結果:ジャスト>
<00:00:01 → 判定結果:ジャストではない>
<23:00:00 → 判定結果:ジャスト>
<23:30:00 → 判定結果:ジャスト>
<23:00:30 → 判定結果:ジャストではない>
<プログラム例>
Sub 使用例() Dim t As String Dim s As String t = "00:00:00" s = 時間ジャスト調査00分と30分(t) If s = "*" Then MsgBox (t & "はジャストです") Else MsgBox (t & "はジャストではありません") End If t = "00:00:01" s = 時間ジャスト調査00分と30分(t) If s = "*" Then MsgBox (t & "はジャストです") Else MsgBox (t & "はジャストではありません") End If t = "23:00:00" s = 時間ジャスト調査00分と30分(t) If s = "*" Then MsgBox (t & "はジャストです") Else MsgBox (t & "はジャストではありません") End If t = "23:30:00" s = 時間ジャスト調査00分と30分(t) If s = "*" Then MsgBox (t & "はジャストです") Else MsgBox (t & "はジャストではありません") End If t = "23:00:30" s = 時間ジャスト調査00分と30分(t) If s = "*" Then MsgBox (t & "はジャストです") Else MsgBox (t & "はジャストではありません") End If End Sub Function 時間ジャスト調査00分と30分(p1 As String) Dim s As String s = 共通_cov_time_t9(p1) Select Case Mid(s, 4, 4) Case "0000" 時間ジャスト調査00分と30分 = "*" Case "3000" 時間ジャスト調査00分と30分 = "*" Case Else 時間ジャスト調査00分と30分 = "" End Select End Function Function 共通_cov_time_t9(p1 As String) As String 共通_cov_time_t9 = "T" & CStr((Format(p1, "hhnnss"))) End Function