0

I am trying to create a Google sheet to track payments for a team. I want a cell to turn red if a value hasn't been entered by the 1st of the month. I have 2 other formats already set up to change to green or yellow depending on full payment or partial payment.

1

If a payment has not been received on an transaction by the first day of the following month, you want the payment cell to appear red.

Assume:

  • Column A = Transaction date
  • Column B = Transaction value
  • Column C = Amount received.

Use this formula as a "custom formula" for Column C in Conditional formatting. =and(isblank(C2),today()>EOMONTH(A2,0)+1)

Explanation:

  • AND() Returns TRUE, if both expressions are true
  • isblank() - checks that no payment has been received; returns TRUE/FALSE
  • today()>EOMONTH(A2,0)+1) - is todays date grater than the first day of the month following the transaction date. TRUE/FALSE

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.