formula: rolling cost projections using Start and End dates

Michelle Willyard

We are trying to put together a formula, for rolling cost projections using Start and End dates, but if Start Date is earlier than "Today's Date", I need the formula to split up a value of the backlog equally by QTR from TODAY's DATE to End Date. We have the TODAY date working, I just need to split out a value from remaining duration of a project by QTR equally. Do I need to use a Metric sheet, to tie the information in, or is there a formula that I can use?

Thank you!

Sean Morgan

Hello @Michelle Willyard ,

I feel that I may be misinterpreting the requirements for your Sheet, but here is what I assume based on the Description and Screenshot:

1) The Duration Column is calculated in Months between the "Date" and "End Date" as there are 6months and 4 weeks between both dates listed on the current Sheet

2) If the Start date is before "Todays Date", then split the value from the Backlog cell, into each of the Qtr Columns/Cells

If possible, please can you describe the use of the "Backlog by Month", and how this is calculated, and if this should be used within the Formula.

You may wish to use a modified version of the following Formula for Mathematical Calculations E.G =IF(Date@row < TODAY(), Backlog@row / 4. This would give you a value for each quarter. If you'd like to have it divide by a different number, you can modify the number after the forward slash / divide symbol.

Please let me know if I have misunderstood your ask.

Regards

Sean

Sean Morgan

Hello @Michelle Willyard ,

I feel that I may be misinterpreting the requirements for your Sheet, but here is what I assume based on the Description and Screenshot:

1) The Duration Column is calculated in Months between the "Date" and "End Date" as there are 6months and 4 weeks between both dates listed on the current Sheet

2) If the Start date is before "Todays Date", then split the value from the Backlog cell, into each of the Qtr Columns/Cells

If possible, please can you describe the use of the "Backlog by Month", and how this is calculated, and if this should be used within the Formula.

You may wish to use a modified version of the following Formula for Mathematical Calculations E.G =IF(Date@row < TODAY(), Backlog@row / 4. This would give you a value for each quarter. If you'd like to have it divide by a different number, you can modify the number after the forward slash / divide symbol.

Please let me know if I have misunderstood your ask.

Regards

Sean

Michelle Willyard

Thank you for your repsone Sena. This is the formual we're using, =IF(AND([Todays Date]@row <= {Quarter 2 2020 Start_Range}, [End Date]@row >= {Quarter 2 2020 End_Range}), Backlog@row / [Quarter count]@row, " "), as I want the backlog amount to be split by the QTR that the work will actually occur in, ergo, if the start date is TODAY, and the End date is April of 2021, I want the backlog number to split between Q4 of 2020, and Q1, & Q2 of 2021. The Backlog by Month calculation was a "test", I wanted to see if I split the entire backlog amount monthly, than I could multiply that number by the appropriate months in each Q accordingly, but after creating the formula above, that column could be deleted.

I'm thinking I will have to put actual dates in each Qtr, in different columns or even a seperate sheet. and then have the formula, <= the beginning of that Q and the End date of that qtr, and then calculate the amount that would fall in each Qtr.

Does that make sense?

Thank you,

Michelle