Hey,
I have a spreadsheet using VBA to timestamp changes in columns. I then want to use COUNTIFS to count those dates that fall within today and 30 days past. The problem I am having is that the COUNTIFS formula doesn't seem to pick up any dates that have been input automatically via the VBA script.
The script I am using is:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim xUpdate As Integer
Dim xTime As Integer
Dim xRow, xCol As Integer
xUpdate = 15
xTime = 16
xRow = Target.Row
xCol = Target.Column
If Target.Text = "a" Then
Cells(Target.Row, Target.Column + 1) = Format(Now(), "DD/MM/YYYY")
End If
If Target.Text <> "" Then
If xCol = xUpdate Then
Cells(xRow, xTime) = Format(Now(), "DD/MM/YYYY")
End If
End If
End Sub
And the COUNTIFS formula is:
=COUNTIFS(Tracker!D:D, ">="&TODAY()-30, Tracker!D:D, "<="&TODAY())
The COUNTIFS formula works if I manually enter the dates in column D, however it does not pick up any dates that are added via the script.
Can anybody help with this?
Thanks.