Wasim Akram
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 expiration date in excel. Stay tuned!
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
3 Easy Steps to Calculate Expiration Date Using Formula in Excel
In the following article, I am describing 3 quick and easy step-by-step processes with formulas to calculate expiration date in excel.
Suppose we have a dataset of some Members 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 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 given in a string.
- Choose a cell to apply the formula. Here I have selected cell (D5).
- Write the formula down-
=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: How to Count Months in Excel(5 ways)
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 a cell (E5) to apply the formula-
=C5+45
- Hence, 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: How to Add 7 Days to a Date in Excel (3 Methods)
Similar Readings
- How to Count Days from Date to Today Automatically Using Excel Formula
- [Fixed!] VALUE Error (#VALUE!) When Subtracting Time in Excel
- How to Calculate Working Days between Two Dates in Excel (4 Methods)
- Calculate Number of Days between Two Dates with VBA in Excel
- How to Calculate Remaining Days in Excel (5 Methods)
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 a 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.
- In conclusion, our final dataset is complete where we calculated the expiration date with formulas in excel.
Read More: Calculate Years and Months between Two Dates in Excel (6 Approaches)
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. Simple!
Conclusion
In this article, I have tried to cover all the steps with formulas to calculate 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. We, the ExcelDemy team, are always responsive to your queries. Stay tuned and keep learning.
Related Articles
- How to Add 30 Days to a Date in Excel (7 Quick Methods)
- Number of Months Between Two Dates in Excel: 5 Easy Ways to Find
- How to Add Dates in Excel Automatically (2 Simple Steps)
- Excel Formula to Calculate Number of Days Between Today & Another Date (6 Quick Ways)
- How to Calculate Years Between Two Dates in Excel (2 Methods)
