How to roll up data over date ranges?
I have data in multiple sheets where each row has a date range along with some amount, say a cost for a task. I'd like to create some kind of report where I can estimate costs for each month based on all these tasks. I can assume that the cost is spread evenly over the date range.
So for example if I have a row like this:
Start, End, Cost
2022-01-01, 2022-03-31, $750
Then the output I want would have $250 for January, $250 for February, and $250 for March. Of course I want to sum up multiple rows with different date ranges and costs.
This functionality seems similar to what Smartsheet already does for the Resource Views for resourcing of staff members... it creates a summary of how loaded each person is over time based on multiple tasks.
Does anyone know if there is a way to do this somehow in a Report or other Smartsheet functionality? I can download the data via the API and calculate it myself but if there was a built-in way of doing it obviously that would be easier. Thanks for any advice!