Help

IF Formula for determining whether a bill is overdue

Topic Labels: Formulas
841 2
cancel
Showing results for 
Search instead for 
Did you mean: 
Laurel_Meng
4 - Data Explorer
4 - Data Explorer

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?

2 Replies 2

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

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