How do I show the only the latest date for a particular name ?

hi
I'm attaching a file here. On the Calculations sheet I've set up a formula to pull in only the latest cert from a user on the Data Entry sheet. It works except for one user Joan (she was always a problem.... LOL) It should read 01/04/2015 instead of the 24/3/2016 it does read (that's for someone else). So what am I missing in the formula?
Secondly in the Cumulative days column I have set up a SUMIFS formula designed to do the following: for each user identify how many total  days leave they have taken between their Latest Start Cert date and their Rolling date. I've got answers but I've put the answers I *should* be getting in the desired answers column.  The brief is that the person's leave is calculated from the start date of their cert but this seems to miss dates (I have said this to the user...). Again, what am i missing? Thank you as always
EE-TRR-Calculations.xlsx
LVL 1
agwalshAsked:
Who is Participating?
 
Ejgil HedegaardCommented:
Replace D4 with E4, and it will work for rolling 12 month.

An array formula works on each row in the table (range), and make a result for that row, and stores all results in an array.
The formula result is the sum of the array.
The principle is like making a helper column with a formula for each row, and then sum that column, but with an array formula it is all in one cell.
Be sure to enter the formula with Ctrl+Shift+Enter, if not the formula will only return the value for the first row, or an error.
Excel sets {} brackets to show that it is an array formula.
You can drag the formula, but when copy select the cell below and down for paste.
The first part check the name, and set 1 if there is match.
The second part check if finish date >= rolling date, and if true, calculate the days between the latest of start date and rolling date up to finish date.
If either conditions fails the row result will be 0.
0
 
Ejgil HedegaardCommented:
Use this to get the Latest Start Cert date
=MAX(INDEX(Table1[Cert Start date]*(Table1[Name]=A4),,))

Open in new window


Rolling date on Calculation is 4 years before Latest Start Cert.
That is different from the Rolling 4 years on Data entry, where it is for each cert, not the latest.
The sumifs formula must look at Cert Start date for both.
=SUMIFS(Table1[Days taken],Table1[Name],$A4,Table1[Cert Start date],"<="&$B4,Table1[Cert Start date],">="&$C4)

Open in new window

EE-TRR-Calculations.xlsx
0
 
agwalshAuthor Commented:
Ah, brilliant. Tried all that and worked wonderfully. Thank you. Now one variation on this. Turns out that the total days taken should be inclusive of all the days - including the days taken on the most recent cert..so therefore how do I adapt the formula so that it calculates the days between the rolling 4 year date for the latest cert and the Cert finish date? I need to add a column that calculates the rolling 12 month total. I've done that by adapting your formula for the cumulative days Cumulative Days - 12 months column.
The formula needs to show the total number of days leave taken based on the difference between the    most recent Cert Finish Date and  their most current rolling 4 year (Calculations - D4:D9) and 12 month date (Calculations G4:G9). I tried that with the revised formula and it came out the same as yours even though you used a different date - what am I missing here? Thanks
EE-TRR-Calculations-vers-02.xlsx
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
Ejgil HedegaardCommented:
The dates for each person are close, so 4 years will include all, no matter which dates are used.
Rolling is based on Latest Start Date, and all finish dates are less than 12 month before, so it will include all also.
Cert Finish Date is the maximum date, so all dates, both Cert Start Date and Cert Finish Date will be less than that, and the criteria can be removed.
So it can be this for 12 month (G4)
=SUMIFS(Table1[Days taken],Table1[Name],$A4,Table1[Cert Finish Date],">="&$E4)
and this for 4 years (H4)
=SUMIFS(Table1[Days taken],Table1[Name],$A4,Table1[Cert Finish Date],">="&$D4)
0
 
agwalshAuthor Commented:
The dates can and probably will vary a lot so I need to have the stability built in to ensure it can cope with lots of certs over the 4 year period..let me try this. :-) thank you :-)
0
 
Ejgil HedegaardCommented:
Above formulas check if the finish dates are greater than the latest rolling period start.
It all depend on how you define the rolling periods.
Based on Start dates it will include more than when using Finish dates.
0
 
agwalshAuthor Commented:
Basically the days leave calculated is based on the following: Every time someone gets a cert, their 4 year rolling date is set to 4 years prior to that date and their 12 months rolling date is set 12 months to that date. Therefore the days allowed at that point are based on the total number of days taken in the last 4 years from the latest 4 year rollback and the final date of their most recent cert...ditto for 12 months...
0
 
Ejgil HedegaardCommented:
Gets a cert = Finish date.
Then the rolling periods must be calculated from the Latest Finish Date.
EE-TRR-Calculations-vers-02.xlsx
0
 
agwalshAuthor Commented:
Oh, I wish...that's how the policy rolls. I did check..The rollback is from the start date but the days are calculated to include ALL days...
0
 
Ejgil HedegaardCommented:
Then this how it must be.
EE-TRR-Calculations-vers-02.xlsx
0
 
agwalshAuthor Commented:
GRRR. This is driving me nuts! I've gone back to the drawing board again...I've put my calculations in the sheet marked Overlapping calculations...
Brian should have 165 days as his total in L3. Starting date (4 years rollback) is in cell J3 i.e. 23/3/2016. His finish date is 4/5/2016.
However he has a cert that covers from 1/12/2011 to 30/6/2012. That means that the days between 23/3/2012 and 30/6/2012 (99 days) should be added to that 99 so he should have a total of 165 instead of the 66 I have
How can I incorporate that calculation? My formula is in cell L3.
EE-TRR-Calculations-vers-03.xlsx
0
 
Ejgil HedegaardCommented:
Then column F (Days taken) can not be used, and the days have to be calculated instead.
Days taken are inclusive start and finish day, so 1 has to be added to the day difference.
So from 23/3/2012 to 30/6/2012 are 100 days.
See array formula (Enter with Ctrl+Shift+Enter) in M6.
=SUM(IF(Table2[Cert Finish Date]>=$J$3,Table2[Cert Finish Date]+1-IF(Table2[Cert Start date]>=$J$3,Table2[Cert Start date],$J$3),0))
EE-TRR-Calculations-vers-03.xlsx
0
 
agwalshAuthor Commented:
Brilliant! And at the risk of driving you completely nuts...:-) how do I build in the functionality of it adjusting for the different names as per the Calculations sheet as I copy it down?
0
 
Ejgil HedegaardCommented:
See formula in H4 on Calculations.
=SUM(IF(Table1[Name]=$A4,1,0)*IF(Table1[Cert Finish Date]>=$D4,Table1[Cert Finish Date]+1-IF(Table1[Cert Start date]>=$D4,Table1[Cert Start date],$D4),0))
EE-TRR-Calculations-vers-03.xlsx
0
 
agwalshAuthor Commented:
ABSOLUTELY MAGNIFICENT :-) And I assume I can just copy the formula to the 12 month column and just tweak for the cells it should be referencing?
0
 
agwalshAuthor Commented:
This has saved me so much time and effort - it's priceless. Can't recommend the help of this expert enough.. THANK YOU.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.