Spreading contract value by Calendar Year based on Period of Performance start and end dates
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!
Answers
-
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.
-
@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
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 136 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 485 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!