date calculation

[nishkrish]

hi

i have a database where column b is date column c is customer name when i
input customer name in the system it should check if the same customer name
appears within 15 days of time from his last transaction.

 

[Sean Timmons]

so, if I understand correctly, you enter the date in column B, thencustoemr
name in C. Once you do this, you want to be told if the customer has been
added within 15 days of the date you just entered?

If so:

=if(C200="","",if(countif(B$2:B199,">=C200-15")>0,"Added within 15
days","Not Added"))

Should work

 

[Luke M]

Assuming you input customer name into D2, this will give a count of how many
occurences of that name appear within 15 days of last entry:

=SUMPRODUCT(--(C2:C100=D2),--(B2:B100+15>=MAX((C2:C100=D2)*(B2:B100))))-1

 

[nishkrish]

HI Sean the formula returns only one value "Added within 15 days"

 

[nishkrish]

Hi Luke i am not good with formula evalution when i pasted it it gives error
and returns "value" and or if i paste the formula in d2 #REF!
i am sure i am suppose to modify the formula but littele in depth what the
formula does (-- and you have given a range c2 to c100

 

[nishkrish]

luke

i modified the formula i dont know if it is right cause now it shows "0"
=SUMPRODUCT(C2:C100=D2)*(B2:B100+15)>=MAX((C2:C100=D2)*(B2:B100+15)-1)

 

[Barb Reinhardt]

It looks like you have an array formula in there. You need to commit with
CTRL ALT ENTER so that it works properly.

HTH,
Barb Reinhardt

 

[nishkrish]

I tried that it doesnt work what exactly you mean by Ctrl alt enter how do i
cummit that.

 

[Gord Dibben]

Barb meant CTRL + SHIFT + ENTER because the formula is an "array" formula.

Hold CTRL and SHIFT keys down then hit ENTER key.

Your formula will receive curly braces around it {formula}

Help explains array formulas.


Gord Dibben MS Excel MVP

 

[Nisha]

Hi Gord,

Thanks it helped

But the formula still shows value as "0" still not able to evaluate

the formula i have applied is
=SUMPRODUCT(--(C2:C100=D2),--(B2:B100+15>=MAX((C2:C100=D2)*(B2:B100))))-1

 

[Barb Reinhardt]

Thanks for the save. I do it all the time, I guess I don't pay attention to
the keystrokes.