Hey besties! Today I want to share with you a super handy custom formula that you can use to calculate revenue based on start and end dates in Google Sheets. This formula will make your life so much easier when dealing with date-based calculations. Are you ready? Let’s dive in!
The Challenge
Imagine you have a rental property with multiple rooms, and you want to calculate the total income from these rooms for a specific period. For example, let’s say you want to generate a report from January 1, 2015, to June 30, 2016. However, you also need to consider the start and end dates of each room’s rental contract. It’s not as simple as just summing up the rental amounts.
The Solution
To solve this challenge, I’ve come up with a logical formula that takes into account the start and end dates of the rental contracts and calculates the revenue for the specified period. Let me explain how it works.
In the formula, we use nested IF statements to check various date-related conditions. Here’s an overview of what the formula considers based on the report start and end dates:
- If the contract start date is before the report start date, the formula calculates the revenue from the report start date to the contract end date.
- If the contract start date is after the report start date, the formula calculates the revenue from the contract start date to the contract end date.
- If the contract end date is empty, the formula considers the current date as the contract end date and calculates the revenue accordingly.
The formula uses the monthly rental amounts provided in the sheet and calculates the per-day rental. It then uses this information to determine the total rental for the specified start and end date period.
The Formula
In cell G2, apply the following formula and then copy and paste it to the adjoining cells down:
`=round(iferror(IF(and(E2<MASTER!$E$13,MASTER!$E$13<>””,F2<>””,F2>MASTER!$E$14),((C2+D2)12/365(datedif(MASTER!$E$13,MASTER!$E$14,”D”)+1)), IF(and(E2<MASTER!$E$13,MASTER!$E$13<>””,F2<>””,E2<>””,F2<=MASTER!$E$14),((C2+D2)12/365(datedif(MASTER!$E$13,F2,”D”)+1)), IF(and(E2>=MASTER!$E$13,MASTER!$E$13<>””,F2<>””,E2<>””,F2>MASTER!$E$14),((C2+D2)12/365(datedif(E2,MASTER!$E$14,”D”)+1)), IF(and(E2>=MASTER!$E$13,MASTER!$E$13<>””,F2<>””,E2<>””,F2<=MASTER!$E$14),((C2+D2)12/365(datedif(E2,F2,”D”)+1)), IF(and(E2>=MASTER!$E$13,MASTER!$E$13<>””,F2=””,E2<>””,today()<=MASTER!$E$14),((C2+D2)12/365(datedif(E2,today(),”D”)+1)), IF(and(E2>=MASTER!$E$13,MASTER!$E$13<>””,F2=””,E2<>””,today()>MASTER!$E$14),((C2+D2)12/365(datedif(E2,MASTER!$E$14,”D”)+1)), IF(and(E2<MASTER!$E$13,MASTER!$E$13<>””,F2=””,E2<>””,today()<=MASTER!$E$14),((C2+D2)12/365(datedif(MASTER!$E$13,today(),”D”)+1)), IF(and(E2<MASTER!$E$13,MASTER!$E$13<>””,F2=””,E2<>””,today()>MASTER!$E$14),((C2+D2)12/365(datedif(MASTER!$E$13,MASTER!$E$14,”D”)+1)),0)))))))),”0″),0)
Example and Spreadsheet
To help you understand better, I’ve created a sample spreadsheet where you can see this custom formula in action. Just make a copy of the Sample Sheet and explore how it works. If you find this formula useful, don’t forget to drop a comment!
That’s it, my dear friends! Now you have a powerful custom formula to calculate revenue based on start and end dates in Google Sheets. No more manual calculations or complicated functions, just a simple formula that does the job for you. Happy spreadsheeting!
P.S. Don’t forget to check out Crawlan.com for more amazing content and tips on Google Sheets.
