Calculate Age and Remaining Days in an Event using DateDif in Excel

You might need to calculate your age in Excel. There may be requirement to find number of years, months and days between two dates. Excel function DateDif helps us find this difference:

Syntax for this function is as under:

DatedIf (first Date, Second Date, Interval)

In order to use above function, first date must be occurring before the second date as per calender. If first date is later than second date, this function will return #NUM error. If either of first or second date is not a valid date, #VALUE error is returned.

Value of interval can be used from following as per requirement.

Interval Meaning Description
m Months Complete calendar months between the dates.
d Days Number of days between the dates.
y Years Complete calendar years between the dates.
ym Months Excluding Years Complete calendar months between the dates as if they were of the same year.
yd Days Excluding Years Complete calendar days between the dates as if they were of the same year.
md Days Excluding Years And Months Complete calendar days between the dates as if they were of the same month and same year.

Finding Your Age:

Your date of birth will be first date and the date at which you want to calculate your age will be Second date in DateDif function. You can use value of interval as required.

 =DATEDIF(B1,B2,”d”) will return your age in number of days.

=DATEDIF(B1,B2,”m”) will give the number of months you have lived on earth

=DATEDIF(B1,B2,”y”) will simply give your age in years.

=DATEDIF(B1,B2,”y”)&” years “&DATEDIF(B1,B2,”ym”)&” months “&DATEDIF(B1,B2,”md”) &” days” will return your age in more understandable and complete form like number of years, months and days.

 

Calculate Number of Days Remaining in a Year:

You can also calculate how many number of days are left in this year. You will first find current year and then use today as first date and 31st December of current year as second date and “d” as interval. Our formula will look like: =DATEDIF ( Today’s date, 31 December of current year, “d”)

 

Similarly by changing second date in above example, remaining days in any event can be calculated.

You can download Excel file having above calculations here.

Receive Excel Tips and Functions in your email

Sign up for our Newsletter -provide your email address to receive updates in your inbox.

Comments

  1. Dear Sir,
    I need formula for xl sheet i explain you what i need.
    i need to find my Age complete dd/mm/yyyy
    explain: (20 year 2 month 11 days )

    • you can use below formula to get the date in required format:

      =DATEDIF(B1,B2,”y”)&” years “&DATEDIF(B1,B2,”ym”)&” months “&DATEDIF(B1,B2,”md”) &” days”

      Where B1 is having your date of birth and B2 has current date. You can use =NOW() to use current date dynamically.

      All of this is also explained in this article under heading: “Finding Your Age.”

Speak Your Mind

*