Function Problems

Display Excel Dates as Quarters While Keeping Date Values

Camila Milosovich Oct 9, 2026 869 views

Question details

The user wants to format and display specific dates as quarters (e.g., Q1-24) without losing the underlying numerical date value, ensuring that calculations and conditional formatting rules continue to function properly.

How to Display Excel Dates as Quarters While Keeping Date Values

Product: Excel
Device & OS: not provided
Scenario: Creating reports or dashboards that require quarter-based visualization while simultaneously running date-based conditional formatting (such as highlighting 30-day intervals).
Observed behavior: Excel lacks a built-in custom number formatting code to directly display a date as a quarter while retaining the original serial date value for background calculations.

Before you start

Ensure your source data contains valid Excel serial dates rather than text strings, as the quarter calculation formula relies on the MONTH() and TEXT() functions to read the dates accurately.

Solution 1Recommended

Use a Helper Column with a Quarter Formula

Since there is no native custom number format for quarters, the most effective method is to create a separate display column with a text formula. This leaves your original date column intact for conditional formatting rules.

This method extracts the month from your original date, mathematically converts it into a quarter (1 through 4), appends a 'Q' prefix, and attaches the two-digit year.

Insert a new column

Right-click the column header next to your dates and select 'Insert' to create a new helper column for the quarter display.

Enter the quarter formula

Assuming your first date is in cell D2, click on the adjacent cell in the new column and enter the formula: ="Q"&QUOTIENT(MONTH(D2)-1,3)+1&"-"&TEXT(D2,"yy")

Apply formula to the entire column

Press Enter, then double-click or drag the fill handle at the bottom-right corner of the cell to copy the formula down to the rest of your rows.

Apply conditional formatting to the original dates

Select your original date column (e.g., Column D), go to the Home tab, click 'Conditional Formatting', and apply your 30-day highlighting rules based on the actual date values.

Use a Helper Column with a Quarter Formula

Hide the Source Data: If you only want the quarters visible on your final printed report or dashboard, you can right-click the original date column header and select 'Hide' after setting up your conditional formatting.

Efficient Spreadsheet Management

Easily Format Dates and Apply Formulas in WPS Spreadsheet

WPS Spreadsheet provides full support for advanced date formulas, helper columns, and complex conditional formatting. You can easily extract quarters from dates and manage reporting rules in a familiar, high-performance interface.

1. Open your workbook: Launch WPS Spreadsheet and open the file containing your date values.
2. Insert a display column: Add a new column next to your dates to serve as the quarter display.
3. Input the formula: Type or paste the formula ="Q"&QUOTIENT(MONTH(D2)-1,3)+1&"-"&TEXT(D2,"yy") into the new column.
4. Set conditional formatting: Navigate to the Home tab, click 'Conditional Formatting', and establish your rules on the original date column.

100% compatible with Microsoft Excel formulas and conditional formatting rulesAdvanced data visualization tools and intuitive formatting UIFree and lightweight alternative to heavy spreadsheet softwareCross-platform support for seamless workflow on Windows, Mac, and Linux

microsoft office alternative - wps office

Frequently Asked Questions

Can I use standard custom number formatting to show quarters instead of a formula?

No, neither Excel nor WPS Spreadsheet currently supports a custom number formatting code (like 'yyyy-mm-dd') that directly translates a date value into a quarter format (Q1, Q2). Using a helper column with a formula is the required workaround.

How do I change the formula to display the full four-digit year, like 'Q1-2024'?

You can easily modify the TEXT function at the end of the formula. Change TEXT(D2,"yy") to TEXT(D2,"yyyy"). The updated formula will be: ="Q"&QUOTIENT(MONTH(D2)-1,3)+1&"-"&TEXT(D2,"yyyy").

Will date-based conditional formatting work on the new formula column?

No, the formula converts the numerical date into a text string (e.g., 'Q1-24'). Text strings do not retain date properties, which is why you must apply any date-based conditional formatting to the column containing the original source dates.

Excel & Data Problems