Skip to main content
Solved

If/And with Blanks and Not Blanks

A

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'))

 

Best answer by kuovonne

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')
)

 

View original
Did this topic help you find an answer to your question?

2 replies

kuovonne
Forum|alt.badge.img+17
  • kuovonne
  • Brainy
  • 5987 replies
  • Answer
  • February 11, 2025

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')
)

 

A
1 person likes this
  • A
    AndreaR
A
  • AndreaR
  • Author
  • Inspiring
  • 10 replies
  • February 12, 2025

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! 🙂

Reply


Most helpful members this week

Terms & Conditions