VBSでDateValueのエラーが発生します

Question

度々申し訳ございません。

VBSを使用して、
「あるセルを参照し、そのセル内の値が既定の値であったら、別セルに固定の値を入力する」

という処理を実施しようとしているのですが、

処理を実行すると、

エラー：型が一致しません。：DateValue
とエラーが表示されます

文字列型と日付型の違いのためということはわかるのですが、
対処方法がよくわかりませんでした。

　⇒csvの日付がyyyy/mm/dd形式のため、
（前段の処理でyyyy/mm/ddにしております）
これを再度日付型として認識させる方法を実現すればいいと思っているのですが、
その実現方法がわかりません。
教えてください

お分かりになられる方、
申し訳ございませんがご教授をお願いいたします

あるセルを参照：[offtime]列
既定の値：「23:59:59」

別セル：翌日の[ontime]列
固定の値：[0:00:00]

入力ファイル：csv_addlog_tmp3.csv

日付1,グループ,U_name,ontime,offtime,act時間
"2019/05/10","Aグループ","AAA","8:29:00","8:30:00","0:01:00"
"2019/05/10","Aグループ","BBB","8:49:00","8:51:00","0:01:00"
"2019/05/10","Aグループ","CCC","8:38:00","11:26:00","0:01:02"
"2019/05/09","Aグループ","AAA","8:28:00","17:04:00","0:01:00"
"2019/05/09","Aグループ","BBB","8:49:00","17:58:00","3:11:52"
"2019/05/09","Aグループ","CCC","8:36:00","19:21:00","0:01:00"
"2019/05/08","Aグループ","AAA","8:31:00","17:04:00","3:36:47"
"2019/05/08","Aグループ","BBB","8:48:00","23:59:59","0:01:00"
"2019/05/08","Aグループ","CCC","8:30:00","22:34:00","0:01:00"
"2019/05/05","Aグループ","BBB","8:51:00","19:10:00","0:01:00"
"2019/05/05","Aグループ","CCC","8:52:00","23:28:00","0:01:00"
"2019/05/04","Aグループ","AAA","8:25:00","17:12:00","1:07:52"
"2019/05/04","Aグループ","BBB","8:48:00","18:46:00","0:01:00"
"2019/05/04","Aグループ","CCC","8:48:00","22:49:00","0:01:00"
"2019/05/03","Aグループ","AAA","8:32:00","23:59:59","0:01:00"
"2019/05/03","Aグループ","BBB","9:01:00","18:13:00","0:01:00"
"2019/05/02","Aグループ","AAA","8:29:00","17:05:00","0:01:00"
"2019/05/02","Aグループ","BBB","9:09:00","18:34:00","0:01:00"
"2019/05/01","Aグループ","AAA","8:28:00","17:07:00","0:01:00"
"2019/05/01","Aグループ","BBB","8:53:00","19:07:00","0:01:00"
"2019/05/01","Aグループ","CCC","8:51:00","21:01:00","0:01:00"

出力ファイル（結果として出したい）：csv_addlog_tmp.csv
⇒5/4のAAAと5/9のBBBのontimeの値が「0:00:00」になっている

日付1,グループ,U_name,ontime,offtime,act時間
"2019/05/10","Aグループ","AAA","8:29:00","8:30:00","0:01:00"
"2019/05/10","Aグループ","BBB","8:49:00","8:51:00","0:01:00"
"2019/05/10","Aグループ","CCC","8:38:00","11:26:00","0:01:02"
"2019/05/09","Aグループ","AAA","8:28:00","17:04:00","0:01:00"
"2019/05/09","Aグループ","BBB","0:00:00","17:58:00","3:11:52"
"2019/05/09","Aグループ","CCC","8:36:00","19:21:00","0:01:00"
"2019/05/08","Aグループ","AAA","8:31:00","17:04:00","3:36:47"
"2019/05/08","Aグループ","BBB","8:48:00","18:40:00","0:01:00"
"2019/05/08","Aグループ","CCC","8:30:00","22:34:00","0:01:00"
"2019/05/05","Aグループ","BBB","8:51:00","19:10:00","0:01:00"
"2019/05/05","Aグループ","CCC","8:52:00","23:28:00","0:01:00"
"2019/05/04","Aグループ","AAA","0:00:00","17:12:00","1:07:52"
"2019/05/04","Aグループ","BBB","8:48:00","18:46:00","0:01:00"
"2019/05/04","Aグループ","CCC","8:48:00","22:49:00","0:01:00"
"2019/05/03","Aグループ","AAA","8:32:00","17:00:00","0:01:00"
"2019/05/03","Aグループ","BBB","9:01:00","18:13:00","0:01:00"
"2019/05/02","Aグループ","AAA","8:29:00","17:05:00","0:01:00"
"2019/05/02","Aグループ","BBB","9:09:00","18:34:00","0:01:00"
"2019/05/01","Aグループ","AAA","8:28:00","17:07:00","0:01:00"
"2019/05/01","Aグループ","BBB","8:53:00","19:07:00","0:01:00"
"2019/05/01","Aグループ","CCC","8:51:00","21:01:00","0:01:00"

