Finding latest transaction and seeing if it is over a year old.

  • Thread starter Thread starter Jim
  • Start date Start date
J

Jim

I have a database for a carpet cleaning business.I want to be able to send
out reminders if has been over a year since I last cleaned a person's
carpet. But I DON'T want to send out a "year notice" a month after I just
sent one out(A year and a month out).

Each row has: Date of service, First name, last name, address, date of
mailing.

I want to be able to have another column that will alert me if the latest
date of mailing is over a year old. Then I will know it is time to mail a
reminder again. Obviously if I service a person's carpet six months after I
last mailed them a notice I want the alert column to "change" to alert me
when it has been a year since I last serviced their carpet NOT since I last
mailed them a notice which was earlier.
I hope this is making sense. Thanks for any help. Jim
 
A B C D E
DOS FN LN ADD DOM
1
2
3

Assuming the above to be the format you are using....

I would use column F and this equation.
in F3 put:

=IF(AND(NOW()-E2>365,NOW()-A2>365),"MAIL","Don't Mail")

If I interpretted your question correctly, this should do it.

This will make sure that it has been at least a year since the carpet
was serviced and at least a year since the last mailer was sent.


Random
 
Back
Top