Assist with Match Dates

[rhani111]

Hi All,

Just wondering if anyone can tell me how to do this....I was helped
recently by an awesome guy in the UK with a formula, but it won't
calculate between the dates. It will only see if there are matching
dates and then return values that appear between them, but I need it to
return values if ON the date specified or between the dates. If there is
no end date.....only count between the start and end date.

here is the formula he gave me...

=SUM(OFFSET($C$196,MATCH(A197,$A$197:$A$203,0),MATCH(AY1,$C$13:$Z$13,0)-1,1,MATCH(AY2,$C$13:$Z$13,0)))

C196 being where the data begins
A197 is the first value to match between A197 and A203
AY1 is the start date
C13:Z13 is where the dates appear (not in any specific order)
AY2 is the end date

Thanx all
Rhani

 

[Biff]

Hi!

What are the dates in C13:Z13 ?

What are the dates in AY1 and AY2 ?

Biff

 

[rhani111]

Hi Biff,

The dates in C13:Z13 are 31/07/06 01/08/06 03/08/06 ect

They are never in order because they may work 4days on and then 4off.

The dates in AY1 is 31/07/08
AY2 is 03/08/06

The start and end dates change whenever the user enters the start and
end dates.

I need the forumla to look ON these days AND between them. So far the
formula only looks for the EXACT dates and returns N/A if i was to
enter say start date of 01/08/06 and an end date of the 04/08/06

Hope that clears it up....lol

Rhani

 

[Biff]

Ok....

I'm having a hard time trying to figure out your setup.

This formula will work but we may need to tweak it until I fully understand
your setup:

=SUMPRODUCT(--(C13:Z13>=AY1),--(C13:Z13<=AY2),OFFSET(C196:Z196,MATCH(A197,A197:A203,0),,))

The reason you'd get #N/A with the other formula is if there was no matching
date.

Biff

 

[rhani111]

Thank You so very much Biff,

That worked beautifully!!!!

 

[Biff]

You're welcome. Thanks for the feedback!

Biff