Finding the oldest record with a non zero value and then reducing it by user input

[Anticor]

All,

I'm attempting to find solve the following:

Each month a user accrues a certain number of hours, they expire after X months, what I'm trying to do is when the user attempts to use the some of their accrued hours it reduces from the oldest "bucket" to ensure they aren't wasting hours.

I've already sorted out zeroing out the available hours once the expiry criteria is met, as well as populating the accrual etc. Where' I'm struggling is actual utilization of available hours:

I've got a form that opens where the user enters the number of hours they'd like to use and what I want to to do is find the oldest non zero bucket of hours and reduce it.

Thus far I've got my glorious comments into my developer and I'm I'm not sure how to the date eval AND the bucket eval.

'Find the oldest bucket of hours and reduce it by the requested value
    'If the oldest bucket doesn't have enough hours reduce it to zero
    'Find the next old oldest bucket and reduce it by the remainder
    'Repeat until the requested hours amount has been satisfied

So taking the above let's say the user has 11 hours available dispersed like this:

The user launches the form and requests 3 hours, the expected outcome would be that the Feb-22 available hours reduces to 0 AND the Mar-22 available hours reduces to 2.

In a perfect world I'd write the above transaction to another sheet for auditing later, but that's a fight for a different day.

 

[Joe4]

What you are describing sounds like the old "FIFO" methodology (First In/First Out).
If you do a Google search on these terms (i.e. "Excel FIFO"), you will find many links of various solutions that people have come up with.
Here is one to get you started, but there are many others: FIFO Calculator — Excel Dashboards VBA

 

[Anticor]

Joe,

Thank you. I looked at FIFO but most solution I was seeing were trying to deal with COGS valuation as it related to the inventory pull BUT....what I need might be nested in there I'll give it a gander.

As I think about it, if I make an assumption (a dirty and dangerous word) that the very first time this is built and populated it will require the first record to start as positive, or non 0/null value, when this executes I could ignore the need for oldest and just try and eval the first non zero with a do loop.