# Thread: Count cells with a future date

1. Dear Loungers,

I have acolumn which may contain dates, some of which will be in the future and I want to count them. I thought this would work:

countif(D537,">now()")
But nothing is counted, I get a result of zero. I currently have 33 cells in the range, 14 with dates, 11 of those are in the future. Interestingly if I change the formula to look for previous dates by changing the > sign to < I get 19 which is the number of blank cells. How can I make this work?

Thank you.......................... liz

=countif(D537,">" & now())

3. Oh! isn't it easy when you know how, I had played with this before but missed the ampersand.

Thank you

4. Not exactly related, but I'm wondering if you can count cells that are formatted as %. I tried =countif(G3:G32,Style="Percent") but that didn't work. Any ideas?

5. You'll have to use a custom function to do this.

Code:
```Function countPercents(myRange As Range) As Long
Dim myCell As Range
For Each myCell In myRange
If Right(myCell.NumberFormat, 1) = "%" Then
countPercents = countPercents + 1
End If
Next
End Function```

6. Thanks for that solution. I'm not sure where I was reading this the other day on these posts but I thought someone was trying to count cells based on colors. Several people were saying it couldn't be done or it was difficult. Here are a couple functions I wrote several years ago that will do it fairly easily. You just have to put these into the sheet you want to use and then treat them like any of the built in functions that come with Excel. The second function will return the colorIndex number that excel uses for any color. Say you want to know what color is in cell D7. In some other cell, you would put =FindColor(D7) and then that cell would have a number in it that is the Color Index used by Excel.

Function CountColors(R As Range, Col As Integer) As Integer
Dim cell As Range
CountColors = 0
For Each cell In R
If cell.Interior.ColorIndex = Col Then
CountColors = CountColors + 1
End If
Next cell
End Function

Function FindColor(R As Range) As Integer
FindColor = R.Interior.ColorIndex
End Function

7. I think the difficulty they were referring to is that the colors to be counted were the result of conditional formatting, not standard formatting. Conditional formatting colors do no assign a ColorIndex value to a cell.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•