記載したコード

sub logon_check()

Dim a, c, co, cr, d(), f, m, n, i, j, so, x, z(5)
Set so = CreateObject("Scripting.FileSystemObject")
f = so.GetParentFolderName(WScript.ScriptFullName)
Set co = so.OpenTextFile(f & "\csv_addlog_tmp3.csv", 1)
Set cr = so.OpenTextFile(f & "\csv_addlog_tmp4.csv", 2, True)

'タイトル業種出力
x = co.ReadLine
cr.WriteLine x


c = - 1

'レコード数分実施
Do Until co.AtEndOfStream
    a = Split(co.ReadLine, ",")
    c = c + 1
    ReDim Preserve d(5, c)
    For i = 0 to 5
        d(i, c) = a(i)
    Next
    Loop
co.Close

Set co = Nothing

        '[offtime]が「23:59:59」の行を選択
For i = 0 to c
    If d(4, i) = "23:59:59"  Then
        m = i
        n = DateAdd("d", 1, d(0, i))
    End If

        '翌日の[ontime]に「00:00:00」を投入
        '表示形式が[h:mm:ss]なので、時刻は[0:00:00]で設定される
For j = m + 1 to c
    If n = DateValue(d(0, j)) and d(4, j) = "23:59:59" and d(2, j) = d(2, i)  Then
'    If n = DateValue(d(0, j)) and DateValue(d(4, j)) = "23:59:59" and d(2, j) = d(2, i)  Then
'    If n = d(0, j) and d(4, j) = "23:59:59" and d(2, j) = d(2, i)  Then
        d(3, j) = "0:00:00"
        Exit For
    End If
    Next
Next
    For i = 0 to c
        For j = 0 to 5
            z(j) = d(j, i)
        Next
        cr.WriteLine Join(z, ",")
    Next
cr.Close
Set cr = Nothing
Set so = Nothing

'バッファを Flush してファイルを閉じる
Set dic = Nothing

end sub

入力ファイル（csv_addlog_tmp3.csv）は、

objADO.Execute _
"SELECT Format([日付],'yyyy/mm/dd') AS 日付1, Format([offtime],'h:nn:ss') as [OFF1] , ｴｰｼﾞｪﾝﾄ , 操作種別 , 端末機No , 部署名 , Format([ontime],'h:nn:ss') as [ON1] " & _
" INTO ~~~

で、作成しています

Answer 1

おそらくDateValueに与えている値がCSVファイルから取ってきた値そのもので、ダブルクォーテーションを含んだ値（例えば"2019/05/01"）になっているからではないかと思います。
単純な対応としては引数に与えるときにダブルクォーテーションを削除してあげればよいでしょう。

If n = DateValue(d(0, j)) and d(4, j) = "23:59:59" and d(2, j) = d(2, i)  Then
　↓
If n = DateValue(Replace(d(0, j),"""","")) and d(4, j) = "23:59:59" and d(2, j) = d(2, i)  Then

同様に"23:59:59"と比較している箇所は正しく判定できていない可能性があります。
"""23:59:59"""と比較しないとダメかもです。

Comments

[amatsuno]

ありがとうございます。
確認したところ、まさにおっしゃる通りでした。
replaceを使用して対応させていただきます