Hi, my problem is related to Lolla70's previous post that was moved to the programming section. In the profiling of households, the members (Column A) are categorized into groups (Column D) according to their age (Column C). I need help in:




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.

2. Is there a formula that will calculate the age group (Column D) without Column C?


Thank you.

Attached Files

Class by Age.xlsx (83.6 KB, 7 views)

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.


yes:
=VLOOKUP(DATEDIF([@Birthday],ProfileDate,"d"),tAgeGroup,4)

Attached Files

msofficeforums140784Class by Age.xlsx (84.6 KB, 8 views)

Thank you p45cal. The profile or census is done every end of quarter of the calenday year, so the profile date is March 31, 2019 while the comparison date is the previous end of quarter, that is Dec 31, 2018. I'm sorry for not providing the full details, otherwise so much guessing from good Samaritans like you would have been avoided. Taking cues from your formula, I came up with the following conditional formula, and it works fine.
=(VLOOKUP(DATEDIF($b14,$D12,"d"),$A$2:$D$10,4))<>$ D14
Any idea why range names do not work in conditional formatting?

Thanks also for the formula below, dispensing with the extra column computing the age in days and saving precious file space.

are you sure $D12 in the cf formula shouldn't be $D$12?

Right, it should be $D$12. Again, thank you.