Hi
We run a property rental company and have several lines with date lines on them. Based on the date line we get the rented days. I would like to be able to see a turnover within a specific period. This should consider the start date can be mid of month sometimes so we would need the formula to count days till end of the month. And then same on another column. This way we can see turnover month for month. Backwards and forwards.
Or is there any other way to calculate this?
Hi Sam! Welcome to the monday community!
This is definitely something my team here at CarbonWeb can help with in monday.com.
To achieve the turnover calculation you’re looking for, we can set up a combination of formulas and columns that will break down the rental periods by month, even when the start or end dates fall mid-month. Here’s how we can approach it:
Start and End Dates: We can create two date columns for the start and end dates of the rental period.
Days in Month Calculation: Using a formula column, we can calculate the number of days the property is rented within a given month. If the rental starts mid-month, the formula will calculate from the start date to the end of the month. Similarly, for periods that carry into the following month, it will calculate from the start of the month until the end of the rental period.
Turnover per Month: By applying a formula or automation, we can calculate the turnover (e.g., rental income) per month, even when rentals span multiple months. We could then set up additional formula columns to aggregate the turnover for each month and even forecast forward or review historical data.
If you’d prefer, we can also explore creating a custom dashboard that displays these turnovers for any period you wish to analyze—whether backward or forward.
Let me know if you’d like help setting this up - Here is my Calendly if you’d like to chat
Best regards,
Yes, you can calculate the number of rented days within a specific period by determining the overlap between each rental period and the period of interest (e.g., each month). This method accounts for rental agreements that start or end mid-month.
Here’s how you can do it in Excel:
Set Up Your Data:Rental Start Date: Enter the start date of each rental period in column A (e.g., cell A2).
Rental End Date: Enter the end date of each rental period in column B (e.g., cell B2).
Period Start Date: In cell C1, enter the start date of the period you’re analyzing (e.g., the first day of the month).
Period End Date: In cell D1, enter the end date of the period (e.g., the last day of the month).
Calculate the Overlapping Days:Use the following formula in cell C2 to calculate the number of days the rental overlaps with the period:
=MAX(0, MIN($B2, D$1) - MAX($A2, C$1) + 1)
Explanation:
MAX($A2, C$1): Determines the later of the rental start date and the period start date.
MIN($B2, D$1): Determines the earlier of the rental end date and the period end date.
Subtracting these two gives the overlap duration.
Adding 1 accounts for inclusive dates (if needed).
MAX(0, …) ensures that negative values (which indicate no overlap) are set to zero.
Copy the Formula:Drag the formula down for all rental entries to calculate the overlapping days for each rental period.
Calculate Turnover:If you have a daily rental rate in column E (e.g., cell E2), you can calculate the turnover for the period in cell F2:
=C2 * E2
Copy this formula down for all entries.
Sum the turnover column to get total turnover for the period.
Repeat for Multiple Periods:If you want to analyze multiple months, you can set up additional columns for each period.
For example, use columns E and F for the next month’s start and end dates, and adjust the formula accordingly.
Alternative Method:
Pivot Tables and Data Modeling:
If you have a large dataset, consider using a pivot table or data modeling tools like Power Query to reshape your data.
Create a calendar table that lists all dates within your analysis range.
Expand each rental period into individual dates and link them to the calendar table.
This allows for more advanced analysis and reporting capabilities.
Benefits of This Method:
Accuracy: Precisely calculates the number of active rental days within any given period.
Flexibility: Easily adjust for different periods (months, weeks) by changing the period start and end dates.
Scalability: Can handle multiple rental agreements and periods by extending the formulas.
Example:
Assuming:
Rental Start Date (A2): January 15, 2024
Rental End Date (B2): March 10, 2024
Period Start Date (C1): February 1, 2024
Period End Date (D1): February 28, 2024
Formula in C2:
=MAX(0, MIN($B2, D$1) - MAX($A2, C$1) + 1) =MAX(0, MIN(March 10, February 28) - MAX(January 15, February 1) + 1) =MAX(0, February 28 - February 1 + 1) =28 days