How to roll up data over date ranges?

ScottWebster

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!

HeatherD.

Hi @ScottWebster !

It may be best to use some helper columns to calculate the cost for each month for each task. In my test, I created a column for each month of the year. Then I entered the following formula in the January column:

=IF(AND(MONTH(Start@row) <= 1, MONTH(End@row) >= 1), Cost@row / (MONTH(End@row) - MONTH(Start@row) + 1), "")

For the February column, I changed the bolded numbers to 2, March to 3, December to 12, etc.

The formula basically translates to "If the start date is during or before (month specified in the column's formula - in the sample above, January) AND the end date is during or before (month specified in the column's formula), divide the cost of the task by the number of months the task spans; otherwise, blank." The number of months the task spans takes the month number of the end date, subtracts the month number of the start date, and adds 1. So, if the start is in March and the end is in June, the value would be 4 (6 minus 3 plus 1) - the task spans 4 months.

You could then use a report to sum each month column, or you could use a formula to summarize the data (as displayed in the below screenshot).

The formulas may need to be tweaked a bit if you have date ranges that cross over from the end of one year to the beginning of the next, but this should be a good starting point for you.

Hope this helps!

Best,

Heather

ScottWebster

Hi Heather, thanks for the response. Your approach looks like it could work. As you mention, I'll need to think a bit about the different years thing. Ideally the calculations would be for some future period, like the next 12 months, rather than a specified month like "July 2022". But that might be possible by determining the month numbers based on an offset from the current date.

One more question if I may... do you know if there is any way to do these sort of calculations "in a report" that combines rows from multiple sheets, or would I have to include the extra columns for each month in every source sheet that contains this data?