Google Sheets – Calculate age with the DATEDIF function

Calculating age in a spreadsheet can be a painful process using many of the formulas floating around the web today. Dates act strangely in spreadsheets so care must be taken when working with them. Fortunately, calculating age can be done much easier using a single function in Google Sheets called DATEDIF. The DATEDIF function will return the difference between two dates in days, months, or years. Using these units in combination can get you a variety of output options.

Use the TIMEDIF add-on if you are working with dates and times.

Syntax

=DATEDIF(start_date,end_date,unit)

start_date Date at which to start the calculation

end_date Date at which to end the calculation

unit Type of output. Choices are “Y”,”M”,”D”,”YM”,”YD”, or “MD”.

• Y – Number of whole years elapsed between start and end dates
  • YM – Number of months elapsed after the number of years shown with the “Y” unit. Will not exceed 11.
  • YD – Number of days elapsed after the number of years shown with the “Y” unit. No matter how many days after the last year, starts counting after end of last full year and is never over 364.
• M – Number of whole months elapsed between start and end dates
  • MD – Number of days elapsed after the number of months shown with the “M” or “YM” unit. Can’t go higher than 30.
• D – Number of whole days elapsed between start and end dates

Examples

Follow image below for the live Google Sheet with this data

Let’s say that you have three columns of data. As long as you have start and end dates, you are ready to go.

Now add 4 columns to the right for the DATEDIF formula with “Y”, “YM”, “YD”, and a column with them joined together. Remember that all of these are in the live Google Sheet shared above. The Y, YM and MD are used as the “unit” in each respective column. The “Y” column is showing the person’s age in years. The “YM” is showing the number of months that have elapsed since the last year shown. The “MD” column is showing the number of days that have elapsed since the last whole month.

 

There are other types of units as well. This next example uses M and MD. Note that the number of months goes over twelve since the years are not in the formula’s output. The column using MD as the unit is the same calculation as above.

 

This last example uses just the D unit. If you are just using this function to calculate days, it would be easier to use a subtraction formula for the number of days or the DAYS function. Dates in spreadsheets are actually stored as integers so they can easily be added and subtracted. The last column, which is unlabeled, contains a subtraction formula.

 

If you want to calculate and age and have it always be current when you open the spreadsheet, consider using the function called TODAY as your end date. TODAY returns the current day.

To just calculate the number of fractional years between two dates, consider using the YEARFRAC function.

To find weeks, use this formula.