SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software Microsoft Office Tutorials and Downloads
Development resources, articles, tutorials, code samples and tools and downloads for ASP.Net, SQL Server, R Script, Windows, Windows Phone, AWS, SAP HANA and ABAP, like SAP UI5, Screen Personas, etc.

Calculate Age in Excel using DateDif Excel Function

This Excel tutorial shows how to calculate age in Excel using DateDif function with changing time interval input variables to get the desired output.

Excel DATEDIF function is used to calculate difference between two dates and express time difference in forms of days, months or years. Since DATEDIF Excel function is an undocumented function, Excel users sometimes use workarounds to calculate age of a person in terms of years, for example. But Excel DateDif date difference function provides a direct use to express time period between two dates in desired time intervals like years, months or days. If you use minus (-) between two dates in an Excel formula, it returns the date difference only in forms of days.

Although calculating time period between two dates is a frequent task and has been implemented using build-in functions in many development languages and programming scripts, Microsoft Excel did not have a date difference function listed in help documentation. It is interesting that DateDif function in Excel is kept undocumented and away from the eyes of Microsoft Excel users.

Let's not make an example how users can use DateDif function in Excel worksheets.
But before keep the syntax of Excel function DateDif in mind:

= DATEDIF( DateSmall; DateBig; "time interval" )

Excel DateDif function to calculate date difference

= DATEDIF( A2; A1; "y" ) -- Returns time as intervals of year
= DATEDIF( A2; A1; "m" ) -- Returns time as intervals of month
= DATEDIF( A2; A1; "d" ) -- Returns time as intervals of day
= A1-A2 -- Substructing smaller date from bigger date in forms of day

Calculate Age Function in Excel Worksheet

If your requirement is to express date difference in forms of a combination of different time intervals like 25 years 3 months and 12 days, then you need a more complex formula. This complex date difference formula will use a number of Excel DateDif function to form the desired output.
Let's apply this formula on an Excel age calculation formula to build age function.

Let's assume that you want to calculate the age of a person who was born at 2nd of June in 1995. Write todays date at A1 cell and the birthdate of the person at A2 cell.
Besides the time intervals "y", "m" and "d" we have additional time intervals that can be used with Excel DATEDIFF function.

These additional DateDif intervals are ym, yd and md intervals. These new intervals work like a mod function.

ym interval parameter returns the number of months (m) remaining after all years (y) are excluded.
yd interval parameter returns the number of days (d) remaining after all years (y) are excluded.
md interval parameter returns the number of days (d) remaining after all months (m) are excluded.

calculate age function using DATEDIF in Excel 2013

So we can get compute age of a person in Excel using the below formula:
=DATEDIF(A2;A1;"y")&" years "&DATEDIF(A2;A1;"ym")&" months "&DATEDIF(A2;A1;"md")&" days "

If you are not sure A2 is small date where else A1 cell has bigger date, you can prevent Microsoft Excel to display error using ERRORIF function for error handling.

=IFERROR(DATEDIF(A1;A2;"y");DATEDIF(A2;A1;"y"))&" years "&IFERROR(DATEDIF(A1;A2;"ym");DATEDIF(A2;A1;"ym")&" months "&IFERROR(DATEDIF(A1;A2;"md");DATEDIF(A2;A1;"md")&" days "))

Microsoft Office

Office 2013 Download
Install Office 2013
Activate Office 2013
Download Office 2010

Download Project 2016
Project 2013 Download
Project 2010 Download

Visio 2013 Download

Outlook Social Connector

Microsoft Office 2010, Office 2013 Download, Tutorial and Articles
Create Word Document for Label Printing using Excel Data
Color Cell Background Partially in Excel using Data Bars
Import Contacts to Outlook from VCF Export File
Cannot Open Hyperlink URL from Outlook Emails
Download Microsoft Project 2016
Import Data from SQL Server in Excel Document using Microsot Query
Outlook Social Connector for Facebook and LinkedIn
Change Default File Locations for Microsoft Office Documents
ScanPST and ScanOST Office Outlook Repair Tool
Calculate Age in Excel using DateDif Excel Function
Compact Outlook PST File
How to view Outlook Folder Size
Download Microsoft Visio 2013 Professional Edition free
Microsoft Excel 2013 Flash Fill Data Entry Assistant Tool
Microsoft Excel 2013 Flash Fill Samples
How to Install Microsoft Office 2013
How to Activate Microsoft Office 2013
Free Microsoft Project 2013 Download Trial Edition
Free Microsoft Office 2013 Download
Microsoft Office 2013: Couldn't install Office. Please try installing again
Microsoft Project 2013 for Office Developers
LinkedIn Outlook Social Connector Download
Download Microsoft Outlook Social Connector for Facebook
Free Download Microsoft Office for Mac 2011
How to Create Master Project, SubProject in Microsoft Project 2010
Microsoft Project 2010 Download Free Trial
Microsoft Project 2010 Team Planner
Add Column to Gantt Chart in Microsoft Project 2010
Copy and Email Timeline in Microsoft Project 2010
Microsoft Project 2010 Activation Error
Microsoft Office 2010 Installation (Professional Plus Edition)
Microsoft PowerPivot Download for Office 2010 Excel
Microsoft Office Redistributable Primary Interop Assemblies Download
Free Download Microsoft Office 2010
How to Save Office Communicator IM Conversation to Communicator Conversation History Folder
Restore Outlook Personel Folder from Outlook .pst (Outlook Data) File

Copyright © 2004 - 2020 Eralper YILMAZ. All rights reserved.
Community Server by Telligent Systems