Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with figuring out last date
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with figuring out last date
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with figuring out last date
On Tue, 20 Jan 2004 19:27:50 -0600, "Jim" wrote:
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 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with figuring out last date
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 "Jim" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with figuring out last date
Thanks that was what I needed! Jim
"Ron Rosenfeld" wrote in message ... On Tue, 20 Jan 2004 19:27:50 -0600, "Jim" wrote: 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 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with figuring out last date
On Wed, 21 Jan 2004 09:19:43 -0600, "Jim" wrote:
Thanks that was what I needed! Jim You're welcome. --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
figuring tax | New Users to Excel | |||
Figuring Age | Excel Worksheet Functions | |||
Figuring out a formula | Excel Worksheet Functions | |||
Figuring Vacation Hrs. Earned using Current Date minus Hire Date | Excel Worksheet Functions | |||
Figuring Out A formula to... | New Users to Excel |