How to Calculate the Expiration Date with an Excel Formula

The sample dataset showcases Club members’ Names and the Starting Day.

To calculate the expiration date for their Monthly Payment:

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

Use the EOMONTH function. It returns 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.

Step 2 – Add Days to a Date to Calculate Expiry Date

The membership renewal date comes after every 45 days. To determine the membership payment date, add 45 days to the starting date to get the output.

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.

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

Use the DATE, YEAR, MONTH, and DAY functions. Members paid 1-year membership program when they joined the club. The next payment date will be after 1 year on the same day.

Select F5 and enter the formula.

=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 the sequential date within a string.