IF Formula for determining whether a bill is overdue

Hi everyone, I did my best with this using just the Help pages but I can’t seem to get this formula right.

Basically, I have a spreadsheet where I have listed my credit card statements when they come in, including the date that the bill is due. Then I have a field where I state whether the bill is “Paid (in full),” “Paid (partially),” or “Unpaid.” Lastly, I have a formula field to determine whether the bill is overdue.

This is the formula I have so far:

IF(AND(DATETIME_DIFF(TODAY(),{Balance Due Date},‘days’>0),OR(Status=“Unpaid”,Status=“Paid (partially)”)),“Overdue”)

What I’m trying say with this formula is: If today’s date is later than the balance due date, and the bill is only Unpaid or Paid (partially), then I want it to spit out “Overdue.” However, with this formula, it ignores the DATETIME_DIFF command and always spits out “Overdue” even if the balance due date hasn’t passed yet.

Is there a way to fix this?

The >0 should be outside the DATETIME_DIFF() function:

IF(
   AND(
      DATETIME_DIFF(TODAY(),{Balance Due Date},'days')>0,
      OR(Status="Unpaid",Status="Paid (partially)")
   ),
   "Overdue"
)

You also could simplify that formula a bit:

IF(
   AND(
      TODAY() > {Balance Due Date},
      OR(Status="Unpaid",Status="Paid (partially)")
   ),
   "Overdue"
)
2 Likes

Ah, I see! I’m amused at myself for missing this. Thanks so much!

This topic was solved and automatically closed 15 days after the last reply. New replies are no longer allowed.