Proforma calculation based of month and years

[Shruder]

Hello,

I have written an existing formula for prorating sales amounts based on schedule terms. I am only focused on Cells S6:AD6 and S14:AD14 which is where the proforma formula lives. The task is to completely automate the proforma formula where it reads off the dates and amounts in the sheet, and not rely on any manual inputs.

Please Login or Register  to view this content.

In the below image, we can see the proforma formula working, Jan-Apr is prior year 2023 amount of $100,000, then in May 2024 the schedule ends so that month needs to get prorated based on the days, which it is at $161,290 amount. Then in June 2024, we pick up the new amount of $200,000. I want to be able to drag the formulas from S6:AD6 down for the next schedule in S14:AD14.

the issue is in S14:AD14 the formula is picking up the 2023 amount of $150,000 instead of the 2024 amount of $175,000. Since the chedule is from 1/1/24 to 12/30/21 the formula should just pick up the 2024 for Jan-Nov, but it is not. So I am trying to modify this formula to do that.

Any suggestions?

WORKBOOK is attached to thread below

Thank you!


schedule.PNG

[Shruder]

Workbook is attached to thread now

[Shruder]

Workbook is attached to thread now

[FDibbins]

How big is your file? We only need enough data to show what you are working with, and what you expect. Remove all the rest (in other words, make up a SMALL dummy file for us)

[Shruder]

Attached is the workbook. I have enough formulas in there to follow my points made from the beginning of this original thread.

[JeteMc]

Changed row 2 from text to dates starting with 1/1/2022
Formula for row 6:

Please Login or Register  to view this content.

Let us know if you have any questions.

[Shruder]

@ JeteMc,

That formula is good, a little simpler that my original one as well. Thanks!

[JeteMc]

You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.

[Shruder]

@ JeteMc,

I have reopened this thread. There is another variable that I forgot to mention in the threads above.

I need the Expire date in column (K) to be incorporated into the long formula.

In the screenshot below -

in (AV6) I want the 2024 amt value to continue until the end of the contract on 5/12/2025 in (K6). The current formula returns blank after the end date (R6) 5/12/2024 - however that $200,000 amount in (P6) should still run until the end of they year 12/31/2024 - since the schedule does not expire until 5/12/2025.

So if the end date and expire date are the same then the formula should return nothing. But if the end date is less than the expire date, we would want that amount from (P6) to continue to the end of the year.

Attachment 836461

[JeteMc]

Try the following:

Please Login or Register  to view this content.

Let us know if you have any questions.

[Shruder]

@ JeteMC,

That formula worked, thanks!

[JeteMc]

You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.