Calculating Monthly Revenue

Luke KBP

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!

Paul Newcome

Is it possible that it could overlap by more than one month where (for example) it starts in April and ends in June (or some later month)?

Luke KBP

That is great question, lol.

Yes, we have a handful of jobs that overlap the span of an entire month.

Paul Newcome

I have a bit of a busy day today, so I will at least get you started. When I get a chance to come back to this I will get more in detail with the specific formulas.

First we are going to need to create additional columns for however many months a project can overlap.

In all of the columns we are going to calculate the number of working days total by using a NETWORKDAYS function and divide the total dollar amount by this number.

The first month column is going to have a formula that calculates the last day of the month (first of next month minus 1 day) and subtracts from that the start date. This will give us how many days in the first month.

The middle column(s) will use a DAY function to tell us how many days are in that month total (based on first of next month minus one).

The last column will use a basic DAY function as a standalone based on the finish date to tell us how many days were in the final month.

As I said... I will have to get back to you with the details after a bit, but there is the overall structure we will need and logic we will use.

Paul Newcome

I do need to ask before getting too far into the middle formula(s)... Is it possible to span more than 3 months such as 28 June to 19 September?

First month formula:

=NETWORKDAYS([Start Date]@row, IFERROR(DATE(YEAR([Start Date]@row), MONTH([Start Date]@row) + 1, 1), DATE(YEAR([Start Date]@row) + 1, 1, 1)) - 1) * ([Revenue Column Name]@row / NETWORKDAYS([Start Date]@row, [End Date]@row))

Last month formula:

=NETWORKDAYS(DATE(YEAR([End Date]@row), MONTH([End Date]@row), 1), [End Date]@row) * ([Revenue Column Name]@row / NETWORKDAYS([Start Date]@row, [End Date]@row))

Luke KBP

WOOOOHOAA!

This looks awesome, thanks man!

Yes we do have projects that can technically go multiple months (new construction paint contracting), not to keeping adding complexity.

In this example are we using columns as variable storage?

I'm guessing we would need to keep adding columns for additional months?

Paul Newcome

The extra columns are for variable storage. Yes.

We do need additional columns for more months. Basically you will need a column for every month. Start month, End month, and every month in between UNLESS... You just wanted to assume 20 working days in every month (5 days/week and 4 weeks/month). If you can go that route then the overall solution gets super simple.