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" <rhani111.2cchri_1155248408.873@excelforum-nospam.com> wrote in
message news:rhani111.2cchri_1155248408.873@excelforum-nospam.com...
>
> 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
>
>
> --
> rhani111
> ------------------------------------------------------------------------
> rhani111's Profile:
> http://www.excelforum.com/member.php...o&userid=19940
> View this thread: http://www.excelforum.com/showthread...hreadid=570557
>

[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" <rhani111.2ccu9j_1155264613.7627@excelforum-nospam.com> wrote in
message news:rhani111.2ccu9j_1155264613.7627@excelforum-nospam.com...
>
> 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
>
>
> --
> rhani111
> ------------------------------------------------------------------------
> rhani111's Profile:
> http://www.excelforum.com/member.php...o&userid=19940
> View this thread: http://www.excelforum.com/showthread...hreadid=570557
>

[rhani111]

Thank You so very much Biff,

That worked beautifully!!!!

[Biff]

You're welcome. Thanks for the feedback!

Biff

"rhani111" <rhani111.2ciz3e_1155550804.8499@excelforum-nospam.com> wrote in
message news:rhani111.2ciz3e_1155550804.8499@excelforum-nospam.com...
>
> Thank You so very much Biff,
>
> That worked beautifully!!!!
>
>
> --
> rhani111
> ------------------------------------------------------------------------
> rhani111's Profile:
> http://www.excelforum.com/member.php...o&userid=19940
> View this thread: http://www.excelforum.com/showthread...hreadid=570557
>