Forum Discussion

timward10's avatar
timward10
Helper II
1 year ago
Solved

Formula help!

Hi, 

 

I have the below formula, that is erroring, this is only when I add in the reference to the contract end date. 

 

Am I missing a format add in to the formula? I wouldn't expect to see revenue in the November '25 and December'25 columns as the contract has ended.

 

Formula - 

Nov-25 = if(and([Product Name]="Optilite Special Protein Analyser",[Oct-25]<>blank()&&format([Optilite Analyser Revenue Date],"MMMM YYYY")<>"November 2025"),blank(),if(and([Product Name]="Optilite Special Protein Analyser",FORMAT([Optilite Analyser Revenue Date],"MMMM YYYY")="November 2025"),[Split],if([Contract End Date]<FORMAT([Revenue Start Date],"MMMM YYYY")="November 2025",BLANK(),if([Oct-25]<>blank(),[Oct-25],if(FORMAT([Revenue Start Date],"MMMM YYYY")="November 2025",[Split])))))
 
Screenshot

 

 

Thanks!

 

Need Help
  • Akash_Varuna's avatar
    Akash_Varuna
    1 year ago

    Hi timward10 It might be because you are trying to compare [Contract End Date] directly with FORMAT([Revenue Start Date], "MMMM YYYY"), which is causing a type mismatch error
    Try using DATE function

    Nov-25 = 
IF(
    AND(
        [Product Name] = "Optilite Special Protein Analyser",
        [Oct-25] <> BLANK(),
        FORMAT([Optilite Analyser Revenue Date], "MMMM YYYY") <> "November 2025"
    ),
    BLANK(),
    IF(
        AND(
            [Product Name] = "Optilite Special Protein Analyser",
            FORMAT([Optilite Analyser Revenue Date], "MMMM YYYY") = "November 2025"
        ),
        [Split],
        IF(
            AND(
                [Contract End Date] <> BLANK(),
                [Contract End Date] >= DATE(2025, 11, 1)  // Ensure proper date comparison
            ),
            IF(
                [Oct-25] <> BLANK(),
                [Oct-25],
                IF(
                    FORMAT([Revenue Start Date], "MMMM YYYY") = "November 2025",
                    [Split],
                    BLANK()
                )
            ),
            BLANK()
        )
    )
)

2 Replies

  • Akash_Varuna's avatar
    Akash_Varuna
    Super User
    1 year ago

    Hi timward10 It might be because you are trying to compare [Contract End Date] directly with FORMAT([Revenue Start Date], "MMMM YYYY"), which is causing a type mismatch error
    Try using DATE function

    Nov-25 = 
IF(
    AND(
        [Product Name] = "Optilite Special Protein Analyser",
        [Oct-25] <> BLANK(),
        FORMAT([Optilite Analyser Revenue Date], "MMMM YYYY") <> "November 2025"
    ),
    BLANK(),
    IF(
        AND(
            [Product Name] = "Optilite Special Protein Analyser",
            FORMAT([Optilite Analyser Revenue Date], "MMMM YYYY") = "November 2025"
        ),
        [Split],
        IF(
            AND(
                [Contract End Date] <> BLANK(),
                [Contract End Date] >= DATE(2025, 11, 1)  // Ensure proper date comparison
            ),
            IF(
                [Oct-25] <> BLANK(),
                [Oct-25],
                IF(
                    FORMAT([Revenue Start Date], "MMMM YYYY") = "November 2025",
                    [Split],
                    BLANK()
                )
            ),
            BLANK()
        )
    )
)