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!