Excel DATEDIF function explained
DATEDIF is practically an insider tip, as the function is not even included in the internal list in the software. The function for calculating the difference (DIF) between two points in time (DATE) is a relic from Lotus 1-2-3, the predecessor to Microsoft Excel. DATEDIF is intended to calculate the length of a period of time and output the result in different units.
What do you need DATEDIF for?
How many days are left until the deadline? How many months are left until my vacation? How many years have I been working with the customer? The major advantage of DATEDIF is not that it can calculate a difference between a start and an end date – you could do that using simple subtraction in Excel. The function lets you adapt the result unit. That means you can decide whether you want to show the corresponding time period in days, months or years.
The Excel DATEDIF function only refers to full days. It does not show time.
DATEDIF syntax
DATEDIF calculates the time difference between a start and an end date. Both data points must be known for the function. You also have to specify the unit in the function to enable the result to be displayed.
=DATEDIF(Start_date,End_date,Unit)The start and end dates must be real date specifications. You can either enter the time directly in the function using the DATE function or use a cell reference. The cell in which you enter the specific day must be formatted as a date. The end date must follow the start date chronologically. The resulting unit is specified as text. It must be entered in quotes within the function. However, you can refer to another cell in this parameter. You have six different options for this parameter.
- Y: Years
- M: Months
- D: Days
- YM: Difference between the months. Years and days are ignored.
- MD: Difference between the days. Months and years are ignored.
- YD: Difference between the days and months. Years are ignored.
The differences between the various units are best explained using an example. We want to calculate the difference between 1/1/2019 and 2/2/2020.
- Y: 1
- M: 13
- D: 397
- YM: 1
- MD: 1
- YD: 32
The first three results are relatively easy to understand: no more than a year, no more than 13 months and no more than 397 days have passed between the two dates. Only full units are shown. Half years are not included in the count. However, the last three options are more interesting. YM is 1 because the year data is ignored entirely. There is just one month between January and February. MD is also just 1, due to the change from the first to the second day. The last option gives you the number of days – taking the different months into consideration – but ignores the different years.
DATEDIF in practice
DATEDIF is easy to implement as a form in Excel. Create one cell for the start date and another for the end date. The unit can also either be entered directly in a cell or selected from a drop-down menu.
=DATEDIF($A$1,$B$1,$C$1)
If you want to find out how many years, months and days there are between two points in time, i.e. get three items of information in one result, the information can also be concatenated. You can do so using a function or the arithmetic operator “&”.
=DATEDIF(A1,B1,"Y")& " Years " & DATEDIF(A1,B1,"YM") & " Months " & DATEDIF(A1,B1,"MD") & " Days "
For example, you can use this method to show your age. The spaces between the quotation marks and the actual text are required by the formula so that numbers and letters are not directly beside one another after concatenation. Another function can help keep the result up-to-date at all times: Excel always outputs the current date using the TODAY function. You can also insert it directly into the DATEDIF function. For example, this can help you calculate the length of a membership.
=DATEDIF(B2,TODAY(),"M")
DATEDIF treats every day equally. However, you can also distinguish working days from weekends and public holidays using the WORKDAY function in Excel.
Related articles
Rawpixel.comShutterstock Excel's WORKDAY function explained
Many people use dates regularly in Microsoft Excel. However, the problem with dates is that they don’t automatically distinguish between working days and weekends. Excel does, however, provide a function that allows you to analyze working days separately. Using Excel’s WORKDAY function, you can calculate deadlines more precisely.
Rawpixel.comShutterstock Excel: ROUNDDOWN – an explanation of this handy function
When performing complex calculations in tables, you can quickly produce values that are not fit for everyday use because they have far too many decimal places. The ROUNDDOWN function in Excel can help with this. It uses a simple formula to simplify your workflow. You can use it to round down any number to the desired number of decimal places.
Practical functions in Excel: COUNTIF explained
What is COUNTIF? With Excel, Microsoft provides a helpful spreadsheet program. The application combines numerous functions – most of which many users are unaware of. It therefore makes all the more sense to learn more about it and use the program for more than just creating tables. The COUNTIF function helps you create statistics, for example. We explain clearly how to use COUNTIF correctly in…
Ground PictureShutterstock Excel: working with dates
Many countries have their own unique way of displaying dates and times. To be able to switch seamlessly between date and time formats, the information must first be stored in a universal format. You can use the DATE function in Excel for this purpose. In this article, you’ll learn how to work with the Excel DATE formula.
Automatically number rows in Excel
When working with big datasets, it’s easy to lose sight of the bigger picture. If you want to add row numbering in Excel, there’s no convenient button to do so. Find out how you can easily integrate continuous numbering in Excel and adjust it to your needs.
