Help

Help with IF and DateTime_Diff

Topic Labels: Formulas
Solved
Jump to Solution
505 4
cancel
Showing results for 
Search instead for 
Did you mean: 
Scott_Brasted
7 - App Architect
7 - App Architect
IF({Last Renewal},
DATETIME_DIFF(
TODAY(),
{Last Renewal},
'days'
) > 365,
"🔴",
"🟢"
)
 
The above formula, without the first {Last Renewal}, works fine. But I have records that are empty and I want them blank. I obviously am not constructing my syntax correctly. I want to say that if {Last Renewal} is not blank, then do the following. as I said it works if take out the first {Last Renewal}. I'm sure it is simple. I just don't see it.
 
Thanks, Scott
1 Solution

Accepted Solutions
jwag
6 - Interface Innovator
6 - Interface Innovator

I think you're missing a 2nd if (nested). Try the following:

IF(
{Last Renewal},
IF(
DATETIME_DIFF(
TODAY(),
{Last Renewal},
'days'
) > 365,
"🔴",
"🟢"
),
""
)

 

This says: if the renewal date is not blank, then check the date diff, otherwise if it's blank, return blank.

See Solution in Thread

4 Replies 4
Scott_Brasted
7 - App Architect
7 - App Architect

A little clarification, I get a green emoji in the blank record. All else works. This is the formula that works, but has a the green emoji in the blank record.

IF(
DATETIME_DIFF(
TODAY(),
{Payment Date},
'days'
) > 365,
"🔴",
"🟢"
)
 
Again thanks, Scott

Scott_Brasted_0-1698328055287.png

Screenshot of result of formula. Need Abernathy records be blank. Thanks ,Scott

jwag
6 - Interface Innovator
6 - Interface Innovator

I think you're missing a 2nd if (nested). Try the following:

IF(
{Last Renewal},
IF(
DATETIME_DIFF(
TODAY(),
{Last Renewal},
'days'
) > 365,
"🔴",
"🟢"
),
""
)

 

This says: if the renewal date is not blank, then check the date diff, otherwise if it's blank, return blank.

Scott_Brasted
7 - App Architect
7 - App Architect

Fantastic, Thanks! I was thinking that I probably needed another If statement. That worked perfectly. Best, Scott