How to Display an Excel Review Date as Month and Year
Question details
The user needs to calculate a review date three years after a release date and format it to display only the month and year (e.g., Aug 2028), while keeping the underlying value as a valid date for sorting and calculations.
- Product
- Spreadsheets
- Device & OS
- not provided
- Scenario
- Calculating a future review or expiration date based on an initial release date.
- Observed behavior
- The goal is to show the date in a specific text format ('mmm yyyy') without converting it to a plain text string, ensuring date-based formulas and sorting continue to work properly.
Ensure your starting release date is entered as a valid Excel date format rather than plain text, so the date calculation formulas can process it correctly.
Use EDATE Formula and Custom Cell Formatting
Calculate the exact date 36 months later using the EDATE function, then apply a custom number format to change its display appearance to month and year.
This method is highly recommended because it changes how the date looks without altering the actual serial date value stored in the cell. This means you can still sort chronologically or use the result in other mathematical date calculations.
Select the target cell where you want the review date to appear (e.g., C3). Enter `=EDATE(B3,36)`, assuming B3 contains your release date. This calculates the date exactly three years (36 months) later.
Right-click the cell containing your newly calculated date and select 'Format Cells' from the context menu.
Navigate to the 'Number' tab and select 'Custom' from the category list on the left. In the 'Type' box, enter `mmm yyyy` and click OK. The cell will now display the date like 'Aug 2028'.
Convert the Result to Text using the TEXT Function
Use the TEXT function if you want the final result to become a static text string rather than an underlying date value.
Calculate and Format Dates Easily with WPS Spreadsheet
WPS Spreadsheet provides full support for advanced date functions like EDATE and EOMONTH, along with powerful custom formatting tools to display your data exactly as needed.
- 1. Open your dataset: Launch WPS Spreadsheet and open the workbook containing your release dates.
- 2. Apply the calculation formula: Select an empty cell and use `=EDATE(cell_ref, months)` to calculate your future review dates.
- 3. Customize the display: Press Ctrl+1 to open the Format Cells dialog, select the Custom category, type 'mmm yyyy', and apply.
Frequently Asked Questions
What is the difference between EDATE and EOMONTH?
EDATE returns the exact same day of the month in the future or past (e.g., 3 years from Jan 15 is Jan 15), whereas EOMONTH always returns the last day of the calculated month (e.g., 3 years from Jan 15 is Jan 31).
Why is my calculated date showing up as a 5-digit number?
Spreadsheet software stores dates as sequential serial numbers for calculation purposes. If you see a number like 45123 instead of a date, the cell is formatted as General. Simply apply a date format by right-clicking the cell and selecting Format Cells.
Can I use a cell reference for the number of months instead of typing 36?
Yes, you can replace '36' in the formula with a cell reference (e.g., `=EDATE(B3, D3)`) where D3 contains the number of months. This is highly recommended for dynamic sheets where review periods might change.
