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