Change Font Color in WPS Spreadsheets When a Time Is Below 40 Minutes or Hours
Question details
The user wants time values in WPS Spreadsheets to appear in red when they are below 40:00 and in black when they reach or exceed that threshold, using conditional formatting or a custom number format.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- Working with duration or time values in a spreadsheet and needing a quick visual indicator for values below 40 minutes or 40 hours.
- Observed behavior
- Time values currently use the default font color, making it difficult to distinguish values below the 40:00 threshold.
Open the WPS Spreadsheets workbook and select the range containing the time values. Confirm whether your 40:00 target represents 40 minutes or 40 hours, because the comparison value differs for each case.
Use Conditional Formatting to Turn Times Below 40:00 Red
Best for automatic cell highlighting that updates whenever time values change.
If 40:00 means 40 hours, use =40/24 as the comparison value. If it means 40 minutes, use =40/24/60. Conditional formatting keeps the underlying value unchanged and only changes the displayed font color.
Open your workbook in WPS Spreadsheets and select the range or column that contains the time values.
On the Home tab, click Conditional Formatting, choose Highlight Cells Rules, and then select Less Than.
In the Less Than dialog box, type =40/24 for 40 hours, or =40/24/60 for 40 minutes.
Click Custom Format, set the font color to red in the Format Cells dialog, and click OK twice to apply the rule.
Use a Custom Number Format to Display Times Below 40 Hours in Red
Use this when you prefer a format-only solution without an extra conditional formatting rule.
Apply Time-Based Font Colors in WPS Spreadsheets
WPS Spreadsheets includes conditional formatting and custom number formats, so you can make time values below 40:00 red and keep values at or above the threshold black without changing the underlying data.
- 1. Open the workbook: Launch WPS Office and open the Excel workbook or create a new WPS Spreadsheets file.
- 2. Select the time range: Click and drag to select the cells that contain or will contain time values.
- 3. Apply conditional formatting: On the Home tab, click Conditional Formatting > Highlight Cells Rules > Less Than, enter =40/24 or =40/24/60, and choose a red font format.
- 4. Or apply a custom format: Open Format Cells > Custom and enter [Red][<1.66666666666666][hh]:mm;[hh]:mm to display qualifying times in red.
Frequently Asked Questions
How do I decide whether to use =40/24 or =40/24/60 in the less-than rule?
In the Excel and WPS time system, 1 day equals 24 hours. If 40:00 represents 40 hours, use =40/24. If it represents 40 minutes, use =40/24/60.
Can I apply this red font rule to an entire column before entering times?
Yes. Select the full column, create the conditional formatting rule with a relative reference such as =A1<40/24, and new time values below the threshold will be formatted automatically.
Why does my custom number format show all values in red or not recolor them?
Make sure the threshold 1.66666666666666 is entered correctly and that the format uses [hh]:mm. Also confirm the cells contain real time values, not text.
Will the red font carry over when I open the file in Microsoft Excel?
Yes. Conditional formatting and custom number formats saved in an .xlsx workbook work in Microsoft Excel because WPS Spreadsheets supports Excel file formats.
