Let's say I have two tables, one with the current date (but where I change it manually.), and the other one with a due date.

The current date is 01/01/19, and the due date is set to one day past the current date, so the due date is 01/02/19, but when the current date is now 01/02/19, I still want the due date to show one day past the current date, so the due date shows 01/03/19, but I want the due date to count that previous due date, which was 01/02/19, unless a true statement is set on a different table, without affecting the due date for 01/03/19.


Day 1:

Current Date = 01/01/19

Due Date = 01/02/19 (0 Payments Left)

Payment made for 01/02/19 = False

Day 2:

Current Date = 01/02/19

Due Date = 01/03/19 (1 Payment Left)

Payment made for 01/02/19 = False

Payment made of 01/03/19 = False

Day 3:

Current Date = 01/03/19

Due Date = 01/04/19 (0 Payments Left)

Payment made for 01/02/19 = True

Payment made for 01/03/19 = True

Payment made for 01/04/19 = False

If this doesn't make sense, then please comment and I will try to make it clearer.

Note 1: I came here and asked this question because I want the formula to track the payments and the payment’s due date and the current date, but I can’t do “Current Date + 1” because then the due date is set to that, so when I manually make it the next day, the due date is “Current Date + 1”, so the due date also goes up a day.

  • A bit unclear, yes. In each of your example three days, the Due Date is merely Current Date + 1. Is "(1 Payment Left)" literally part of the output, and that's what you want calculated? Please edit the question to clearly show what actually appears in your file vs. notes you're adding for us, and to show what thing(s) you want to be automatic. – Joel Reid Mar 19 '19 at 18:24
  • Yes, I want the due date to be the Current Date + 1, but if the payment wasn’t made, it logs it so that it’s payable later, but also a way to tell the formula that you payed the payment. – Chris Mar 20 '19 at 15:43

Please consider revising your question to contain a very clear explanation of what you've tried, what didn't work in your best attempt(s), your tables' names, your label cells, your data cells' addresses and contents, which values are entered manually, and which you'd like to be automatic. Using my best guesses:

  • You said Current Date is manually entered. Lets say in Current Date Table, B1.
  • It seems that in your due date table, the actual Due Date is always just that other table's "current date" plus one day, so use a formula like
    Current Date Table::B2 + 1
  • The dates seem to have no bearing on your "Payments Left" tally, so perhaps just count the "Payment Made for dd/mm/yy" value cells containing FALSE, and subtract one. For example:
    COUNTIF(Payments By Date Table::B,FALSE)-1
    or to add on the parentheses and Payments Left text:
    "(" & COUNTIF(Payments By Date Table::B,FALSE)-1 & " Payments Left)

screenshot of above formulas working as described

| improve this answer | |
  • This answers most of my question, I want this, but the formula to count the cash for the payment if the payment wasn’t made. Let’s say I owe $50 a day, if I paid it, I want it to say $0 due, but if I don’t pay and I owe $50, it shows $100 the next day, if not paid again, the next day shows $150. – Chris Mar 26 '19 at 21:24
  • You're breaking some spreadsheet normalization concepts. Try a couple tutorials or videos on using spreadsheets to track a budget or record payments. That aside, none of those requirements were in your question. This "comments" area is not for discussion nor problem-solving. You should edit your question or write a new one with the complete current situation and your entire goal, all explained clearly. Good luck with your project. – Joel Reid Mar 31 '19 at 12:57

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .