How to Calculate an Expiration Date from a Manufacturing Quarter in Excel
Question details
The user needs to calculate an exact expiration date (the final day of the corresponding quarter) based on a text string representing a manufacturing quarter and year, plus a numeric service life in years.

- Product
- Excel
- Device & OS
- not provided
- Scenario
- Tracking product lifecycles, managing inventory, or maintaining warranty expiration records where manufacturing dates are recorded by quarter rather than exact dates.
- Observed behavior
- The goal is to extract the quarter and year from a string like '1Q/18', add a specific number of years, and output the last calendar day of that expiration quarter formatted as a date.
Ensure your manufacturing quarter data follows a consistent text format, such as '1Q/18', and that the service life is entered as a numeric value in years to prevent formula calculation errors.
Use a combination of EOMONTH, DATE, and Text functions
This method extracts the quarter and year from the text string, converts them to a valid date, adds the service life years, and returns the final day of that expiration quarter.
The formula relies on the RIGHT and LEFT functions to parse the year and quarter from the '1Q/18' string format. It multiplies the extracted quarter by 3 to pinpoint the final month of that quarter. Finally, the EOMONTH function advances the date by the specified service life (converted into months) and outputs the final day of that month.
Assume your service life in years is located in cell B3, and the manufacturing quarter text string (e.g., '1Q/18') is located in cell C3.
Select the cell where you want the calculated expiration date to appear and enter the formula: =EOMONTH(DATE(2000+VALUE(RIGHT(C3,2)),VALUE(LEFT(C3,1))*3,1),12*B3)
Press the Enter key. The result may initially appear as a 5-digit serial number instead of a standard calendar date.
Right-click the result cell, select 'Format Cells', navigate to the 'Number' tab, choose 'Date', and select your preferred date formatting style.

Calculate Expiration Dates Easily with WPS Spreadsheet
WPS Spreadsheet fully supports advanced date and time functions like EOMONTH, DATE, and text extraction formulas, making it incredibly simple to manage inventory and automate lifecycle tracking.
- 1. Open WPS Spreadsheet: Launch WPS Office and open a new or existing spreadsheet containing your manufacturing data.
- 2. Input your data: Enter your manufacturing quarters into one column and the corresponding service life years into another.
- 3. Apply the formula: Paste the EOMONTH and DATE formula into your expiration date column to instantly calculate the correct final days.

Frequently Asked Questions
What if my manufacturing year format uses four digits instead of two?
If your manufacturing string format is '1Q/2018' instead of '1Q/18', you must adjust the formula to extract 4 characters for the year. Change RIGHT(C3,2) to RIGHT(C3,4) and remove the '2000+' addition inside the DATE function.
Why does my calculated date appear as a random five-digit number?
Dates in spreadsheet programs are stored internally as serial numbers. To resolve this and view the actual calendar date, simply right-click the cell, select 'Format Cells', and apply a 'Date' format from the Number tab.
Can I calculate the expiration date if the service life is recorded in months instead of years?
Yes, if your service life data in cell B3 is already represented in months rather than years, you simply need to change the '12*B3' argument at the very end of the formula to just 'B3'.




