Counter, part II

[Shaolin]

Column I of sheet 1 (starting from row 4) contains the dates of particular events. Is there a way to that is shows # events that have ALREADY taken place versus the number that are in planning on sheet 2 (cell A3 on sheet 2 if it's a date of the past and A4 on sheet 2 if it's a date of the future)??

*Few cells on that column contain TBD.

I think the logic would be:

• Recognize the current date
• start from row 1 of column I and recognize the date
• If the cell reads anything other than a date, skip to next cell of column I
• compare step 1 from step 2
• if it recognizes an old date ? add to counter 1
• if it recognizes a date of the future ? add to counter 2
• print counter 1 on cell B3
• print counter 2 on cell C3

[Shaolin]

I am trying to write an IF statement like

=SUM(IF(TEXT(I4:I65,"d-mmm"))1,0)

excel

=SUMIF(I4:I65,"d-mmm" > 2-May, L4:L65)

My understanding of the above SUMIF statement is as follows: In the range I4:I65, test whether the date in the format "d-mmm" is ahead of the current date of May 2nd, if so add the corrensponding figures in the range of L4:L65. The the L column, I wrote a bunch of 1s, FYI.

Excel is "mad" at the fact I use a '-'. Is there a way that excel can compare a date in a particular cell to the CURRENT date?

Thanks in advanced

[Shaolin]

This is the code I wrote thus far. Now I would like it to print counterPast and counterFuture in cells M2 and N2 respectively. I get the error: "Method not valid without suitable object" (in red text below)

Sub dateCounter()
Dim counterPast As Integer
Dim counterFuture As Integer
Dim eventDate As Date
Dim rngCheck As Range
Dim cel As Range
Dim cel1 As Range
Set rngCheck = Range("I:I")
counterPast = 0
counterFuture = 0
For Each cel In rngCheck
If cel = d - mmm Then
    eventDate = cel
If Now > eventDate Then
        counterPast = counterPast + 1
    End If
End If
For Each cel1 In rngCheck
If cel1 = d - mmm Then
    eventDate = cel1
If Now < eventDate Then
        counterFuture = counterFuture + 1
    End If
End If
Print counterPast("J1")
Print counterFuture("K1")
End Sub

[Bob Phillips]

All you need is a simple SUMPRODUCT formula

="Past dates = "&SUMPRODUCT(--(A4:A1000<TODAY()))

="Future dates = "&SUMPRODUCT(--(A4:A1000>=TODAY()))

This VBA should work

Sub GetData()
Dim i As Long
Dim cPast As Long
Dim cFuture As Long
    For i = 4 To Cells(Rows.Count, "A").End(xlUp).Row
        If Cells(i, "A").Value < Date Then
            cPast = cPast + 1
        Else
            cPast = cPast + 1
        End If
    Next i
Range("B3").Value = "Past dates = " & cPast
    Range("C3").Value = "Future dates = " & cFuture
End Sub

Typo alert

Sub GetData() 
    Dim i As Long 
    Dim cPast As Long 
    Dim cFuture As Long 
    For i = 4 To Cells(Rows.Count, "A").End(xlUp).Row 
        If Cells(i, "A").Value < Date Then 
            cPast = cPast + 1 
        Else 
            cFuture = cFuture + 1 
        End If 
    Next i 
Range("B3").Value = "Past dates = " & cPast 
    Range("C3").Value = "Future dates = " & cFuture 
End Sub

[Shaolin]

Thanks a bunch xld