date calculation

  • Thread starter Thread starter nishkrish
  • Start date Start date
N

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.
 
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
 
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
 
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
 
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)
 
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
 
I tried that it doesnt work what exactly you mean by Ctrl alt enter how do i
cummit that.
 
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
 
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
 
Thanks for the save. I do it all the time, I guess I don't pay attention to
the keystrokes.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

VBA to automatically cut, paste entire row based on date input and delete row 0
Calculating unearned amount billed 45 days in advance... 1
Excel How to create a Accounts Receivable to calculate days in which payments against invoice are received 0
Help With A Formula - Easy For Those Who Know 0
Microsoft Access 1
COUNTIFS with No Criteria 1
Finding Unique Records 1
calculate total hours based on dates 5

Back
Top