If/And with Blanks and Not Blanks

Question

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

kuovonne · Accepted Answer

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 · Answer

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! &#128578;

Sign up

Login to the community

Scanning file for viruses.

This file cannot be downloaded