Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2016-09-17
17
Medium Priority
?
60 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 8
17 Comments
 
LVL 23

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 23

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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

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 23

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 23

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
 
LVL 23

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 23

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 23

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 23

Accepted Solution

by:
Ejgil Hedegaard earned 2000 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
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 how to use a scrolling table in Microsoft Excel using the INDEX function.

722 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