agwalsh
asked on
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
EE_Course_CF.xlsx
ASKER
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
EE_Course_CF_01.xlsx
Sorry can't understand logic of Col I
ASKER
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 :-)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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)SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
@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],[Cour
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.
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.
ASKER
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 :-)
Or maybe concatenate Name and Course into one (hidden) field. You can then use MAXIF on the Date with combined field as criteria.
ASKER
Sounds like a solution but is there a maxif in 2010 - I checked and doesn't look like it...
Maxif is only from Excel 2016
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
ASKER
As always, Experts Exchange rides to the rescue and helped me see the wood for the trees...My heroes!
pls try
Open in new window
Regards