# DateDif: The “Secret” Excel Formula (Updated 11.14.2020)

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

## 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.

Tags: