Skip to main content
Solved

Only displaying postive numbers

  • November 23, 2023
  • 4 replies
  • 26 views
L
Forum|alt.badge.img+3

Hello,

   I am a commercial insurance agent and have create a pipeline report. I have a working formula that tells me how many days I have left before a policy needs to be renewed - 

DATETIME_DIFF((WORKDAY({Due Date},0)),TODAY(),'days')
 
is there a way to show positive and negative numbers in different colors?
Example
The Smith liability policy is up in 30 days when I enter it. So every passing day the countdown decrements. That works fine.
But I use the same date field for new policy so if Mr. Jones was a policy it was show a negative numbers on the second day since I use the current day when I first get the file.
The idea is to glance at the table and see what is most important.
Any feedback would be welcome. 
 

Best answer by Lautermilch

Or use emojis.

IF({Due Date},

  IF(
    DATETIME_DIFF((WORKDAY({Due Date},0)),TODAY(),'days')>=0,
    "🔵",
    "🔴"
  )&
  DATETIME_DIFF((WORKDAY({Due Date},0)),TODAY(),'days')
)

Thanks to both of you. I am on the free plan at this time and don't have colors or emojis. The formula worked but the cell showed the name of the emoji. I was on the paid plan for a while but $20 is a bit much for only one sheet that I use this for.

    4 replies

    Databaser
    Forum|alt.badge.img+25
    • Databaser
    • Brainy
    • November 23, 2023

    Not in the cell itself, but you could use conditional record coloring for this, based on your formula field.

    Jan - Databaser
    S
    Forum|alt.badge.img+21
    • Sho
    • Inspiring
    • November 24, 2023

    Or use emojis.

    IF({Due Date},

      IF(
        DATETIME_DIFF((WORKDAY({Due Date},0)),TODAY(),'days')>=0,
        "🔵",
        "🔴"
      )&
      DATETIME_DIFF((WORKDAY({Due Date},0)),TODAY(),'days')
    )
    Databaser
    L
    Forum|alt.badge.img+3
    • Lautermilch
    • Author
    • New Participant
    • Answer
    • November 24, 2023

    Or use emojis.

    IF({Due Date},

      IF(
        DATETIME_DIFF((WORKDAY({Due Date},0)),TODAY(),'days')>=0,
        "🔵",
        "🔴"
      )&
      DATETIME_DIFF((WORKDAY({Due Date},0)),TODAY(),'days')
    )

    Thanks to both of you. I am on the free plan at this time and don't have colors or emojis. The formula worked but the cell showed the name of the emoji. I was on the paid plan for a while but $20 is a bit much for only one sheet that I use this for.

    S
    Forum|alt.badge.img+21
    • Sho
    • Inspiring
    • November 24, 2023

    Thanks to both of you. I am on the free plan at this time and don't have colors or emojis. The formula worked but the cell showed the name of the emoji. I was on the paid plan for a while but $20 is a bit much for only one sheet that I use this for.


    Emojis would be available on the free plan.
    It is a copy-paste issue.
    Press Windows+. key and try inputting it again yourself.

    Terms & ConditionsAccessibility statement