Countifs where date is less than

[stevemc2]

Hi

I'm struggling with getting a formula that will do the above to work.

i was actually trying to obtain a count where a date is older than 8 years ago but could not get that to work. So I then tried entering that formula (=DATE(YEAR(TODAY())-8,MONTH(TODAY()),DAY(TODAY()))) in to it's own cell (Y1) and then referring to that n my formula; however I still cannot get this to work.

My formula now is =SUM(COUNTIF(Report!$G$1:$G$100000,"<"&Summary!Y1)) but the result returned is 0 when I know that there are dates within the column that are older than 8 years ago. I've tried amending Y1 to be an actual date but to no avail.
I've also tried amending the date to one that I can see actually exists in the column of dates; and have found that "=" works. But I can't get either "<" or ">" to work.

Could you advise where I may be going wrong?

Thanks

 

[william_man]

What format are your dates in Report!$G$1:$G$100000? Would you be able to share some sample data from that range please?

 

[RoryA]

Just FYI, you don't need SUM there as you only have one criterion, so it's just:

=COUNTIF(Report!$G$1:$G$100000,"<"&Summary!Y1)

 

[stevemc2]

Thanks

The dates are in (long) date format. I'm not sure How i can give you sample data other than copying it in to this reply (which then just comes out as unformatted text) - but it is just a column of dates that I am looking to run the formula on.

 

[stevemc2]

Thanks for the reply.
The formula is actually a number of nested countifs - but I have continued removing these, and reverted to just countif in order to try and understand why the formula won't work (to no avail).

 

[RoryA]

I suspect your dates are actually text, not true dates. If you enter:

=isnumber(L2)

in a cell (assuming L2 contains one of your dates), does it return true or false?

 

[stevemc2]

That looks to be the case - it returns false.

Thanks for your reply - and for others.
It ended up unnecessary as I have been required to match a process elsewhere that is not actually using anything to generate the actual date 8 years ago but is going to be just an approximation (i.e. 2952 days ago) - so I will be matchhing that in my formula.