Excel: whether you love it or loathe it, you can’t deny its usefulness. It’s simple to use, easy to modify data, very customisable, not hard to manipulate data to create charts, graphs and other analysis, you can work with others on it at the same time…you get the drift. So it’s not surprising that a significant portion of rent roll data across the globe is still exchanged in Excel. You may be one of those who regularly uses Excel rent rolls. We’re big fans of the format, which is just as well as we deal with thousands of Excel rent rolls or tenancy schedules in this format.
As a provider of rent roll automation software, we’ve also noticed Excel’s quirkiness, though, and thought we’d share one or two (okay, maybe more) in this series of blogs, starting with date formats.
1. Incorrect century in Excel date format: the 2029 rule
Yes, century. If you use the date format DD/MM/YY and type in any date after 2029, Excel will treat it as a date during the 1900s.
It’s because Excel stores dates as sequential serial numbers. So it treats 1 January 1900 as the first day of its time scale and all subsequent dates are calculated as a number of days after that date, which means that Excel’s maximum date is 31 December 9999.
However, when using a two-digit year format, Excel assumes that any year entered between 00 and 29 is in the 2000s and any year entered between 30 and 99 is in the 1900s. For example, 01/05/35 will be treated as if it’s 1 May 1935 and not 1 May 2035.
1.1 Lease expiry dates in Excel rent rolls
If you have a lease expiry date that’s many decades in the past, it’s inevitably going to cause confusion – especially if you want to calculate KPIs such as weighted average lease term (WALT) because it’ll result in a negative figure. Incorrect lease expiry dates can become an even bigger issue in larger rent rolls as they’re not easy errors to spot.
This wasn’t a problem 10 years ago because 2030+ wasn’t an expiry date for many leases. But it’s surfacing as a problem now because – as we get closer to 2030 – the chances of more tenancy schedules being affected increases due to a greater number of leases expiring after 2030.
It’s possible that Microsoft may update their software to rectify this deviance. But, if you are signing longer-term leases that will end after 2030 and you’re manually collecting, inputting and processing your rent rolls, you should, nevertheless, look out for this anomaly in your rent roll data.
1.2 How to manage the 1900 dates in Excel
To avoid the last-century problem, the simplest option is to use a four-digit year format (e.g. DD/MM/YYYY) instead of a two-digit year format (YY) for all your rent roll data. Excel will then interpret the year correctly and it should prevent any date-related errors.
Applying a MIN formula to all of the dates can quickly check for any unexpected anomalies. Click in a cell with a date, then click AUTOSUM dropdown, select MIN, choose cell range (if necessary) and hit Enter.
2. Dates as text values in Excel
There’s another niggle with dates – worth noting if you import data from csv files/spreadsheets. If the dates have been stored as text values instead of real date formats in your source file, Excel won’t recognise them as dates and it won’t apply any date formatting. (You know they’re text values if they’re aligned left in the cell.)
Not a big problem, right? Actually, it could be. If you’re an asset manager working with commercial real estate rent roll data, you’ll know how important start or end dates in leases or rental agreements are. But in this context, the text values are only a problem if you rely on dates as part of your analysis. (Which most asset/investment managers do, don’t they?) They won’t work, but Excel does kindly flag them as an error so they don’t go unchecked.
2.1 How to fix text values in Excel rent rolls
Sometimes, Excel will automatically convert text dates to Excel numeric dates upon pasting, but make sure that the DD/MM versus MM/DD of the copied dates matches what your computer is expecting.
If you still have text dates, you can use the DATEVALUE (date_text) formula to convert them to Excel dates. So, for example, you’d use =DATEVALUE (A1) for a text date in the cell A1.
You can also tell if you have text dates if they still look like dates after changing the Number Format to Date from the Home tab.
3. Incorrect date sequences in spreadsheets
Dates in the wrong order can also be a common problem – especially when you have old rental records that haven’t been updated for some time or they’ve been updated with incorrect dates. One example of this is if a break date is entered after the lease expiry date, which could affect the timing of lease renewals or the ability to identify delinquent tenants, for instance.
Other examples of date sequencing errors include missing or duplicate dates, which can cause confusion and inaccuracies in rent roll data.
To catch errors like these so they don’t affect your analyses, you’ll need a robust process for data quality in place to check that all lease-related information is accurate and up to date.
4. Non-existent dates
Another problem we see sometimes in rent rolls is dates that don’t exist, such as 31/06/YYYY, or leap-year dates (29/02/YYYY). These sorts of dates generate errors in rent roll analyses and, while Excel will flag them, it can be hard to find the root cause of them. It may seem like a small issue, but it can lead to hours of confusion and errors in calculations.
The way to get round this is to use the DATEVALUE formula as it’ll throw an error if the text contains a non-date.
5. But why do these errors with dates occur in Excel rent rolls in the first place?
- Excel is very easy to use and it’s highly flexible. Its scope is incredibly wide, which means it’s used for so many things that aren’t related to the real estate industry. (So, for example, some users use Excel for date-tracking purposes that make dates in the 1900s relevant.) However, Excel’s greatest asset is also its greatest liability because it means that anyone can use it (a blessing), but there’s also a greater chance of introducing errors because it’s so easy to make changes (a curse). Its flexibility and ease make it harder to keep a grip on data quality.
- There’s no set standard in the industry for how rent rolls should be presented or exchanged and date formats are just one example of that. Every manager in the commercial real estate process uses different formats and standards to collect, collate and process their rent roll data (see illustration below). So every operator then has to manually adjust the data to fit in with their in-house format and process.
- Human error. Given Excel’s ease of use, the lack of data standards, the number of operators and variety of formats in use across the commercial real estate industry, it’s inevitable that human error will feature somewhere in the data collection, entry and processing of rent rolls. Even the single task of standardising data across formats is highly repetitive, which makes it very easy to lose track of your place in a spreadsheet, become distracted or – dare we say it – just bored.
Read about bad data in how PRODA is building trust in data.
6. Excel date-format problems: a solution
So what’s the answer to these date-format quirks and the possible errors they can lead to? Automation. It can help the process exponentially. It standardises data automatically across formats – removing the repetition and likelihood of human error – while also saving enormous amounts of time.
If you use purpose-built automation software, like PRODA, it tracks changes over time, allows for collaboration and a secure exchange of data, and gives you outputs that you and your partners can use in different ways on different systems across your business operations.
Automation is data standardisation without imposing data standards so you use your data in any way you like – now and in the future – without worrying about system loyalty.
And PRODA does all of this already. If you want to know more about reducing the risk of date-related errors in your commercial rent rolls, talk to us today.
