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

S Kirkland

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

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

Thank you for any help you can provide!

Carson Penticuff

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.

S Kirkland

@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