Help with figuring out last date

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

Jim

Hi
I have a carpet-cleaning business. I have set up a spreadsheet as
follows:

Date of service First Name Last Name Address Price #
of Jobs Alert


By the end of the year I may have a 1000 names some of which may have been
done several times in the year. I want to be alerted when the LAST service
done for any name is over 6 months old and have it Alert me in the Alert
column. If a person had their carpets cleaned on Jan 5, Jun 10 and Nov 23, I
need a formula that will alert me only 6 months after the last date(Nov 23)
not 6 months after Jan 5 or Jun 10. Using Sumproduct I can get that I have
done 3 jobs for the person, but how can I get the 6-month alert to work for
only the last of the 3 dates.
I want to be able to send out reminder cards for those who have not been
serviced in the last 6 months. Any ideas? Thanks much! Jim
 
Ji

One way. Add a new column that is the Most Recent Date Serviced. Assuming that your data is in the area A2:G4 in the format you provided, then array enter the formula in H2 and copy down
=MAX(($B$2:$B$4=B2)*($C$2:$C$4=C2)*($A$2:$A$4)
This will provide the most recent date that the customer was services, using the first name / last name combination as being unique. Modify as required to get the unique listing

In the Alert column (G) enter the formul
=NOW()>edate(H2,6
and copy down. If the last service was more than 6 months ago, then the result will be TRUE. You could use conditional formatting to highlight these cells or an if statement to make some relevant comment. Naturally, this will appear for each appearance for the customer

Tony
 
Hi
I have a carpet-cleaning business. I have set up a spreadsheet as
follows:

Date of service First Name Last Name Address Price #
of Jobs Alert


By the end of the year I may have a 1000 names some of which may have been
done several times in the year. I want to be alerted when the LAST service
done for any name is over 6 months old and have it Alert me in the Alert
column. If a person had their carpets cleaned on Jan 5, Jun 10 and Nov 23, I
need a formula that will alert me only 6 months after the last date(Nov 23)
not 6 months after Jan 5 or Jun 10. Using Sumproduct I can get that I have
done 3 jobs for the person, but how can I get the 6-month alert to work for
only the last of the 3 dates.
I want to be able to send out reminder cards for those who have not been
serviced in the last 6 months. Any ideas? Thanks much! Jim
Click to expand...

A lot depends on how you have your database set up.

Perhaps this will give you an idea of an approach.

Assume you have the customer names in A1:A1000 and the carpet cleaning date in
B1:B1000.

In a separate column, place a list of customers -- let us say that these
thousand jobs are done on 200 customers. Make this column F.

The last date that a particular customer had their carpet cleaned would be
given by the *array-entered* formula:

=MAX((F2=$A$1:$A$1000)*($B$1:$B$1000))

To *array-enter* a formula, hold down <ctrl><shift> while hitting <enter>. XL
will place braces {...} around the formula.

So you would enter the formula in G2 and copy it down as far as needed.

You could then use a conditional format on the cells that had the last date.

Format/Conditional Format/Formula Is: =TODAY()>(G2+45)

If you select all the cells in column G before entering the formula, it will be
entered into each cell, and the cell reference adjusted appropriately. Or you
can enter it in G2 and use the format painter to copy it to the other cells.

I like to use a red fill with a bold white font to make it stand out.

Then, whenever you open that sheet and it recalculates, those who are over 6
months will stand out quite readily.


HTH,

--ron
 
Jim,
Try this array formula (Entered while Ctrl and Shift keys are being held
down)
=LARGE(($C$2:$C$351=C2)*($A$2:$A$351),1)<$J$1
where J1 holds the formula =Today()-180
and answer will be True or False
this will have a problem with same last names so I think better to have full
name column and do the evaluation on that column
HTH
Cecil
 
Thanks that was what I needed! Jim


Ron Rosenfeld said:
A lot depends on how you have your database set up.

Perhaps this will give you an idea of an approach.

Assume you have the customer names in A1:A1000 and the carpet cleaning date in
B1:B1000.

In a separate column, place a list of customers -- let us say that these
thousand jobs are done on 200 customers. Make this column F.

The last date that a particular customer had their carpet cleaned would be
given by the *array-entered* formula:

=MAX((F2=$A$1:$A$1000)*($B$1:$B$1000))

To *array-enter* a formula, hold down <ctrl><shift> while hitting <enter>. XL
will place braces {...} around the formula.

So you would enter the formula in G2 and copy it down as far as needed.

You could then use a conditional format on the cells that had the last date.

Format/Conditional Format/Formula Is: =TODAY()>(G2+45)

If you select all the cells in column G before entering the formula, it will be
entered into each cell, and the cell reference adjusted appropriately. Or you
can enter it in G2 and use the format painter to copy it to the other cells.

I like to use a red fill with a bold white font to make it stand out.

Then, whenever you open that sheet and it recalculates, those who are over 6
months will stand out quite readily.


HTH,

--ron
Click to expand...
 
Back
Top