Formula help!

[timward10]

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!

[timward10]

Perfect! That has worked @Akash_Varuna 

Thank you!

[Akash_Varuna · Accepted solution]

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()
        )
    )
)