Quote:
Originally Posted by Marcia
1. A conditional formatting formula that will highlight the name of a member whose group status (Column D) was changed in the last quarter or 90 days. The profile date is entered in $B11.
|
Not sure what you want here; something that's changed in the last 90 days is comparing the status today with the status 90 days ago. Does your profile date have any relevance at all?
Perhaps you want to compare the profile date with 90 days ago?, or the profile date with 90 days before the profile date?
So I can't give you a defintive answer, but I can get you started. One way is to calculate the classification code twice, once for one date and once for the other, compare the result. If they're the same, no change, if they're different then of course it has changed between those 2 dates.
Conditional formatting wants to see a TRUE/FALSE result for it to decide whether to highlight or not. I've added a column to your lower table which uses two dates: your ProfileDate and a new ComparisonDate (cell D11). So you can experiment adjusting the two dates and watching column E. TRUE means it's changed, FALSE not. The formula in that column can ultimately be the basis of the formula you end up using in conditional formatting - I wouldn't expect that you will need to have column E at all ultimately.
Quote:
Originally Posted by Marcia
2. Is there a formula that will calculate the age group (Column D) without Column C?
|
yes:
=VLOOKUP(DATEDIF([@Birthday],ProfileDate,"d"),tAgeGroup,4)