0

I am trying to create a formula based on some timestamp fields that will return a number. The error I am getting is "Syntax Error" without anymore details. Below is the formula I have entered.

I went off this article to get this code because it is exactly what I am looking for - https://opfocus.com/blog/how-to-accurately-track-salesforce-stage-changes/

IF( ISBLANK( PQ_Timestamp_v2__c ), NULL,
IF( NOT(ISBLANK( CFU_Timestamp_v2__c)) , CFU_Timestamp_v2__c – PQ_Timestamp_v2__c,
IF( NOT(ISBLANK( RFA_Timestamp__c )) , RFA_Timestamp__c – PQ_Timestamp_v2__c,
IF( NOT(ISBLANK( Ready_for_ETRAN_Timestamp_v2__c )) , Ready_for_ETRAN_Timestamp_v2__c – PQ_Timestamp_v2__c,
IF( NOT(ISBLANK( Audit_Timestamp__c )) , Audit_Timestamp__c – PQ_Timestamp_v2__c,
IF( NOT(ISBLANK( Bank_Handoff_Timestamp_v2__c )) , Bank_Handoff_Timestamp_v2__c – PQ_Timestamp_v2__c,
IF( NOT(ISBLANK( Declined_Timestamp_v2__c )) , Declined_Timestamp_v2__c – PQ_Timestamp_v2__c,
IF( NOT(ISBLANK( Withdrawn_Timestamp_v2__c )) , Withdrawn_Timestamp_v2__c – PQ_Timestamp_v2__c,
IF( IsClosed, Closed_Date__c – PQ_Date__c,
now() – PQ_Timestamp_v2__c )))))))))
1
  • If you can accept using a Report for this analysis, then consider using a “Opportunity History” report and manipulate its “From Stage”, “To Stage”, and “Stage Duration” within the filters, and groupings within a Summary Report Dec 22, 2021 at 17:12

2 Answers 2

5

The issue is your minus sign character (-).

I presume you copied this formula from that site and then replaced the fields. It seems the minus sign (-) is actually an en dash instead of a hyphen-minus which is causing the generic issue. You can copy and paste from that article or your formula into a ASCII Text to Hex Code Converter and see the difference when compared to typing on your keyboard.

Source What it looks like Hex Character
Your linked article & this question 2013 en dash
Keyboard - 2D hyphen-minus

I suspected this after I had copied your formula exactly as is and received the same generic error when I should've received an error: field xxxx doesn't exist.

Reproduced this in my org by doing the following:

  1. Copied your formula as is from this question
  2. Replaced all fields with fields that actually exist in my org
  3. Got the same generic error
  4. Removed all en dashes (-) and re-typed them manually with my keyboard within the editor
  5. Validated and got a successful compile
1
  • that was it!! thank you so much!
    – Jenna Odom
    Dec 23, 2021 at 14:46
0

Everything looks correct to me, but I went ahead and refactored this to see if this version might work:

IF(IsClosed,
    Closed_Date__c – PQ_Date__c,
    BLANKVALUE(
        CFU_Timestamp_v2__c,
        BLANKVALUE(
            RFA_Timestamp__c,
            BLANKVALUE(
                Ready_for_ETRAN_Timestamp_v2__c,
                BLANKVALUE(
                    Audit_Timestamp__c,
                    BLANKVALUE(
                        Bank_Handoff_Timestamp_v2__c,
                        BLANKVALUE(
                            Declined_Timestamp_v2__c,
                            BLANKVALUE(
                                Withdrawn_Timestamp_v2__c,
                                now()
                            )
                        )
                    )
                )
            )
        )
    ) - PQ_Timestamp_v2__c
)

It's not exactly the same as yours, but I think this might be more accurate?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.