How to Calculate Expiration Date with Excel Formula

Get FREE Advanced Excel Exercises with Solutions!

Sometimes we need to calculate the expiration date for our final reports to get a reminder before the end of the expiry date. Using Microsoft Excel you can calculate the expiration date in a quick and simple way. Today in this article, I will share with you the formula to calculate the expiration date in Excel. Stay tuned!


Excel Formula to Calculate Expiration Date: 3 Easy Steps

In the following article, I am describing 3 quick and easy step-by-step processes with formulas to calculate expiration dates in Excel.

Suppose we have a dataset of some member’s Name List joined for our food service-based business. On the other hand, we have the Starting Day of their service. Now we are going to calculate the expiration date for their Monthly Payment, Membership Payment, and Subscription Payment with formulas.


Step 1: Apply the EOMONTH Function to Get the Last Day of the Month

  • Above all, let’s calculate the monthly payment date with their starting date. To do so we will apply a formula with the EOMONTH function. Here the EOMONTH function returns the last day of the month.
  • Choose a cell to apply the formula. Here I have selected cell (D5) and inserted the formula.
=EOMONTH(C5,1)+0

Apply EOMONTH Function to Get the Last Day of the Month

  • Hit the Enter button to get the expiration date for the monthly payment.
  • Drag the Fill Handle down to fill all the cells.

  • Here we have successfully calculated the expiration date for all the customers with a simple formula.

Apply EOMONTH Function to Get the Last Day of the Month

Read More: Excel Calculates Difference Between Two Dates in Days


Step 2: Add Days with Date to Calculate Expiry Date

  • In this step, we will determine the membership payment date for all of the customers. For all the members the membership renewal date comes after every 45 days. Thus we will add 45 days with the current starting date to get the output.
  • Simply, select cell E5 to apply the formula.
=C5+45

 Add Days with Date to Calculate the Expiry Date

  • Press the Enter button and pull the Fill Handle down to fill the columns.
  • Finally, as you can see we have determined the expiry date of their membership program.

 Add Days with Date to Calculate the Expiry Date

Read More: Excel Formula to Calculate Number of Days Between Today and Another Date


Step 3: Combine DATE, YEAR, MONTH, and DAY Functions to Calculate Final Output

  • In this final step, we will calculate their subscription payment date with the combination of the DATE, YEAR, MONTH, and DAY functions. At the time of joining all of the members paid for their 1-year membership program. So the next payment date will come after 1 year on the same day.
  • To start with choose cell F5 and write the formula down.
=DATE(YEAR(C5)+1,MONTH(C5),DAY(C5))

Where,

  • The DATE function will create a valid date from a given date.
  • The YEAR function will calculate the year from the provided date in the string.
  • The MONTH function will return the month portion from a date.
  • The DAY function extracts only the sequential date within a string.

Combine the Date, Year, Month, and Day Functions to Calculate the Final Output

  • Click Enter to complete the formula and drag down the Fill Handle.
  • After that, you will get the perfect sequential date from the starting date for all the members.

  • Our final dataset is complete where we calculated the expiration date with formulas in Excel.

Combine the Date, Year, Month, and Day Functions to Calculate the Final Output

Read More: Calculate Number of Days between Two Dates with VBA in Excel


Things to Remember

  • After applying the formula sometimes it might happen you won’t get the date value. This happens if the cell is not formatted to date format. No worries! Press Ctrl+1 and then choose Date from the format cell window.

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


Conclusion

In this article, I have tried to cover all the steps with formulas to calculate the expiration date in Excel. Take a tour of the practice workbook and download the file to practice by yourself. I hope you find it helpful. Please inform us in the comment section about your experience. Our team is always responsive to your queries. Stay tuned and keep learning.


Related Articles


<< Go Back to Days Between Dates | Calculate Dates | Date-Time in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Wasim Akram
Wasim Akram

Wasim Akram holds a BSc degree in Industrial and Production Engineering from Ahsanullah University of Science and Technology. Over the past 2 years, he has been actively contributing to the ExcelDemy project, where he has authored more than 150 articles. Now, he is working as an Excel VBA and Content Developer. He likes learning new things about Microsoft Office, especially Excel VBA, Power Query, Data Analysis, and Excel Statistics. He is also very interested in machine learning and... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo