# How to Calculate the Expiration Date with an Excel Formula – 3 Steps

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.

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

## Things to Remember

• Cells must be in date format. Press Ctrl+1 and choose Date.

## Related Articles

<< Go Back to Days Between Dates | Date-Time in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Wasim Akram

Wasim Akram holds a BSc degree in Industrial and Production Engineering from Ahsanullah University of Science and Technology. Over the past 2 years, he has been actively contributing to the ExcelDemy project, where he has authored more than 150 articles. Now, he is working as an Excel VBA and Content Developer. He likes learning new things about Microsoft Office, especially Excel VBA, Power Query, Data Analysis, and Excel Statistics. He is also very interested in machine learning and... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF