Forum Discussion
BiblioManiac
Jan 10, 2022Copper Contributor
Formula for Expiration Tracking
Hello All, I am on an HP PC, Windows 10, using Excel 2016. My organization has a spreadsheet that is used to track an incentive program. Nurses in our organization can record the hours they s...
Riny_van_Eekelen
Jan 10, 2022Platinum Contributor
BiblioManiac Assuming I understood your intentions correctly, I would suggest an entirely different set-up, as demonstrated in the attached schedule. It's more compact and easier to maintain.
Note that I used structured tables. These have an advantage that they expand automatically (including formulae used) when you add new rows to them.
See if this meets your requirements. If not, let me know.
BiblioManiac
Jan 11, 2022Copper Contributor
Hello Riny_van_Eekelen,
I do like the way this looks.
However, we have records for over 1,000 nurses currently participating in the program. I'm not sure how this structure would translate for that many individual records.
This does give me some new ideas though.
Thank you!!!!
I do like the way this looks.
However, we have records for over 1,000 nurses currently participating in the program. I'm not sure how this structure would translate for that many individual records.
This does give me some new ideas though.
Thank you!!!!
- mtarlerJan 12, 2022Silver ContributorIn your linked spreadsheet I added a row I called Expired and it calculates how much of the previously earned PIP expired that month (and I updated the cumulative to account for that). The calculation used EDATE(R$1,-12) for 1 year instead of -24 for the 2 years you want just so we could actually see that it works. I hope that helps.