How to apply WorksheetFunction.CountIfs depending on time?

Question

I am trying to count records on Sheet2.

Dim wksdata As Worksheet
Dim xyz as String
Dim Time as String

Set wksdata = Sheets("Data")

Time = Date + TimeValue("08:00:00") 'Set Time value as todays date 8am

'set calculations worksheet cell I15 as the time value
Worksheets("Calculations").Range("I15").Value = Time

'converts time column to time format
Worksheets("Data)").Range("U:U").NumberFormat = "dd/mm/yyyy hh:mm:ss"

xyz = WorksheetFunction.CountIfs(wksdata.Range("I:I"), "xyz", _
  wksdata.Range("K:K"), "C", wksdata.Range("U:U"), "<" & _
  Worksheets("Calculations").Range("I15").Value)

If I apply this directly in a worksheet it works with the same logic.

Why isn't it working in VBA?

Answer 1

Try and use a different variable for setting the date and time. "Time" is built in VBA and will return the current time and so Range("I15").Value will be set to the current time and not 08:00 as required

Dim wksdata As Worksheet
Dim xyz as String
Dim startTime as String

Set wksdata = Sheets("Data")
startTime = Date + TimeValue("08:00:00") 'Set Time value as todays date 8am
Worksheets("Calculations").Range("I15").Value = startTime 'set calculations 
worksheet cell I15 as the time value

Worksheets("Data)").Range("U:U").NumberFormat = "dd/mm/yyyy hh:mm:ss"  
'converts time column to time format


xyz = WorksheetFunction.CountIfs(wksdata.Range("I:I"), "xyz", 
wksdata.Range("K:K"), "C", wksdata.Range("U:U"), "<" & 
Worksheets("Calculations").Range("I15").Value)

Answer 2

The date part needs to be converted into a Long. Once this is done, the formula works correctly. Here is a sample of how this could be done using a similar formula:

Sub test_method()

Range("B1") = Now()
Range("B1").NumberFormat = "dd/mm/yyyy hh:mm:ss"

'Trying to calculate using a VBA date in the worksheet formula doesn't work
'This returns 0
Debug.Print WorksheetFunction.CountIfs(Range("B1:B10"), ">" & (Now() - 0.75))

'Now we change it to a long and it yields the correct answer
'returns 1
Debug.Print WorksheetFunction.CountIfs(Range("B1:B10"), ">" & CLng((Now() - 0.75)))

End Sub