Do you need to calculate your age in Excel? Or there is a requirement to find the number of years, months, and days between two dates.
Excel function DATEDIF helps us find this difference.
The syntax for this function is as under:
DatedIf (first Date, Second Date, Interval)
To use the above function, the first date must be occurring before the second date as per the calendar. If the first date is later than the second date, this function will return the #NUM error. If either of a first or second date is not a valid date, the #VALUE error is returned.
The value of interval can be used from the 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 the number of days. =DATEDIF(B1, B2, "m") ' will give the number of months you have lived on earth =DATEDIF(B1, B2, "m") ' It will give the number of months you have lived on earth =DATEDIF(B1, B2, "y") 'It will simply give your age in years. =DATEDIF(B1,B2,"y")&" years "&DATEDIF(B1,B2,"ym")&" months "&DATEDIF(B1,B2,"md") &" days" ' It will return your age in a more understandable and complete form like the 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.