Counting Dates with Extra Character in cell

I have a spreadsheet that I am tracking dates within. I put in just a date in a cell for a actual completion and a date with a P for pending (12/9/2009 P). I am trying to count yesterdays and today’s date with the P.

I am using is “=COUNTIF(Commission!I3:I796,"=12/8/2009 P")” and it works but I would like to use the “=TODAY()” and “=TODAY()-1”. That way I don’t have to change the formula daily.

I would also like to count the dates that fall into the current week and the next week.

I don’t want to separate the date if at all possible.

Thanks for any help.

You can use the TEXT function, something like this

=COUNTIF(Commission!I3:I796,TEXT(TODAY()-1,"m/d/yyyy")&" P")

for "current week" it depends on your definition. If today is Wednesday 9th then what's the current week, Mon 7th to Sun 13th?

That works great. Thanks so much..

The week would be between 7-13th. then 14-20. Also I would like to list the items that match the week in another tab.

Like.

Current Next
Week Week

12/7/09 12/14/09
12/9/09 12/16/09
12/10/09 12/19/09