SOLVED

# Calculating Revenue between Dates

Hello All

We have signed some contracts each of which have their own values, start and end dates. I need to be able to automatically calculate the revenue in respect of these dates.

What formulae is available to automatically calculate the below: Many thanks

8 Replies

# Re: Calculating Revenue between Dates

On row 3, you have the contract start on 15th October 2023 and end on 30th June 2024.

You list the first amount in Oct-23, but if that is correct, the duration should be 9 months:

Oct, Nov, Dec. Jan. Feb, Mar, Apr, May, Jun

And the amount should be 200,000/9.

Or do I miss something?

# Re: Calculating Revenue between Dates

@Hans Vogelaar yes you are correct. Although the reason why the table finishes at March 2024 is because the company operates on a fiscal year, April to March.

# Re: Calculating Revenue between Dates

Select E2.

Enter the formula

=IF(AND(\$B2<=EOMONTH(E\$1,0),\$C2>=E\$1),\$A2/\$D2,"")

Fill to the right to P2, then down.

# Re: Calculating Revenue between Dates

@Hans Vogelaar thank you so much this has worked a treat!

I do have another request though...

Where a contract has a value of £100,000 for a duration of 12 months and starts on 15/10/2023, I should only recognise 16 days of revenue i.e. (16/31)*(£100,000/12) = £4,301. Please advise, thanks!

# Re: Calculating Revenue between Dates

But then the sum of the amounts would not be equal to the contract value anymore. Does that matter?

# Re: Calculating Revenue between Dates

Ideally I would like the sum of the amounts to agree to the contract value. However, please show me the solution you have in mind.
best response confirmed by Kapil35 (Copper Contributor)
Solution

# Re: Calculating Revenue between Dates

See the attached workbook. The amount per month depends on the actual number of days that fall within that month.

# Re: Calculating Revenue between Dates

Thank you Hans, this is much appreciated!