As machines are not a hundred percent error-proof, sometimes errors can be found in the final result. As a result, users have to find those errors or defects and solve them. Defect age is related to this situation. This is the difference between the time from which the error has been solved and the time the users found the error. Measuring this time correctly is important in terms of production. In this article, I will show you how to apply the defect aging formula in Excel.

## Download Practice Workbook

You can download the free **Excel** workbook here and practice on your own.

## Step-by-Step Procedures to Calculate Defect Aging by Applying Formula in Excel

In this article, you will see the step-by-step procedures to apply the defect aging formula in Excel. To apply this formula, I will combine** the IF function** and

**. Also, in the later sections, I will apply the aging formula in Excel using**

*the TODAY function***. Then, I will apply the aging formula in Excel for days and months.**

*the IF function*To apply the defect aging formula, I will use the following sample data set. I have some dates for finding defects and solving them in some random machines.

### Step 1: Preparing Data Set

In our first step, I will prepare the data set for applying the aging defect formula. To do that,

- Firstly, make an extra column along the main data set under column
.*E* - Then, name the column
.*Defect Aging* - Here, I will apply the formula to calculate the result in days.

**Read More: ****How to Calculate Days with Aging Formula in Excel**

### Step 2: Applying Formula to Calculate Defect Aging

In the second step, I will apply the formula for calculating defect aging to the newly created column in the previous step. For that,

- First of all, use the following combination formula of
and*the IF function*in cell*the TODAY function*.*E5*

`=IF(D5<>"",D5-C5,TODAY()-C5)`

**Formula Breakdown**

**=IF(D5<>””,D5-C5,TODAY()-C5) **

- Firstly, the IF function will see if cell
is empty or not.*D5* - Secondly, if it finds the cell value then, it will subtract it from the cell value of
to calculate the date.*D5* - Moreover, if it does not find any value in
, then it will delete the current date from*C5*.*D5*

- Then, press
to get the desired result in cell*Enter*, showing defect aging for the first machine, that is*E5*days.*10* - Thirdly, use the
feature, to drag the same formula for the lower cells of that column.*AutoFill*

**Read More: ****How to Do Aging Analysis in Excel (with Quick Steps)**

### Step 3: Showing Final Result

In the last step of my procedure, I will show the final result after applying the function in all the cells of the defect aging column.

- After applying
, the formula will get adjusted for the lower cells from cell*AutoFill*.*E6:E8* - Then, you can see the values of defect aging for all the machines.

**Read More: ****How to Use IF Formula for Aging Buckets in Excel (3 Suitable Examples)**

## Applying Aging Formula in Excel Using IF Function

In this section, I will show you how to apply the aging formula in Excel using ** the IF function**. Here, I will apply

**in the nested format. I will apply the nested**

*the IF function***formula to show the age group of some random people of different ages. Go through the following steps for a better understanding.**

*IF***Step 1:**

- Firstly, take the following data set that contains the names and ages of some random people in columns
and*B*respectively.*C* - Then, to know the age groups, insert the following nested
formula in cell*IF*.*D5*

`=IF(C5<16,"Children",IF(C5<=25,"Young Adults",IF(C5<35,"Middle-aged Adults","Senior Citizen")))`

**Formula Breakdown**

** =IF(C5<16,”Children”,IF(C5<=25,”Young Adults”,IF(C5<35,”Middle-aged Adults”,”Senior Citizen”))) **

: This formula denotes if the value of cell*IF(C5<16,“Children” …)*is less than*C5*which means if the age is less than*16*, then it will return a string “*16*”. It actually shows that people with the age below*Children*will be in the*16*group.*Children*: This part means if the value of cell**IF(C5<=25,“Young Adults” ….)**doesn’t meet the first condition, it will automatically consider this second condition. The second condition demonstrates if the value of cell*C5*is less than or equal to*C5*, it will return a string called*25*”. This actually denotes the age limit of less than or equal to*“Young Adults*will be in the*25*group.*Young Adults*: If the value of cell*IF(C5<35,“Middle-aged Adults” ….)*doesn’t match the above two criteria then, it will go through this condition. This formula says if the value of*C5*is less than*C5*, it will return a string called “*35*”. This actually demonstrates the age limit of less than*Middle-aged Adults*will be in the*35*group.*Middle-aged*- Finally, if the value of
doesn’t match any of the above criteria, then it will automatically return a string “*C5*”.So, if the age limit of*Senior Citizen*is above*C5*, it will be in the*35*group.*Senior Citizen*

**Step 2:**

- Secondly, press
and see the age group for*Enter***Andrew**in cellwhich is*D5*.*Young Adults* - Consequently, drag the same formula to the lower cells of that column by using
.*Fill Handle*

**Read More: ****Aging Formula in Excel Using IF (4 Suitable Examples)**

## Applying Aging Formula in Excel for Days and Months

In the last section of this article, I will demonstrate the procedure to apply the aging formula for days and months in Excel. Here, I will also use a combination of some Excel functions that include the ** INT**,

**, and**

*YEARFRAC***functions. The steps to apply this formula are as follows.**

*TODAY***Step 1:**

- First of all, to determine one’s age in days and months, I will use the following sample data set.
- Here, I have the names and dates of birth of some random people.
- Then, to calculate the age of the first person in days type the following combination formula.

`=INT((YEARFRAC(C5,TODAY(),1)*365))`

**Formula Breakdown**

** =INT((YEARFRAC(C5,TODAY(),1)*365)) **

:*YEARFRAC(C5,TODAY(),1)*will count the difference in the year between cell*The YEARFRAC function*and the current year in fractions.*C5*: Then, I will multiply the year by*(YEARFRAC(C5,TODAY(),1)*365)*to convert it into days.*365*: Lastly,*INT((YEARFRAC(C5,TODAY(),1)*365))*will turn the fraction value into its nearest integer.*the INT function*

**Step 2:**

- Secondly, hit
to see the desired age in days.*Enter* - Then, with the help of
use the same formula in the following cells of the column.*AutoFill*

**Step 3:**

- Thirdly, to apply the aging formula for months, in cell
use the following combination formula.*E5* - Here, the applied formula is the same as the previous one, but in the place of
, I will multiply the year value by*365*to convert it into months.*12*

`=INT((YEARFRAC(C5,TODAY(),1)*12))`

**Step 4:**

- Finally, after pressing
and dragging*Enter*, you will get the desired ages in months for all the people.*Fill Handle*

**Read More: ****How to Use Multiple If Conditions in Excel for Aging (5 Methods)**

## Conclusion

That’s the end of this article. I hope you find this article helpful. After reading the above description, you will be able to apply the defect aging in Excel. Please share any further queries or recommendations with us in the comments section below.

The** ExcelDemy** team is always concerned about your preferences. Therefore, after commenting, please give us some moments to solve your issues, and we will reply to your queries with the best possible solutions ever.