Formula for Dollar Amount Expiration Tracking Based on 2 Year Date Range

[BiblioManiac]

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. We have over 1,000 nurses participating whose balances need to be tracked.

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.

The spreadsheet is currently separated into two sheets. Sheet 1 records the 2020-2021 records, Sheet 2 is for the 2022-2023 range. In theory when hours on Sheet 1 hit the 2 year expiration, the dollar amount recorded per month in the 2020-2021 sheet would be subtracted from the 2022-2023 sheet, but only if those points have not already been used.

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!

[ranman256]

you really should start using a database instead of a spreadsheet for this.
Microsoft Access is build just for this, to query data over time.
Don't use a screwdriver to hammer in nails.

[BiblioManiac]

I am aware of this.

My organization is notorious for using Excel for things that would work better in other programs.

However, this is the tracking system I was handed when I took this position and I am being expected to make it work as is, at least for the time being.

Any suggestions for how to get this working in Excel would be greatly appreciated.

[JeteMc]

Hello BiblioManiac and Welcome to Excel Forum.
If you are allowed to put this on one sheet, I feel that would make things easier.
Note that since an Excel spreadsheet has 16,384 columns it could accommodate 1,365 years' worth of monthly data on one sheet.
This proposal adds a row (5) which is used to track cumulative unredeemed and unexpired hours. Therefore, the formula should start with February 2022.
In the attached file I put the following in cell AC5 and copied to the right:

Please Login or Register  to view this content.

Let us know if you have any questions.

[JeteMc]

Looking at this again, I don't think that an extra row is needed.
In the file attached to post #4, begin populating at AC4 and drag to the right using: =(SUM($D2:AC2)-SUM($D3:AC3)/1.25-IF(SUM($D3:AC3)/1.25<SUM($D2:D2),SUM($D2:D2)-SUM($D3:AC3)/1.25,0))*1.25
Let us know if you have any questions.