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.

How to Calculate Remaining Shelf Life Percentage in Excel

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

Read More: Make an Excel Spreadsheet Automatically Calculate Percentage


Step 2 – Calculate the Date of Expiration in Excel

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

How to Calculate Remaining Shelf Life Percentage in Excel

  • 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)

How to Calculate Remaining Shelf Life Percentage in Excel

  • 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

How to Calculate Remaining Shelf Life Percentage in Excel

  • 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.

How to Calculate Remaining Shelf Life Percentage in Excel

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.

Download the Practice Workbook


Related Articles


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

Get FREE Advanced Excel Exercises with Solutions!
Sanjida Mehrun Guria
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

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo