0

I need to make a formula field work, unfortunately, I cannot figure out how to place statements so that I can include 2 False options (False options have been bolded)

The logic behind the formula:

If Account Stage = Extended Onboarding and MS 1 Date is blank calculate Days = Extended timestamp - Kickoff Date,

If Account Stage = Ongoing and Extended Onboarding Days are blank and MS 1 Date is blank calculate days = Ongoing timestamp - Kickoff Date

If neither of the timestamps are there and MS 1 Date is blank use =Today - Kickoff Date

But then if MS 1 Date is not blank :

If Account Stage = Extended Onboarding calculate Days = Extended timestamp - MS1 Date

If Account Stage = Ongoing, and Extended Onboarding Days are blank calculate days = Ongoing timestamp - MS1 Date

And again if neither of timestampsare there use = Today - MS1 Date

IF( TEXT( Onboarding_Stages__c)= "Extended Onboarding" &&

ISBLANK(MS1_Date_min_rollup__c),

DATEVALUE (Extended_Implementation_Timestamp__c) - Kickoff_Date__c,

IF( TEXT( Onboarding_Stages__c)= "On Going" && ISBLANK( Days_in_Extended_Onboarding__c) && ISBLANK(MS1_Date_min_rollup__c),

DATEVALUE(Ongoing_Timestamp__c) - Kickoff_Date__c ,

Today () - Kickoff_Date__c,

IF( TEXT( Onboarding_Stages__c)= "Extended Onboarding",

DATEVALUE(Extended_Implementation_Timestamp__c) - MS1_Date_min_rollup__c,

IF( TEXT( Onboarding_Stages__c)= "On Going" && ISBLANK( Days_in_Extended_Onboarding__c),

DATEVALUE(Ongoing_Timestamp__c) - MS1_Date_min_rollup__c ,

Today () - MS1_Date_min_rollup__c))

Improve this question
3
  • Please can you explain the full logic you are trying to support as a description of the requirement while retaining the current formula to show what you've tried, then add detail of just what isn't working correctly. That will give the community the detail they need in order to understand your problem and help you resolve it.
    – Phil W
    Commented Mar 26, 2021 at 11:00
  • I hope my explenation makes it more clear :)
    – kikalz
    Commented Mar 26, 2021 at 11:14
  • these problems are best broken down into component formula fields that can be individually tested
    – cropredy
    Commented Mar 27, 2021 at 3:31

1 Answer 1

Reset to default
0

This was more than a little harder to read.

From what I understand from your text (not the formula), you have a formula field calculating difference between two dates. The dates vary based on the Onboarding_Stage__c and other timestamp values.

You mentioned two false statements but if it is like the condition has to be true to return one value and false to return the other value, you can put in nested IF() to achieve this goal.

As per my understanding so far, I have the below formula written which you can change or add conditions going down to the inner most value_if_false parameter in IF() blocks as required. I am also assuming Onboarding_Stages__c is a picklist field and hence used ISPICKVAL() instead of TEXT(), you can switch back to TEXT() if the field isn't a picklist field.

    IF(
        ISPICKVAL(Onboarding_Stages__c, "Extended Onboarding") && ISBLANK(MS1_Date_min_rollup__c),
        DATEVALUE(Extended_Implementation_Timestamp__c) - Kickoff_Date_c,
        IF(
            ISPICKVAL(Onboarding_Stages__c, "Ongoing") && ISBLANK(Days_in_Extended_Onboarding__c) && ISBLANK(MS1_Date_min_rollup__c),
            DATEVALUE(Ongoing_Timestamp__c) - Kickoff_Date__c,
            IF(
                ISBLANK(Extended_Implementation_Timestamp__c) && ISBLANK(Ongoing_Timestamp__c) && ISBLANK(MS1_Date_min_rollup__c),
                TODAY() - Kickoff_Date__c,
                IF(
                    NOT(ISBLANK(MS1_Date_min_rollup__c) && ISPICKVAL(Onboarding_Stages__c, "Extended Onboarding)),
                    DATEVALUE(Extended_Implementation_Timestamp__c) - MS1_Date_min_rollup__c,
                    IF(
                        NOT(ISBLANK(MS1_Date_min_rollup__c) && ISPICKVAL(Onboarding_Stages__c, "Ongoing") && ISBLANK(Days_in_Extended_Onboarding__c)),
                        DATEVALUE(Ongoing_Timestamp__c) - MS1_Date_min_rollup__c;
                        IF(
                            ISBLANK(Extended_Implementation_Timestamp__c) && ISBLANK(Ongoing_Timestamp__c),
                            TODAY() - MS1_Date_min_rollup__c,
                            0
                        )
                    )
                )
            )
        )
    )
Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .