Skip to main content

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

 

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

 

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


Terms & Conditions