Expense Sheet - Adding additional expense to first month

Hi there! I am working on an expense worksheet. These are the problems that I need help with:

1) When we receive an invoice, it is entered into our system the same month the invoice was received. Sometimes the expense period covers a period of time that has already passed.

Example: Invoice is received in April 2023, but the Expense period began in October 2022 -- the first month we would expense anything would be in April. The first month of expenses should cover October through April, and then only one month of expenses per month after that until it is zeroed out.


2) If the total expense entered is a negative, the balance will not calculate in column H. At times there is a negative invoice due to a refund or canceled invoice. This problem is rare but if I can solve the formula that would be great!


Thank you in advance for any help! I'm thinking I'll need a more extensive formula for problem #1, possibly utilizing "sumif", etc.

Maybe try this in cell I5 - copy across and down:

=MAX(MIN(I$3,$D5)-MAX(I$2,$C5,$B5)+1,0)*$G5+IF(I$2=MAX($B5,$C5),(I$2-$C5)*$G5)