0

I am trying to create a formula that will display various emojis (red, yellow and green) depending of the time a lead spent into various stages. For example, if the lead is in the stage "assigned" since more than 10 days, the formula should display a red (or yellow it does really matter for my question here).

I created this formula:

IF(
  AND(
    NOT(ISNULL(Nurturing_End_Date__c)), 
    ISPICKVAL(Status, "Nurturing"),
    DATETIMEVALUE(Nurturing_End_Date__c) <= NOW()
  ), 
  "🟥",
  IF(
    AND(
      NOT(ISNULL(Tracking_Assigned_Date__c)),
      ISPICKVAL(Status, "Assigned"),
      DATETIMEVALUE(Tracking_Assigned_Date__c) <= NOW() - (7 * 24 * 60 * 60 * 1000)
    ), 
    "🟥",
    IF(
      AND(
        NOT(ISNULL(Tracking_Assigned_Date__c)),
        ISPICKVAL(Status, "Assigned"),
        DATETIMEVALUE(Tracking_Assigned_Date__c) <= NOW()
      ),
      "🟧",
      IF(
        AND(
          NOT(ISNULL(Tracking_Contacted_Date__c)),
          ISPICKVAL(Status, "Contacted"),
          DATETIMEVALUE(Tracking_Contacted_Date__c) <= NOW() - (7 * 24 * 60 * 60 * 1000)
        ), 
        "🟥",
        IF(
          AND(
            NOT(ISNULL(Tracking_Contacted_Date__c)),
            ISPICKVAL(Status, "Contacted"),
            DATETIMEVALUE(Tracking_Contacted_Date__c) <= NOW()
          ), 
          "🟧",
          IF(
            AND(
              NOT(ISNULL(Tracking_Qualification_R1_Date__c)),
              ISPICKVAL(Status, "Qualification_R1_scheduled"),
              DATETIMEVALUE(Tracking_Qualification_R1_Date__c) <= NOW() - (7 * 24 * 60 * 60 * 1000)
            ),
            "🟥",
            IF(
              AND(
                NOT(ISNULL(Tracking_Qualification_R1_Date__c)),
                ISPICKVAL(Status, "Qualification_R1_scheduled"),
                DATETIMEVALUE(Tracking_Qualification_R1_Date__c) <= NOW()
              ),
              "🟧",
              IF(
                AND(
                  NOT(ISNULL(Tracking_Qualified_R2_Date__c)),
                  ISPICKVAL(Status, "Qualified_R2_scheduled"),
                  DATETIMEVALUE(Tracking_Qualified_R2_Date__c) <= NOW() - (7 * 24 * 60 * 60 * 1000)
                ),
                "🟥",
                IF(
                  AND(
                    NOT(ISNULL(Tracking_Qualified_R2_Date__c)),
                    ISPICKVAL(Status, "Qualified_R2_scheduled"),
                    DATETIMEVALUE(Tracking_Qualified_R2_Date__c) <= NOW()
                  ),
                  "🟧",
                  "🟩"
                )
              )
            )
          )
        )
      )
    )
  )
)

The issue is that the formula show no error but when I populate specific values in the corresponding fields (for example Tracking_Assigned_Date__c), the layout of the lead start bugging showing this error: ui.services.connection.api.PartnerConnectionException: INVALID_DATE_FORMAT: Invalid DateTime enter image description here(see screen cap).

I tried to understand the issue myself but as the formula looks fine to me, I don't get the error message.

Thanks for your help

3
  • Maybe I forgot to mention that the field I am referring to are "date time" fields (all of them Sep 4, 2023 at 17:21
  • start with a simpler formula, get it to work, then add each additional if/else clause
    – cropredy
    Sep 4, 2023 at 18:35
  • 1
    The first thing that strikes me as odd is why are you using DATETIMEVALUE() if the fields are already of type Datetime? That function is generally used to convert something of type String into a Datetime.
    – Derek F
    Sep 4, 2023 at 19:00

0

You must log in to answer this question.

Browse other questions tagged .