Spreading contract value by Calendar Year based on Period of Performance start and end dates

Options

Good evening,

I am working on transitioning data from Excel to Smartsheets and am having trouble figuring out the Smartsheet equivalent to a formula I have used for years in Excel to spread contract value/revenue by calendar year as follows:

=MAX(MIN(DatesforCalcs!C\$1,[@End])-MAX(DatesforCalcs!B\$1,[@Start]),0)/(DATEDIF([@Start],[@End],"d"))*[@Value]

So for the CY 2023 column where DatesforCalcsC\$1 = 12/31/23 and DatesforCalcs!B\$1 = 12/31/22 this formula in Excel provides me a high level revenue estimate for 2023 based on the number of days the contract is active in CY 2023 as a percentage of the total number of days for the contract period of performance.

Here is what I have so far in Smarsheet but it is still giving me #Unparseable

Tags:

• ✭✭✭✭✭✭
Options

I haven't really dug into it yet, but on first glance, you have spaces in the [Start Date] and [End Date] columns, but they are omitted in the formula.

• Options

@Carson Penticuff that I was part of it.

I think I figured out the full solution:

=MAX(NETDAYS(MAX(DATE(2022, 12, 31), StartDate@row), MIN(DATE(2023, 12, 31), EndDate@row)), 0) / NETDAYS(StartDate@row, EndDate@row) * Value@row

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!