Conditionally format only most recent renewal within a certain date range.

OK, what I want to do is this.. I've got a list of training records. People have to do various courses and repeat to keep them up to date. How can I set up a formula (to use with conditional formatting) that only identifies the most recent entry for a course/user and then sees if that is within 90 days of today. So if someone's most recent entry for a course renewal was 28/2/2018 it should be formatted, but all other entries for that person and course should not be. If the most up to date renewal is 31/12/2018, it should NOT be formatted. Thanks
EE_Course_CF.xlsx
LVL 1
agwalshAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rgonzo1971Commented:
Hi,

pls try
=(H5<=TODAY()+90)*(H5>=TODAY())

Open in new window

Regards
0
agwalshAuthor Commented:
Tried that. Got all zeros. I need the formula to take the fact that some people will have taken this course more than once and I only want the most recent occurrence of the person/course combination. Am attaching what I got when I tried your formula. Thanks anyway though :-)
EE_Course_CF_01.xlsx
0
Rgonzo1971Commented:
Sorry can't understand logic of Col I
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

agwalshAuthor Commented:
Basically what I want to happen is this: Identify individuals (repeated entries) e.g. Harding Brandybuck has done the beer drinking course twice. The first time he did the course, the renewal date was 24/9/2018, row 18. However he did the course again and this time the renewal date is 6/10/2018. If we just go by date only, the row 18 entry is out of date. However, I do not want it highlighted as he did the same course again and that date 6/10/2018 is more than 90 days away from today (7/2/2018) so he is technically up to date with the course.  However he also did the Breadmaking course and the Wizardry course and his certification for both of those has lapsed therefore they should be highlighted... Thank you :-) Appreciate the assistance :-)
0
Rob HensonFinance AnalystCommented:
This formula in column K will give result of TRUE or FALSE:

=SUMIFS([Renewal date],[Name],[@Name],[Course attended],[@[Course attended]],[Date of course],MAXIFS([Date of course],[Name],[@Name],[Course attended],[@[Course attended]]))<TODAY()+90

This gets the MAX course date for the person and course from column G. It then uses that date as a criteria in the SUMIFS along with name and course to get the date from column H and compares it to TODAY+90

As this gives a TRUE/FALSE result it can be used as a formula in Conditional Formatting.

See attached.
EE_Course_CF_01.xlsx
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Rgonzo1971Commented:
Or use directly in CF
=IF(AND(H5<TODAY()-90,H5=MAX(--($A$5:$A$29=A5)*--(F5=$F$5:$F$29)*($H$5:$H$29))),1,0)

Open in new window

0
Rgonzo1971Commented:
or in the table as an array formula (Ctrl+Shift+Enter)
=IF(AND([@[Renewal date]]<TODAY()-90;[@[Renewal date]]=MAX(--([Name]=[@Name])*--([Course attended]=[@[Course attended]])*([Renewal date])));1;0)

Open in new window

0
agwalshAuthor Commented:
@Rob Henson - checked this and it works beautifully. However I notice you are using Maxifs - can this be used in earlier versions of Excel? My user has Excel 2010 Also I understand how the Sumifs works, but what is the explanation of this first bit? "[Renewal date],[Name],[@Name],[Course attended],[@[Course attended]]" Thank you.
0
Rob HensonFinance AnalystCommented:
Hi agwalsh, apologies for delay in replying.

Wasn't sure when MAXIFS came in to Excel, looks like it might have been Excel 2016, so no use for Excel 2010 unless somebody can convert it to maybe a SUMPRODUCT or something similar.

However, I will still explain the logic behind the formula:

The square brackets around the criteria indicate that it is looking at a Table rather than a standard List. As you know the syntax for SUMIFS is:

=SUMIFS(SumRange, CriteriaRange1, Criteria1, CriteriaRange2, Criteria2, CriteriaRange3, Criteria3,  etc etc

So:
SumRange is covered by [Renewal date] ie the Renewal Date column from the Table - as Dates are stored internally as numbers, the result of summing a columns of dates where you know that there will only be one entry that matches the criteria is a number representing a single date.

CriteriaRange1 is covered by [Name] ie the Name column, the criteria is then covered by [@Name], this translates to 'the value from Name column on this row'

CriteriaRange2 similarly for Course Attended column.

CriteriaRange3 refers to Date of Course

The MAXIFS gets the MAX date from Course Date where Name and Course Name match. This Date is then used as a Criteria for Date of Course (Criteria3)

I suspect the MAXIFS could be achieved with MAXIF in an array formula or something similar but not sure.

Hope that helps explain although you may not be able to use it.
0
agwalshAuthor Commented:
Thanks for the explanation, much appreciated :-) I  pulled it into Excel 2010 and the formula changed by putting .xlfn before the MaxIfs and making everything true. :-( . That is annoying! I now need to work it through myself so I understand it properly. Ideally I'd prefer not to use an array formula - simply because the user wouldn't be experienced enough to understand how it works. I suspect I may have to construct a couple of helper columns - not ideal but hopefully should get me there in the end... if you can think of something with sumproduct/index, that would be brilliant. Thank you so much :-)
0
Rob HensonFinance AnalystCommented:
Or maybe concatenate Name and Course into one (hidden) field. You can then use MAXIF on the Date with combined field as criteria.
0
agwalshAuthor Commented:
Sounds like a solution but is there a maxif in 2010 - I checked and doesn't look like it...
0
Rgonzo1971Commented:
Maxif is only from Excel 2016
0
Rob HensonFinance AnalystCommented:
Ok, how about creating a pivot table from original data extracting only the MAX date per name and course. This will then give a table with only one entry per name and course, date attended and renewal date. The renewal date can then have conditional formatting fairly simply with comparison to today+90
0
agwalshAuthor Commented:
I worked it out eventually. I used a concatenated field to put name and course together. Then used the MaxIf array combination as shown http://blog.contextures.com/archives/2011/07/27/finding-min-if-or-max-if-in-excel/ and in a third column used a nested if to identify only the courses that had renewal dates. A bit clunky but hopefully got the job done. Such a pity I couldn't have used the Maxifs combination...thanks again for your help.
0
agwalshAuthor Commented:
As always, Experts Exchange rides to the rescue and helped me see the wood for the trees...My heroes!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.