You can use CF to control how positive/negative numbers display with text too.
Select the range you want to format (col B) then enter the rules below, in the order given.
Due in more than 90 days
Cell format:Fill: none
Custom number format: "Due in "#" days";;;@
Due in less than 90 days
Cell format:Fill: Orange
Custom number format: "Due in "#" days";;;@
Due today
Cell format:Fill: Red
Custom number format: ;;"Due today";@
Overdue
Cell format:Fill: Dark red
Text: White
Custom number format: ;"Overdue by "#" days";;@
You could combine the last two into this:
Due today or earlier
Cell format:Fill: Dark Red
Text: White
Custom number format: ;"Overdue by "#" days";"Due today";@
Explanation
This works by using the Custom Number format structure, which is:Format for positive number ; format for negative number ; format for zero ; format for text
See this page for more info: https://support.office.com/en-gb/art...2-09fab54be7f4
Hope that helps - attached is a file showing it working in column E.
Bookmarks