Ranges and dates

B

ben simpson

Hello everyone.
I've been searching for a way to formulate the following:

If a A1 is found to be within a named range, and A1 < B1, I need to add 1
to C1, and if its not found within that named range, then add 0 to C1.

I've looked at it for so long that I'm crosseyed already. Help would be
welcomed and I'd be most grateful. Thanks.....Ben
 
B

Bernard Liengme

clarify "If a A1 is found to be within a named range"
do you mean A1 is between to values or is part of a named range?
 
B

ben simpson

In A1 if the TODAY() (to avoid the volatile error).
The named range is a list of dates, (Holiday dates that are upcoming)
In A12:AH13 is the range of dates that I need to check against the named
range. It a work cycle.
A2 is 1 day past the end od the work cycle.

I need to check how many,if any, of the holiday dates fall within the
present work cycle, and place the number of matches that are found in a cell
to be added to keep a running total.

Hope this make sense. Thanks....Ben
 
D

daddylonglegs

If I understand correctly you want to count how many dates within your
range fall between A1 and A2. If so

=SUMPRODUCT(--(A12:AH13>=A1),--(A12:AH13<=A2))

or

=COUNTIF(A12:AH13,">="&A1)-COUNTIF(A12:AH13,">"&A2)
 
B

ben simpson

What I've been using is:
{=SUM(COUNTIF(Holidays,H12:AH13))}
This counts the number of times that the dates match within the work period.
What I was looking for is a way to populate the cell with a 1 on the date it
matches Ex: 1 holiday is added on 1/1/2006 for the new years holiday, and so
on, even if its in the middle of a work cycle. I have each of the actual
dates listed in the named range...Ben
 
D

daddylonglegs

OK try this...

=SUMPRODUCT(--(H12:AH13<=TODAY()),--ISNUMBER(MATCH(H12:AH13,holidays,0)))
 
Top