The sample dataset showcases Club members’ Names and the Starting Day.
To calculate the expiration date of their Monthly Payment:
Step 1 – Apply the EOMONTH Function to Get the Last Day of the Month
- Choose a cell to enter the formula. Here, D5.
=EOMONTH(C5,1)+0
- Press Enter to see the expiration date for the monthly payment.
- Drag down the Fill Handle to fill the rest of the cells.
This is the output.
Read More: Excel Calculates Difference Between Two Dates in Days
Step 2 – Add Days to a Date to Calculate Expiry Date
The membership is renewed after every 45 days.
- Select E5 and use the formula.
=C5+45
- Press Enter to see the expiration date for the monthly payment.
- Drag down the Fill Handle to fill the rest of the cells.
This is the output.
Read More: Excel Formula to Calculate Number of Days Between Today and Another Date
Step 3 – Combine the DATE, YEAR, MONTH, and DAY Functions to Calculate the Final Output
- Select F5 and enter the formula.
=DATE(YEAR(C5)+1,MONTH(C5),DAY(C5))- 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 the sequential date within a string.
- Press Enter to see the expiration date.
- Drag down the Fill Handle to fill the rest of the cells.
This is the output.
This is the final output.
Read More: Calculate Number of Days between Two Dates with VBA in Excel
Things to Remember
- Cells must be in date format. Press Ctrl+1 and choose Date.
Download Practice Workbook
Download the practice workbook.
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
<< Go Back to Days Between Dates | Date-Time in Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
