test a range for value....

  • Thread starter Thread starter Jim
  • Start date Start date
J

Jim

Hi,

I have a column of dates, formatted as date dd/mm/yyyy. I would like to have
a formula in one cell that will evaluate the entire range to check if all
the dates are within a given year. If A1 is 01/01/2008, I can put
=year(a1)=2008 in B1 and return a true or false, but I can't seem to get it
to work for a range rather than just an individual cell. Any help would be
greatly appreciated. TIA.

Jim
 
Assuming your dates are in column A, with the search date 01/01/2008
in B1. This formula will count how many dates are not in the same year
as B1:

=SUMPRODUCT(--(YEAR(A1:A1000)=YEAR(B$1)))

I've assumed 1000 dates, so adjust this if you have more (but you
can't specify a complete column, unless you have XL2007).

Obviously, if this results in anything greater than zero, then you
have some dates in a different year, so you could amend it to:

=IF(SUMPRODUCT(--(YEAR(A1:A1000)=YEAR(B$1)))>0,FALSE,TRUE)

where TRUE means that every date is from the same year as B1.

Hope this helps.

Pete
 
To check to see if A1:A10 contain dates in 2008:

=SUMPRODUCT(--(YEAR(A1:A10)=2008))=10
(you'll see true or false)

To check to see if the numeric (dates are numbers) entries in A1:A10 are all in
2008:

=SUMPRODUCT(--(YEAR(A1:A10)=2008))=count(a1:a10)
(you'll see true or false)

Adjust the ranges to match--but you can't use whole columns (except in xl2007).

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html
 
Thank you all very much.


Dave Peterson said:
To check to see if A1:A10 contain dates in 2008:

=SUMPRODUCT(--(YEAR(A1:A10)=2008))=10
(you'll see true or false)

To check to see if the numeric (dates are numbers) entries in A1:A10 are all in
2008:

=SUMPRODUCT(--(YEAR(A1:A10)=2008))=count(a1:a10)
(you'll see true or false)

Adjust the ranges to match--but you can't use whole columns (except in xl2007).

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html
Click to expand...
 
Back
Top