Hello.
I am learning how to use coda for my use in comercial property management.
On a table I have all building areas (floors and units) and on a separate table clients and contracts.
I would like to have the information of how many days a space has been available. It can be 2 different columns, one for current available spaces and just substracting todays date with the end of last contract.
A second column can be the sum of all available dates of that space.
I am attaching a hypothetical example of a floor that has been leased for 3 different tenants. My question is how do you recommend to subtract starting date from current or last contract less the ending date of the previous one?
Floor 8 is the example and I am attaching an excel file to get the result I need.
I have created the document below in the hopes of answering your question.
Days from previous row
In an ideal world you could just refer to the row above however because this is more like a database than a spreadsheet you need to treat it differently.
Filters it to only rows where the Area matches the area on this row (8 in our example) and where the End Date of the contract is less than the start date of this row’s contract
It then selects only the End Date column (we don’t need the entire table)
Sorts the results in ascending order
Selects the Last Value
Logically this should return the end date of the contract that completed before this one.
I have also gone ahead and created other columns with similar functionality.
Duration (Days) is how long the contract lasts
Total Days Under Management is how long you have had the property available to rent for
Days Available Since Last Contract is how long the area has been available since the last contract ended
Total Days Unoccupied (lifetime) is the sum total of days where an area was under contract minus how many days you’ve had it under management.
These can be a good metric to see that while an area has been out of contract for a short time (10 days for instance), during its lifetime it has only been occupied 50 days out of 400. You could use this to make percentages and start tracking your best and worst performing. Use the Days Available Since Last Contract values to create averages of how long areas go unoccupied.
It should be noted that date minus date does not work how you work expect. For example a contract starting on Monday and Ending on Friday. If you wanted to calculate the duration of the contract End-Start it returns 4 days. This is due to how dates are calculated and works the same on excel. You will need to manually adjust as needed if you want it to show 5 days.
I hope this is what you were looking for an can point you in the right direction.
You did an amazing job understanding the requirement and doing the formulas to resolve it.
I did the formulas that you describe on the second part for the areas table and they worked perfect.
Like magic!
It’s going to be very useful.
Sorry I took some days to review it but I had a very busy week.
