Below is a simple formula to find a person’s current age using Google Sheets. Where `A1`

is the person’s Birthday.

`=DATEDIF(A1,today(),"Y")`

## Formula Explained

### Functions Used

`DATEDIF`

`TODAY`

With the above formula we are finding the difference between two dates; a birth date in cell `A1`

and today’s current date with the `TODAY`

function. We are returning this difference in years (`"Y"`

).

## Expanding Our Age Formula

The above formula is simple and effective, but by defining the goals of our formula we can expand our formula’s applications.

### Formula Goals

- Ignore blank cells
- Find a person’s current age or their age at the date of their passing
- Formula should utilize autofill capabilities

## Practice Sheet

Make a copy of our practice sheet to follow along as we expand our formula’s capabilities.

### Ignoring Blank Cells

We want to create a formula that will not produce an age if the “birthday” cell is blank. This is a useful addition to our formula because it enables us to use the autofill feature for a whole column and not have to worry receiving errors or ages if there is no person in a row. This also allows us to add additional people to our spreadsheet and their age will be calculated once we enter a birthday. If we were to run the formula above with a blank “birthday” cell our formula would return a value of 120 or higher.

#### Functions Used

To ignore cells without a birth date we will use the `IF`

and `ISBLANK`

functions.

`=IF(ISBLANK(A1),,DATEDIF(A1,today(),"Y"))`

### Formula Explained

The addition to our previous formula now features `IF`

and `ISBLANK`

functions. This formula will allow you to use the autofill function and only display an Age value for rows with a birthday. Assume `A1`

contains date of birth.

`ISBLANK`

returns a boolean value of either TRUE or FALSE depending if the referenced cell is empty.- With the
`IF`

function we can now create two options for our formula.- If
`ISBLANK(A1)`

is blank, then a value of TRUE is returned. This would result in the`IF`

function returning the`value_if_true`

value. This is the value after the first comma. Which we have specified to be blank. If there is no birthday we would like our age column to leave this cell blank. - If
`ISBLANK(A1)`

is not blank, then a value of FALSE is returned. This would mean there is content within the birthday column, then the`IF`

function returns the`value_if_false`

value. In this case, this would run our previous formula to find the difference between the person’s birth date and today’s date and return the difference in years.

- If

At this point you can use the autofill function for the entire “Age” column and it will only display ages for rows that have a birth date value.

### Current Age or Age At Passing

Let’s consider you have a spreadsheet of family members’ birthdays and their contact information. You want to find everyone’s age or the age of family members when they passed away.

#### Functions Used

To expand our function to display the current age or age of passing, we need to create a formula to evaluate cells in the row to determine which formula to run.

`=IF(ISBLANK(B1),DATEDIF(A1,TODAY(),"Y"),DATEDIF(A1,B1,"Y"))`

### Formula Explained

With this formula your Age column will return the person’s current age or if they have passed it will return the age of their passing. Assume `A1`

contains date of birth and `B1`

contains date of death.

`ISBLANK`

returns a boolean value of either TRUE or FALSE depending if the referenced cell is empty.- With the
`IF`

function we can now create two options for our formula.- If
`ISBLANK(B1)`

is blank, then a value of TRUE is returned. This would result in the`IF`

function returning the`value_if_true`

value. This is the value after the first comma. Which we have specified to return the person’s current age. If there is no date of death then we would like our age column to return the person’s current age. - If
`ISBLANK(B1)`

is not blank, then a value of FALSE is returned. This would mean there is content within the date of death column, then the`IF`

function returns the`value_if_false`

value. In this case, this would run our new`DATEDIF`

formula to find the difference between the person’s birth date and date of death and return the difference in years.

- If

### Combining Formulas

We created two formulas to address; blank cells, current age or age at death, and allowing for the formula to be copied down an entire column by returning a blank value when no data is in the Birthday column. We can now combine these two formulas into one and have a simple yet elegant formula that will give us a person’s age within Google Sheets.

`=IF(ISBLANK(A1),,IF(ISBLANK(B1),DATEDIF(A1,TODAY(),"Y"),DATEDIF(A1,B1,"Y")))`

Now our previous two formulas have been combined into one formula. You will notice, if done correctly, you have now created a nested `IF`

statement. Nesting functions within one another is a tool that will help you advance your Google sheets skill set and enable you to create more advanced formulas.