How to Calculate Expiration Date with Excel Formula

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

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: 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

 Add Days with Date to Calculate the Expiry Date

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

 Add Days with Date to Calculate the Expiry Date

Read More: How to Add 7 Days to a Date in Excel (3 Methods)


Similar Readings


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.

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.

  • In conclusion, 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 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

Wasim Akram

Wasim Akram

Hi! my name is Wasim Akram. I am a graduate in mechanical engineering from Ahsanullah University of Science & Technology. I am passionate about learning new things and writing articles. I write article about Microsoft Excel and it's my favourite thing to do.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo