Calculating Monthly Revenue

I would like to sum monthly revenue estimates of projects on our schedule sheets.

We have several projects that straddle different months, this IS where it gets tricky.

Is it possible to build a formula that looks at a project and if the Start/End date straddles a month, it then calculates how many working days in each month, then divides the sale amount accordingly.

Project A 4/28 to 5/5 $1000 (2 working days in april, 2 weekend days, 3 working days in may)

Project B 4/20 to 4/22 $1000

Project C 5/10 to 5/12 $1000

April Revenue = $1400

May Revenue = $1600

I am familiar with collect and sumif, but I'm guessing this formula would need much more than that.

Any help would be greatly appreciated!


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!