How to process surplus amounts in values

I am working with schedule data at my facility that is used to make various projections.

Our scheduling software organizes production runs by run and not by day which leads to some challenges. For example:

If we have a 60hour run of one product that begins at 7/17/23 00:00, then in the report, 7/17/23 will read 60 hours, 7/18/23 will read 0 hours, and 7/19/23 will read 12 hours of whatever product was made next.

This creates challenges when attempting to provide accurate projections. I need 7/17 to read 24, and then the overflow needs to be distributed among subsequent days until it's used up (7/18 should read 24 and 7/19 should read 12 hours of the product and then 12 hours of whatever followed).

For simplicty we can use the data set below.

Thanks,
Parker

Please try

Formula:

Please Login or Register  to view this content.

@Parker,

The day before yesterday I posted a solution.
Since then you have been active on this forum several times, but I have not yet seen a response from you.
I find that annoying, because I have spent more than an hour of my time to help you further.

Then I at least expect you to take note of that.

Thank you for letting me know. .

I gladly spent my time on it.
It was an interesting problem to solve.

I'm curious about your findings.
I'll wait until you've had time to look at how the formula works.

You may have more time left after the implementation of the formula.

The formula first calculates per run, based on the start date and time (b) and the runtime, the expected end date and time (e), and then over which days the run should be spread (s).

The runtime per day is then determined on the basis of this data.
This is a maximum of 24 hours, but on the first day and last day, the start time and end time are taken into account and then it is usually less.

The number of hours per day is multiplied by the corresponding factor.

After this has been determined for all runs, everything is summed and displayed per day.

You can find out exactly how all functions in the formula work via "Evaluate formula". Navigate to a formula you want to evaluate and click Formulas --> Evaluate Formula --> Evaluate

You are Welcome!

Thanks for your feedback, Glad to have helped.

If you haven't already, please consider adding reputation to all helpers you think they deserve.
In that case please click * Add Reputation left below their answers.

Do you know why my final array would continue down until it reached 2/21/2024

I don't see your final array,
but maybe because of a runtime that reaches that far?

Thanks for the feedback and rep

Please drop here your specific case (without confidential info) with expected results and I (or an other member) will try.
I like to solve this kind of cases. .