How to Change Excel Date Format Based on an IF Formula in WPS Office
Question details
User needs a date returned by an Excel formula to display as month only when status is DD and as day and month when status is PIF.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- A spreadsheet formula returns different dates depending on whether cell E4 contains DD or PIF, and the user needs the date display format to match each status.
- Observed behavior
- The returned dates currently use one display format, but DD should show only the month and PIF should show the day and month, such as 17 June.
Open the workbook in WPS Office Spreadsheets and confirm which cell contains the DD/PIF status and which cells contain the date results. Select the date cells before creating a formatting rule so the rule applies to the correct range.
Apply Conditional Formatting to Change the Date Display When Status Is PIF
Use this method when the underlying date value should remain unchanged and only the visible date format should change for PIF.
Conditional formatting keeps the cell value numeric and changes only the number format when the status cell equals PIF. DD cells keep the normal month-only format.
Open your workbook in WPS Office Spreadsheets, then select the cells that contain the dates to format, for example the range with the formula results.
Click the Home tab, choose Conditional Formatting, and select New Rule.
In the rule type list, select “Use a formula to determine which cells to format,” then enter =E4="PIF" using the actual status cell reference.
Click Format, go to the Number tab, choose Custom, and enter d mmmm so the date displays as 17 June.
Click OK to close the Format Cells dialog, then click OK again to apply the rule. Ensure the normal DD format remains set to a month-only format such as mmmm.
Return Formatted Text Directly with the TEXT and IF Formula
Use this method when the formula result itself must be display-ready text instead of a real date value.
Change Excel Date Formats Automatically with WPS Office Conditional Formatting
With WPS Office Spreadsheets, you can keep your date values intact and make the display switch between month-only and day-month formats by using conditional formatting rules and formulas—fully compatible with Excel workbooks.
- 1. Open your Excel workbook in WPS Office: Launch WPS Office Spreadsheets, click Menu in the top-left corner, choose Open, and select the .xlsx or .xls file containing your date formula.
- 2. Select the date result cells: Click and drag to select the cells that display the dates returned by your DD/PIF formula.
- 3. Create the PIF conditional formatting rule: Go to the Home tab, click Conditional Formatting > New Rule, and choose “Use a formula to determine which cells to format.”
- 4. Enter the formula and custom number format: Type =E4="PIF" in the rule description, click Format, choose Custom on the Number tab, enter d mmmm, and confirm with OK.
- 5. Apply and test the result: Click OK to apply the rule, then change the status cell to DD or PIF and verify the date changes between month-only and 17 June.
Frequently Asked Questions
Can I display the month for DD and the day-month format for PIF without changing the actual date value?
Yes. Use conditional formatting: keep the DD cells in a month format such as mmmm, and add a rule with the formula =E4="PIF" and the custom number format d mmmm. The real date value remains unchanged.
What formula returns the formatted text directly instead of using conditional formatting?
Use =IF(E4="DD",TEXT(EDATE(A4,1),"mmmm"),IF(E4="PIF",TEXT(A4+9,"d mmmm"),"")). Adjust A4+9 to A4+4 or change the cell references if needed.
Why is my conditional formatting rule not applying to the date cells?
Check that the formula references the correct status cell—usually the top-left cell of the selected range—and that the custom format is applied under Format Cells > Number > Custom. Also confirm the status cell contains exactly PIF without extra spaces.
Will these formatting methods work when I open the file in Microsoft Excel?
Yes. WPS Office saves workbooks in Excel-compatible formats, and conditional formatting rules and custom number formats such as d mmmm work in Microsoft Excel as well.
Can I apply this to an entire column instead of selected cells?
Yes. Select the whole date column, then create a rule using the first row’s status reference, such as =$E1="PIF" or =E1="PIF", and apply the custom number format d mmmm.
