Proforma calculation based of month and years

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

Workbook is attached to thread now

Workbook is attached to thread now

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)

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

Formula:

Please Login or Register  to view this content.

Let us know if you have any questions.

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

Try the following:

Formula:

Please Login or Register  to view this content.

Let us know if you have any questions.

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