Home > Microsoft Excel > How to Use the Excel DATEDIF Function? 3 Easy Examples Show
The Excel DATEDIF formula calculates the gap between two date ranges in the format of years, months, or days. It was originally introduced in a very old Excel version and documented only in Excel 2000. But, it can still be used in the latest versions of Excel including Office 365. DATEDIF function in ExcelIn this tutorial, I’ll show you how to use Excel DATEDIF with the help of some examples. You’ll learn:
Related: Excel DATEDIF Syntax
Where start_date is the beginning date of the time period end_date is the ending date of the time period and Unit is the type of difference you are looking for ( days, months, or years) You can use the following time unit options inside the DATEDIF formula: ‘y’ – to find the difference in terms of complete years. ‘m’ – to find the difference in terms of complete months. ‘d’ – to find the difference in terms of complete days. ‘ym’ – to find the difference in months, ignoring months. ‘md’ – to find the difference in days, ignoring months ‘yd’ – to find the difference in days, ignoring years. Note: Excel will not display or suggest the syntax for DATEDIF as it does for other normal functions. Also Read: How to Use Goal Seek in Excel? (3 Simple Examples) How to Insert Multiple Rows in Excel? The 4 Best Methods How to Autofit Excel Cells? 3 Best Methods How to Use the Excel DATEDIF Function?The following examples will help you appreciate the formula’s usage better. I recommend you open a rough spreadsheet and follow along to gain a better understanding. Example 1: Common UsesIn this example, column D contains the date March 1, 2019 and column E contains the date April 5, 2021. In column F, we can use the following variations of the DATEDIF function to calculate the difference in years, months and days respectively. F5=DATEDIF(D5,E5,”y”) // returns 2 F6=DATEDIF(D6,E6,”m”) // returns 25 F7=DATEDIF(D7,E7,”d”) // returns 766 Excel DATEDIF Basic UseExample 2: Calculate the Difference in Days Using DATEDIFIn this example, we use the DATEDIF formula to calculate the difference in days between dates. Please note that you can use three different time units for days inside the DATEDIF function: (1) total days, (2) days ignoring years, and (3) days ignoring months and years. =DATEDIF(D11,E11,”d”) // returns the difference in total days i.e 766 days =DATEDIF(D12,E12,”yd”) // returns the difference in days ignoring years i.e 35 days =DATEDIF(D13,E13,”md”) // returns the difference in days ignoring months and years i.e 4 days Excel DATEDIF – Difference in daysExample 3: Calculate the Difference in Months Using DATEDIFUse the DATEDIF formula to calculate the difference in months between dates. You can use two different time units for months inside the DATEDIF formula: (1) complete months and (2) in terms of months but ignoring years =DATEDIF(D17,E17,”m”) // result: difference in complete months =DATEDIF(D18,E18,”ym”) // result: difference in terms of months but ignoring years Using DATEDIF to find the difference in monthsExample 4: Calculate the Difference in Years Using DATEDIFUse the DATEDIF formula to calculate the difference in years between two dates. =DATEDIF(D21,E21,”y”) // difference in complete years DATEDIF and YEARFRAC to find the difference in yearsA small caveat: Since, DATEDIF rounds off the difference to the next lowest value, it is advisable to use YEARFRAC to find a more accurate difference in years. Important Note:The DATEDIF function returns the number of complete days, months, or years by rounding them off to the next smallest value. This means that it may give incorrect results when calculating the date difference in some cases. It is advisable to be careful while using DATEDIF and double-check the results before proceeding further. The #NUM! error usually arises when the start_date is greater than the end_date. The #VALUE! Error usually occurs when invalid arguments are used inside the formula. Suggested Reads: How to Group Worksheets in Excel? (In 3 Simple Steps) How to Shade Every Other Row in Excel? (5 Best Methods) How to Use the Excel Fill Handle Easily? (Top 3 Uses with Examples) Closing ThoughtsIn this short guide, I have shown how to properly use the DATEDIF function in Excel. I hope you find this very useful in your spreadsheet calculations. If you have any questions about this or any other Excel feature, feel free to let us know in the comments. We’re always happy to help. Visit our free Excel Resources Centre for more high-quality Excel guides. Want to learn more about Excel? Click here to access our advanced Excel courses with in-depth training modules. You can train your entire team in Excel and other business software, for a single low monthly fee by clicking here. Simon CalderChris “Simon” Calder was working as a Project Manager in IT for one of Los Angeles’ most prestigious cultural institutions, LACMA. He taught himself to use Microsoft Project from a giant textbook and hated every moment of it. Online learning was in its infancy then, but he spotted an opportunity and made an online MS Project course - the rest, as they say, is history! Wie funktioniert Datedif Excel?Mit der DATEDIF-Funktion kannst du die Zeit zwischen einem Ausgangsdatum und einem Enddatum in Jahren, Monaten oder Tagen ermitteln. Die zurückzugebende Zeiteinheit bestimmst du über das Argument Einheit. Die Einheit muss als Text (Großschreibung beachten) an die Funktion übergeben werden.
Wie kann ich in Excel das Alter berechnen?=DATEDIF(B3;HEUTE();"Y")
Damit berechnet Ihnen Excel das Alter aus der Differenz vom Geburtsdatum und dem heutigen Datum. Wenn Sie die Funktion auf die gesamten Daten anwenden wollen, klappt das problemlos, indem Sie das grüne Auswahlkästchen nach unten ziehen.
Wie viele Jahre zwischen zwei Daten Excel?In diesem Beispiel zeigen wir Ihnen wie Sie ganz einfach Differenzen zwischen zwei Datumswerten berechnen können. Excel stellt dafür die Funktion =DATEDIF(Ausgangsdatum;Enddatum;Differenz-Parameter) zur Verfügung.
Wie zähle ich Tage zwischen zwei Daten einschließlich des startdatums in Excel?In Excel können wir Tage zwischen zwei Daten mithilfe einer einfachen Formel berechnen: = Enddatum - StartdatumDiese Formel schließt jedoch das Startdatum aus.
|