Forum Discussion
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 i...
See the attached workbook. The amount per month depends on the actual number of days that fall within that month.
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.
HansVogelaar 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!
But then the sum of the amounts would not be equal to the contract value anymore. Does that matter?
- 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.
See the attached workbook. The amount per month depends on the actual number of days that fall within that month.