date calculation
 

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.




--
Nisha P

date calculation
 

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

"nishkrish" wrote:

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.




--
Nisha P

date calculation
 

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


--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"nishkrish" wrote:

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.




--
Nisha P

date calculation
 

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


"Sean Timmons" wrote:

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

"nishkrish" wrote:

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.




--
Nisha P

date calculation
 

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
--
Nisha P


"Luke M" wrote:

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


--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"nishkrish" wrote:

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.




--
Nisha P

date calculation
 

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)
--
Nisha P


"Luke M" wrote:

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


--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"nishkrish" wrote:

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.




--
Nisha P

date calculation
 

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" wrote:

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)
--
Nisha P


"Luke M" wrote:

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


--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"nishkrish" wrote:

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.




--
Nisha P

date calculation
 

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


"Barb Reinhardt" wrote:

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" wrote:

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)
--
Nisha P


"Luke M" wrote:

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


--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"nishkrish" wrote:

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.




--
Nisha P

date calculation
 

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


On Fri, 21 Aug 2009 12:33:01 -0700, nishkrish
wrote:

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

date calculation
 

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



"Gord Dibben" wrote:

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


On Fri, 21 Aug 2009 12:33:01 -0700, nishkrish
wrote:

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

date calculation
 

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

"Gord Dibben" wrote:

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


On Fri, 21 Aug 2009 12:33:01 -0700, nishkrish
wrote:

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