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

:beerchug: