VBSで、指定のカラムの値が23:59:59のとき、別のカラムの値を0:00:00にしたい

VBSで指定のカラムの値に[23:59:59]が指定されていた場合に、別カラムの値を[0:00:00]にしたいと考えています

変更条件：

①OFF1の値が[23:59:59]の時、同じ[u_no]の翌日のON1の値を[0:00:00]にしたい
（2019/5/7,,8:32:00,23:59:59,15,8:06:56 → 2019/5/8,AAA,0:00:00,17:12:00,15,7:25:27
⇒ u_no15の5/7のOFFが[23:59:59]なので、u_no15の5/8のONの値を[0:00:00]）

元csv:csv_tmp1.csv

日付,表示名,ON1,OFF1,u_no,act_time
2019/5/14,AAA,8:29:00,8:30:00,15,0:01:00
2019/5/14,BBB,8:49:00,8:51:00,18,0:01:00
2019/5/14,CCC,8:38:00,11:26:00,19,2:14:36
2019/5/13,,8:28:00,17:04:00,15,7:33:15
2019/5/13,,8:49:00,17:58:00,18,7:54:07
2019/5/13,CCC,8:36:00,19:21:00,19,8:05:25
2019/5/12,,8:31:00,17:04:00,15,7:34:01
2019/5/12,,8:48:00,18:40:00,18,8:50:37
2019/5/12,,8:30:00,23:59:59,19,11:43:57
2019/5/9,,8:51:00,19:10:00,18,8:23:09
2019/5/9,,8:52:00,23:28:00,19,10:33:02
2019/5/8,AAA,8:25:00,17:12:00,15,7:25:27
2019/5/8,,8:48:00,18:46:00,18,7:50:56
2019/5/8,,8:48:00,22:49:00,19,11:43:39
2019/5/7,,8:32:00,23:59:59,15,8:06:56
2019/5/7,,9:01:00,18:13:00,18,6:33:28

変更後csv:csv_tmp2.csv

日付,表示名,ON1,OFF1,u_no,act_time
2019/5/14,AAA,8:29:00,8:30:00,15,0:01:00
2019/5/14,BBB,8:49:00,8:51:00,18,0:01:00
2019/5/14,CCC,0:00:00,11:26:00,19,2:14:36
2019/5/13,,8:28:00,17:04:00,15,7:33:15
2019/5/13,,8:49:00,17:58:00,18,7:54:07
2019/5/13,CCC,8:36:00,19:21:00,19,8:05:25
2019/5/12,,8:31:00,17:04:00,15,7:34:01
2019/5/12,,8:48:00,18:40:00,18,8:50:37
2019/5/12,,8:30:00,23:59:59,19,11:43:57
2019/5/9,,8:51:00,19:10:00,18,8:23:09
2019/5/9,,8:52:00,23:28:00,19,10:33:02
2019/5/8,AAA,0:00:00,17:12:00,15,7:25:27
2019/5/8,,8:48:00,18:46:00,18,7:50:56
2019/5/8,,8:48:00,22:49:00,19,11:43:39
2019/5/7,,8:32:00,23:59:59,15,8:06:56
2019/5/7,,9:01:00,18:13:00,18,6:33:28

コードは以下で確認しましたが、
どちらもうまくいきませんでした

コード1:

vbscript
1Dim dic, fw, sA, dt , sd,ckck5,ckck6
2Const CPATH = "C:\csvtest\"
3Const CINFILE = "csv_tmp1.csv", COUTFILE = "csv_tmp2.csv"
4Const ForReading = 1, ForWriting = 2
5
6Set dic = CreateObject("Scripting.Dictionary")
7
8With CreateObject("Scripting.FileSystemObject")
9Set fw = .OpenTextFile(CPATH & COUTFILE, ForWriting, True)
10With .OpenTextFile(CPATH & CINFILE, ForReading)
11If (Not .AtEndOfStream) Then fw.WriteLine .ReadLine
12While (Not .AtEndOfStream)
13sA = Split(.ReadLine, ",")
14If (UBound(sA) = 5) Then
15
16sd =sA(0)
17ckck5 = replace(sd,"""","")
18'msgbox ckck5
19ckck6 = sA(3)
20'msgbox ckck6
21'dt = CDate(sA(0))
22dt = CDate(ckck5)
23If (dic.Exists(sA(4))) Then
24If (dic(sA(4)) = dt) Then
25sA(2) = "0:00:00"
26dic.Remove sA(4)
27ElseIf (sA(3) = "23:59:59") Then
28dic(sA(4)) = dt + 1
29End If
30ElseIf (sA(3) = "23:59:59") Then
31dic(sA(4)) = dt + 1
32End If
33fw.WriteLine Join(sA, ",")
34End If
35Wend
36.Close
37End With
38fw.Close
39End With
40Set dic = Nothing

このコードでは、[0:00:00]へ変換されません

コード2:

vbscript
1Dim a, c, co, cr, d(), f, m, n, i, j, so, x, z(5),ckck2,ckck1
2Set so = CreateObject("Scripting.FileSystemObject")
3f = so.GetParentFolderName(WScript.ScriptFullName)
4Set co = so.OpenTextFile(f & "\csv_tmp1.csv", 1)
5Set cr = so.OpenTextFile(f & "\csv_tmp2.csv", 2, True)
6x = co.ReadLine
7cr.WriteLine x
8c = - 1
9Do Until co.AtEndOfStream
10a = Split(co.ReadLine, ",")
11c = c + 1
12ReDim Preserve d(5, c)
13For i = 0 to 5
14d(i, c) = a(i)
15Next
16Loop
17co.Close
18Set co = Nothing
19For i = 0 to c
20ckck1=d(3, i)
21msgbox ckck1
22
23If d(3, i) = "23:59:59" Then
24m = i
25n = DateAdd("d", 1, d(0, i))
26End If
27For j = m + 1 to c
28
29'msgbox m
30ckck2=(d(0, j))
31'msgbox ckck2
32'msgbox n
33
34'If n = DateValue(d(0, j)) and d(3, j) = "23:59:59" and d(4, j) = d(4, i) Then
35If d(3, j) = "23:59:59" and d(4, j) = d(4, i) Then
36'msgbox n
37
38d(2, j) = "0:00:00"
39Exit For
40End If
41Next
42Next
43For i = 0 to c
44For j = 0 to 5
45z(j) = d(j, i)
46Next
47cr.WriteLine Join(z, ",")
48Next
49cr.Close
50Set cr = Nothing
51Set so = Nothing
52'MsgBox("Finished!")

このコードでは、同日のONが[0:00:00]になります

すいません。
修正内容のご教授をお願いいたします

行動規範の内容に同意します

回答1件

ベストアンサー

日付の降順に並んでいるのなら逆にたどらなければいけないのでは？

VBScript
1	If d(3, i) = "23:59:59" Then
2		For j = i - 1 to 0 Step -1
3			If d(4, j) = d(4, i) Then
4				d(2, j) = "0:00:00"
5				Exit For
6			End If
7		Next
8	End If