countif() not working with VBA populated dates

[dmj120]

I found a perfect VBA script that populates the initial and updated dates onto a summary tab. But when I countif() the populated date, nothing is captured - I have to manually add the same date in an adjacent cell, which I then countif().

Any idea why, and how to fix this?

Private Sub Worksheet_Change(ByVal Target As Range)
'Timestamp Data
'   TeachExcel.com

Dim myTableRange As Range
Dim myDateTimeRange As Range
Dim myUpdatedRange As Range

'Your data table range
Set myTableRange = Range("E6:E11000")

'Check if the changed cell is in the data tabe or not.
If Intersect(Target, myTableRange) Is Nothing Then Exit Sub

'Stop events from running
Application.EnableEvents = False

'Column for the date/time
Set myDateTimeRange = Range("I" & Target.Row)
'Column for last updated date/time
Set myUpdatedRange = Range("J" & Target.Row)

'Determine if the input date/time should change
If myDateTimeRange.Value = "" Then

    myDateTimeRange.Value = Now

End If

'Update the updated date/time value
myUpdatedRange.Value = Now

'Turn events back on
Application.EnableEvents = True
End Sub

 

[RoryA]

Now includes the time, which is probably what is throwing your formulas off. Use Date instead.

 

[dmj120]

I tried date, still doesn't count today's entry.

Now includes the time, which is probably what is throwing your formulas off. Use Date instead.

 

[RoryA]

What’s the formula?

 

[dmj120]

What’s the formula?

=IF(ISBLANK(B8),"",COUNTIF(ModelList!L:L,B8)) --- column L has the VBA populated date

 

[dmj120]

I realized I didn't update the column after changing the code.....changing it to Date works. Thanks!

Now includes the time, which is probably what is throwing your formulas off. Use Date instead.