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.
- Sheet 1: Practice Example to use with the step-by-step guide
- Sheet 2: RE Financial Modeling Example
Frequently Asked Questions about the DateDif Example Workbook
What does the DateDif Example workbook demonstrate?
The workbook demonstrates how to use the DATEDIF function in Excel through a practice scenario and a real estate financial modeling application.
What is included in Sheet 1 of the workbook?
Sheet 1 includes a practice example designed to be used alongside a step-by-step instructional guide for learning the DATEDIF function.
What is included in Sheet 2 of the workbook?
Sheet 2 provides a real estate financial modeling example that applies the DATEDIF function in a CRE context.
What is the main function used in this workbook?
The main function featured in this workbook is DATEDIF, which calculates the difference between two dates in various units (days, months, years).
How many times has the workbook been downloaded?
The workbook has been downloaded 2,459 times.
Why is the DATEDIF function useful in real estate financial modeling?
DATEDIF is useful for calculating the duration of leases, loan terms, or investment holding periods with precision in months, years, or days, which is critical in financial modeling.
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.
Frequently Asked Questions about the DateDif Excel Formula
What is the DateDif formula in Excel?
The DateDif formula calculates the number of days, months, or years between two dates. Although it’s not listed in Excel’s function menu, it can still be used manually. For example:
=DateDif(start_date, end_date, “d”) returns the number of days between the two dates.
Why doesn’t DateDif appear in Excel’s function list?
Microsoft does not include DateDif in Excel’s ‘Insert Function’ list, despite it being functional. This makes it a “hidden” or undocumented formula, but it still works when typed directly into a cell.
How is DateDif used in real estate financial modeling?
It’s useful for calculating durations such as:
Time between lease start and analysis date
Length of holding periods
Timelines for rent escalations
The post mentions use cases in a Condominium Development Model and lease modeling scenarios.
What does “d”, “m”, and “y” return in DateDif?
“d” returns total number of days
“m” returns number of full months
“y” returns number of full years
Partial months or years are not counted. For example, 17 years and 11 months would return 17 with “y”.
Does DateDif account for partial months or years?
No. DateDif only returns full days, months, or years. To include partial time periods, you’ll need additional logic. Sheet 2 of the downloadable Excel file offers guidance on how to account for that.
Is there an example of how to use DateDif step-by-step?
Yes. The post walks through an example with start date 1/1/2021 and end date 5/26/2032:
=DateDif(C9,D9,”d”) → 6,310 days
=DateDif(C9,D9,”m”) → 207 months
=DateDif(C9,D9,”y”) → 17 years
Where can I get a practice file to use DateDif?
The post provides a downloadable Excel file with two sheets:
A practice example
A real estate modeling example
You can follow the instructions or watch the tutorial video included in the post.
