- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎Feb 11, 2025 08:31 AM - edited ‎Feb 11, 2025 08:32 AM
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:
Solved! Go to Solution.
Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎Feb 11, 2025 09:54 AM
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')
)

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎Feb 11, 2025 09:54 AM
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')
)
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎Feb 12, 2025 06:37 AM
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! 🙂
