Help

The Community will be undergoing maintenance from Friday February 21 - Friday, February 28 and will be "read only" during this time. To learn more, check out our Announcements blog post.

If/And with Blanks and Not Blanks

Topic Labels: Formulas
Solved
Jump to Solution
142 2
cancel
Showing results for 
Search instead for 
Did you mean: 
AndreaR
6 - Interface Innovator
6 - Interface Innovator

Hi there. I am trying to write a formula ("Duration of Pending Repayment") that calculates how long an unpaid repayment is overdue.

Essentially, it will be:

If "Repayment Due Date" is not blank
And "Repayment Received Date" is blank
Then calculate Today-Repayment Due date. This part I got with DATETIME_DIFF(TODAY(),{Repayment Due Date},'days')

I cannot figure out how to do the If/And with blanks, not blanks. Any ideas, wizards? ðŸ¤“ Thank you in advance! 

 

PS - Here's how the formula currently reads:

IF(AND(NOT({Repayment Due Date})),{Invoice Issue Date},DATETIME_DIFF(TODAY(),{Repayment Due Date},'days'))

AndreaR_0-1739291281597.png

 

1 Solution

Accepted Solutions
kuovonne
18 - Pluto
18 - Pluto

You're close. Try this. I changed the field names from {Invoice Issue Date} to {Repayment Due Date}. I also shifted the NOT() to the field that should be blank. I also moved the closing parenthesis for the AND().

IF(
  AND(
    {Repayment Due Date},
    NOT({Repayment Received Date})
  ),
  DATETIME_DIFF(TODAY(),{Repayment Due Date},'days')
)

 

See Solution in Thread

2 Replies 2
kuovonne
18 - Pluto
18 - Pluto

You're close. Try this. I changed the field names from {Invoice Issue Date} to {Repayment Due Date}. I also shifted the NOT() to the field that should be blank. I also moved the closing parenthesis for the AND().

IF(
  AND(
    {Repayment Due Date},
    NOT({Repayment Received Date})
  ),
  DATETIME_DIFF(TODAY(),{Repayment Due Date},'days')
)

 

AndreaR
6 - Interface Innovator
6 - Interface Innovator

THANK YOU! That worked! I feel like I should ask for your autograph, you've been a huge help throughout the community threads! Have a great one! 🙂