COUNTIFS not working... arghh!

[caj1980]

I am using the following COUNTIFS formula to count the number of rows who meet two conditions. The first condition is that the Status column equals "RESOLVED"' the second condition is that the Resolved Date column is >= (greater than or equal to) Cell A1. Cell A1 is autocalcualted to be today's date minus 7. The end result should tell me how many rows were marked Resolved in the last 7 days (in this example the result should be 2). But it always comes out to ZERO.

=NOW()-7 = 3-Jan-14
Resolved (Last 7d)=	=COUNTIFS(B5:B8,"RESOLVED",C5:C8,">=A1")
Ticket #	Status	Resolved Date
52146	OPEN
52148	RESOLVED	3-Jan-14
52159	RESOLVED	5-Jan-14
52180	RESOLVED	1-Jan-14

<tbody>
</tbody>

Thanks in advance for the help!
Chris J.

 

[caj1980]

custom format of mmm dd yyyy returns the desired dates (Jan 03 2014, Jan 05 2014, Jan 01 2014).

I'm just wondering if it is still something with the =TODAY()-7. I removed the formula in A1 and just inserted the date Jan 03 2014, but same result.

 

[Jonmo1]

I think I found it..

File - Options - Advanced.
Scroll all the way down
UNcheck "Transition formula evaluation"

 

[caj1980]

It was already unchecked! I saw another box for "use 1904 date system" that was unchecked. I checked it and re-saved the formula but still no good. I have now unchecked that box so it is back to default settings.

 

[Jonmo1]

I have no other ideas.

Maybe if I can actually see the file.
PM me if you want to email the file to me.

 

[caj1980]

That would be nice, but the files are on a non-internet system. I could try to recreate the spreadsheet though. My shift is almost over though so I won't be back on until Monday. Thanks for all your help!

 

[Jonmo1]

I saw your book

take a closer look at post #2

Try

=COUNTIFS(B5:B8,"RESOLVED",C5:C8,">="&A1)

 

[caj1980]

Not sure what I missed, but #2 is working now on my recreated spreadsheet. I'll transfer to my real worksheet on my production system when I get back to work on Monday. I'll let you know if that works. Thanks!