Licence status - if formula

[Matt_Banks]

Hi,

I am currently trying to use an if formula to display emojis to highlight the status of a license (date field). I currently have:-

IF(IS_AFTER(TODAY(), {END DATE}), “ :stop_sign: EXPIRED”, IF(IS_SAME(TODAY(), {END DATE}), “ :calendar: EXPIRES TODAY”, IF(IS_AFTER(TODAY(), DATEADD({END DATE}, -1, ‘week’)), “ :warning: EXPIRES SOON”)))

The formula is working fine but I wanted to add ELSE display “ :heavy_check_mark: ACTIVE” but can’t seem to figure it out.

Thanks

[Zollie]

You just needed to add to the end the last IF block. It’s a little easier to understand what’s going on if you edit while indenting (just make sure you take the indentations out before running it).

IF(
    IS_AFTER(TODAY(), {END DATE}),
    “:stop_sign: EXPIRED”,
    IF(
        IS_SAME(TODAY(),{END DATE}),
        “:calendar: EXPIRES TODAY”,
        IF(
            IS_AFTER(TODAY(),DATEADD({END DATE}, -1, ‘week’)),
            “:warning: EXPIRES SOON”),
            ":heavy_check_mark: ACTIVE"
        )
    )
)

Explanation

Each IF block works like this:

IF(LOGIC, what happens if true, what happens if false)

Since you’re nesting IF blocks, ‘what happens if false’ has been routing you to the next IF blocks. But with the last (or most nested) IF block, you hadn’t provided a false return value.