# How to Calculate Remaining Shelf Life Percentage in Excel (With Easy Steps)

### Step 1 – Prepare the Dataset

• Insert the product names in the Product column.
• Insert the manufacturing date in the MFG Date column.
• Provide the information on the length of use in the Duration of Usage column.

• Make sure you define the unit of duration. We put all durations in Days.

### Step 2 – Calculate the Date of Expiration in Excel

• Insert this formula in cell E5:
`=C5+D5`

• Press Enter.
• You will see the expiry date of the first product based on the MFG Date and duration.

• Use the Fill Handle tool to drag this formula in the cell range E6:E9.

Additional Tip: If your duration of usage is in weeks, months or years, apply these formulas for each type of duration.

• For 3 weeks:`=E5+3*7 `
• For 3 months: `=EDATE(E5,3) `
• For 3 years: `=DATE(YEAR(E5)+3,MONTH(E5),DAY(E5))`

### Step 3 – Find Out the Percentage of Completed Shelf Life

• Insert this formula in cell F5:
`=YEARFRAC(C5,E5,1)`

• Press Enter.

We used the YEARFRAC function to calculate the value of duration between the manufacturing date and the expiry date in fractions.

• Format the cell as a percentage.
• Apply this formula in the cell range F6:F9 using the AutoFill tool.

### Step 4 – Calculate the Remaining Shelf Life Percentage

• Insert this formula in cell G5.
`=1-F5`

• Hit Enter.

• Drag the bottom corner of cell G5 down to cell G9 to get the value for each product.

### Step 5 – Check the Expiration Status in Excel

• Insert today’s date in cell D11. We inserted the present day as 10 August 2022 for calculation.

Note: You can use the TODAY function to find the present day based on your region. Simply apply this formula in cell D11.

`=TODAY()`
• Insert this formula in cell F5.
`=IF(\$D\$11>E5,"Expired",(E5-\$D\$11))`

• Press Enter.

We used the IF function to make a comparison between the values of cells D11 and E5. The output will be ExpiredÂ if the former is earlier.

• Apply this formula in cell range F6:F9 and you will get the final output.

## Things to Remember

• Make sure you insert the dates in the Date format before calculation.
• Format the result cell in the Percentage format.
• If you want to find the Duration of Usage, apply this formula:
`=Expiry Date - MFG Date`
• Mind the blank cells in your dataset. Otherwise, it will show a false result.

## Related Articles

<< Go Back to Calculating PercentagesÂ | Calculate in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Sanjida Mehrun Guria

Hello! Welcome to my blog. I have completed my Bachelor in Architecture from Ashanullah University of Science & Technology, Dhaka. I am a passionate, goal-oriented person with an immense thirst for knowledge and an attitude to grow continuously. Besides Architectural work, I find it very enthusiastic to work in Excel blogging. Exceldemy is a platform where I have got the opportunity to flourish my skill in Microsoft Excel and therefore, here I will be posting blogs related to... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF