Skip to main content

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"

)

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!

Reply


Terms & Conditions