DateDif: The "Secret" Excel Function to Quickly Figure Out Time B/T Dates

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.

Using DateDif In Real Estate Financial Modeling

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, I use it in my Condominium Development Model as shown in the video. A second example can be found in the second tab of the downloadable excel file below where I use it to model for rental increases in a lease over time that has a start date prior to the analysis start.

DateDif Video Tutorial

The below video will walk you step by step through how the function work. Download the template below or simply open a blank Excel file on your computer and follow along.

DateDif Example

Sheet 1: Practice Example to use with the step-by-step guide
Sheet 2: RE Financial Modeling Example

Download Now!2282 Downloads

Video Walk Through Instructions

Step 1: In cell C9, type 1/1/2021
Step 2: In cell D9, type 5/26/2032
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 has spent a decade working in various capacities on more than $7 billion of real estate transactions spanning all asset classes and geographies throughout the USA. Michael is both the founder of Firm Ridge Real Estate, which has a core focus on niche and emerging real estate strategies and A.CRE Consulting, a real estate advisory and financial modeling firm that has provided services on projects totaling more than $21 billion to date. Prior, Michael was a founding member and COO of Stablewood Properties, an institutionally backed real estate operator. And before Stablewood, Michael was at Hines in San Francisco. Michael has both an MBA and Master in Real Estate with a concentration in Real Estate Finance from Cornell University.

Contact Michael