How to work out an age or the months between dates
In Grid by Zaweba, DATEDIF gives whole years, months or days between two dates for ages and anniversaries, and YEARFRAC gives the fraction of a year.
Use DATEDIF for whole years, months or days, and YEARFRAC for a fraction of a year. In Grid by Zaweba, DATEDIF with a date of birth, today's date and the unit "Y" returns someone's age in completed years. Change the unit to "M" for the number of whole months between any two dates, or "D" for days.
Why simple subtraction isn't enough
Subtracting one date from another gives the number of days between them, which is exact but not what most people want. Dividing by 365 is close, but leap years make it drift, and someone a day short of their birthday can come out a year older than they are. DATEDIF counts completed years and months the way a person would, so an age turns over on the birthday itself.
Working out an age
Put the date of birth in one cell. In the next, type =DATEDIF( then the birth date cell, then TODAY() for the end date, then "Y" in quotes. The result is the age in whole years, and because TODAY() changes each day, the age updates by itself when the birthday comes round.
For an age as of a particular day, such as the start of a school year or the date of an event, use that date instead of TODAY(). That's often what forms and registers really need.
Months, and years and months together
The unit "M" gives whole months between two dates. That's handy for tenancy lengths, subscriptions and how long someone has been a member.
For an answer like 7 years and 4 months, use two DATEDIFs: one with "Y" for the years, and one with "YM", which gives the months left over after the whole years. Join them with text in one formula to show it as a sentence in a single cell. "MD" gives the days left over after whole months, though it can give odd results around months of different lengths, so check it if you rely on it.
YEARFRAC for fractions of a year
YEARFRAC returns the time between two dates as a decimal, such as 3.42 years. It's the right choice when you need to multiply by an annual figure: a pro-rata salary, interest for part of a year, or a share of an annual fee. An optional basis argument chooses the day-count convention, which matters in finance where different conventions are standard.
Related date functions
EDATE adds a number of months to a date, which gives renewal dates and anniversaries without worrying about month lengths. EOMONTH returns the last day of a month a set number of months away, useful for billing periods. Both sit in Grid's Date and Time family with DATEDIF and YEARFRAC.
Things that trip people up
The start date must come first. DATEDIF with the dates the wrong way round returns an error rather than a negative number.
Dates must be real dates, not text. Grid uses the 1900 date system, so a proper date is a serial number formatted to look like a date. A date pasted from a web page may arrive as text. If a formula returns #VALUE!, check the input cells first.
A sheet with many TODAY() formulas recalculates them every time the workbook changes, since TODAY is a volatile function. That's quick in Grid, which only recalculates what depends on a change, but it does mean the answers aren't fixed. If you need a fixed age for a record, copy the cells and use Paste Special to paste values.
With DATEDIF for whole units and YEARFRAC for fractions, Grid by Zaweba covers both everyday ages and the more precise sums that money calculations need.
