For one reason or another, there is a perfectly functioning and pretty valuable formula that can be utilized in Excel that Microsoft has chosen to exclude from Excel’s list of functions: the DateDif function. This function is a valuable tool that can instantly calculate the number of days, months, or years between two dates.
While in Excel, if you click on the ‘fx’ button to the left of the formula ribbon and search for this function in the ‘Insert Function’ box, you will see that it is nowhere to be found. However, you can manually type the DateDif formula into a cell and by following the instructions in this post, get the results described.
The DateDif formula can be a valuable tool for real estate financial modeling and is a great function to have in your arsenal. For example, it can be used effectively to model for rental increases in a lease over time as well as if you have a lease that has started before your analysis start date. The second sheet of the downloadable excel file attached to this post will give you a clear example of how to do this.
Below are step by step instructions that you can use to try out the formula. Download the template below or simply open a blank Excel file on your computer and follow along.
How To Use DateDif (Step-By-Step Guide)
- Sheet 1: Practice Example to use with the step-by-step guide
- Sheet 2: RE Financial Modeling Example
- Step 1: In cell C9, type 12/31/1997
- Step 2: In cell D9, type 4/11/2015
- Step 3: In cell E9, type =DateDif(C9,D9,”d”)
The d in quotation marks will return the number of days between the two dates, which should read 6,310.
- Step 4: In cell F9, type =DateDif(C9,D9,”m”)
The m in quotation marks will return the number of full months between the two dates, which should read 207 for this example. If there are additional days after a full month count is reached it will not include those days as an additional month. If you want to include that additional month, see sheet 2 in the downloadable Excel file.
- Step 5: In cell G9, type =DateDif(C9,D9,”y”).
The y in quotation marks will return the number of full years between the two dates, which should read 17 for this example. If there are additional days or months after a full year count is reached it will not include those days or months as an additional year. If you want to include that additional year, see sheet 2 in the downloadable Excel file.
About the Author: Michael Belasco has over nine years of real estate and construction experience. He currently works for a global real estate investment, development, and asset management firm in San Francisco managing large scale development projects in the city. Michael has both an MBA and Master in Real Estate with a concentration in Real Estate Finance from Cornell University.