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
- 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.
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
- 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.
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.
- 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.
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
- How to Calculate Number of Months Between Two Dates in Excel
- Excel Formula to Count Days from Date
- How to Find Number of Weeks Between Two Dates in Excel
- How to Count Months from Date to Today by Using Excel Formula
- How to Calculate Years from Today in Excel
- How to Calculate Years Between Two Dates in Excel
- Calculate Years and Months Between Two Dates in Excel
