Forum Discussion
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 spend training new nurses. They receive $1.25 per hour extra that they can use to be reimbursed for certain purchases.
The organization recently added a rule that nurses have to use their funds within 2 years, or those hours will expire.
This is supposed to be on a monthly basis. So, for example, hours recorded on January 2021 will expire February 1, 2023. Hours recorded February 2021 will expire March 1, 2023 and so on moving forward. This staggering is to ensure that nurses have the full two years to spend their funds.
We need to find a way to get the spreadsheet to calculate this expiration. However, we also need it to take into account if the Nurse already spent the hours.
In the attached example spreadsheet, the Nurse put in hours on March 2020, so these hours would normally expire April 1, 2022. However, they spent money in 2021, so we need the expiration calculation to leave the hours alone that total the amount spent.
We don’t want our nurses to lose hours incorrectly.
I figured out how to get the hours to subtract using the formula below:
January Expiration:
=MAX(0,D4+(E2*1.25)-E3-Sheet1!D4)
February Expiration:
=MAX(0, E4+(F2*1.25)-F3-Sheet1!E4)
Etc. for each month moving forward.
However, I have not been able to figure out how to get it to leave the balance alone if the nurse already spent funds.
I have dabbled in VBA, so if there is a way to do this via that method rather than formulas, I would be open to that as well.
The goal is to get the Expiration to calculate within the spreadsheet so that we do not have to do this manually for over a thousand records. Human error is bound to be an issue if we have to calculate this manually.
Your insight and experience are greatly appreciated.
Example Spreadsheet:
https://1drv.ms/x/s!ArTANjj2NBKytQF3qHpbmq4uzf3g?e=iwbBIR
3 Replies
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.
- 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!!!!- In 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.
