Help

Re: Licence status - if formula

594 0
cancel
Showing results for 
Search instead for 
Did you mean: 
Matt_Banks
4 - Data Explorer
4 - Data Explorer

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

1 Reply 1
Zollie
10 - Mercury
10 - Mercury

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.