Solved

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

Posted on 2016-09-17
17
42 Views
Last Modified: 2016-10-28
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
0
Comment
Question by:agwalsh
  • 8
  • 8
17 Comments
 
LVL 21

Expert Comment

by:Ejgil Hedegaard
ID: 41803210
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
 

Author Comment

by:agwalsh
ID: 41804502
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
 
LVL 21

Expert Comment

by:Ejgil Hedegaard
ID: 41805529
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
 

Author Comment

by:agwalsh
ID: 41806303
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
 
LVL 21

Expert Comment

by:Ejgil Hedegaard
ID: 41806636
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
 

Author Comment

by:agwalsh
ID: 41806744
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
 
LVL 21

Expert Comment

by:Ejgil Hedegaard
ID: 41806784
Gets a cert = Finish date.
Then the rolling periods must be calculated from the Latest Finish Date.
EE-TRR-Calculations-vers-02.xlsx
0
 

Author Comment

by:agwalsh
ID: 41806891
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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 21

Expert Comment

by:Ejgil Hedegaard
ID: 41807209
Then this how it must be.
EE-TRR-Calculations-vers-02.xlsx
0
 

Author Comment

by:agwalsh
ID: 41815776
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
 
LVL 21

Expert Comment

by:Ejgil Hedegaard
ID: 41816375
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
 

Author Comment

by:agwalsh
ID: 41816433
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
 
LVL 21

Expert Comment

by:Ejgil Hedegaard
ID: 41816793
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
 

Author Comment

by:agwalsh
ID: 41819493
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
 
LVL 21

Accepted Solution

by:
Ejgil Hedegaard earned 500 total points
ID: 41820131
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
 

Author Closing Comment

by:agwalsh
ID: 41863505
This has saved me so much time and effort - it's priceless. Can't recommend the help of this expert enough.. THANK YOU.
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now