I have a list of employees that includes their HireDate and DateOfBirth from which I have calculated their Age and YearsOfService on a specified date in the future (8/1/2023). From this data I need to run an analysis to determine how many years it will be before the Age + YearsOfService >= 90 assuming each field increases by 1 year on the yearly anniversary of the specified date.
For example if current YearsOfService = 11 and Age = 30 then current sum is 41. Next year it will be 12 + 31 = 43, the following year will be 13 + 32 = 45, etc. I can figure it out on an employee-by-employee basis on a separate worksheet using SEQUENCE() function for each and summing each row, but there are over 600 employees to go through.
Further, I am trying to categorize the results to identify those employees that will reach the magic sum of 90 within "0 to <1 yr", "1 to <2 yrs", "2 to < 5yrs" and "5+ yrs". I think that should make my analysis easier, just can't figure out how ;(
I am open to any solutions but if you choose to help this info may be useful: I am pretty experienced with PQ (perhaps a GroupBy solution?), somewhat less with VBA if it involves looping and tables together, and feel really good with formulas. I just can't figure out how to easily accomplish what I'm trying to do. Sadly, PBI is not an option with this client and it has to ALL be done in Excel 365.
Any input/advice will be so very much appreciated.
Hi Kathi,
You can work out how many years until a person reaches 90 with this formula
=IF((90-(B3+C3))<0,0,ROUNDUP((90-(B3+C3))/2,0))
and you can then use a lookup table to classify them into groups
=VLOOKUP(D3,$G$3:$H$6,2,TRUE)
I've done this in Excel using formulae but it is as easily done in Power Query.
Regards
Phil
Genius! Thank you so, so much Philip. I was very much over-complicating things. Now that I've seen your solution I feel a little silly. Thanks for taking the time to help me 🙂
No worries Kathi, glad to help.
Regards
Phil
