# Small business owner needs help with excel formula

#### roqalexander

##### New Member
For context:

Right now I am a small business owner who uses quickbooks. my customers are set up with monthly payment plans that I manually keep track of. my business has grown so it’s hard to keep track of all these customers at this point.

SO the columns I have are:

customer name,
Amount paid last month
Date of most recent payment
Amount due this month,
invoice due date,
total outstanding balance,

i manually put in the invoice due dates but is there anyway to have them change to the next month automatically if a customer actually pays for the month?

JUST AN Example: Customer X’s Invoice due date is on 4/15/2021 and then IF for example they paid their monthly installment on 4/14/2021 their due date on excel CHANGES to the next due date 5/15/2021 as long as the balance is above 0 dollars aka not paid off.

have no idea if this could be done but boy it would save me a time of time on customer data.

many thanks in advance if this can be done. Like seriously.

### Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

#### Sulprobil

##### Board Regular
monitor customer payments filetype:xls
or for
accounts payable register filetype:xls

#### kweaver

##### Well-known Member
Without knowing more about the layout you have and/or want, something like this might get you started:

Book4
ABCDEFG
1Customer NameAmount Paid Last MonthDate Of Most Recent PaymentAmount Due This MonthInvoice Due DateTotal Outstanding BalanceNext Due Date
2AAAA\$1,000.004/14/2021\$1,000.004/15/20215/15/2021
3BBBB\$500.003/20/2021\$500.004/20/20214/20/2021
Sheet1
Cell Formulas
RangeFormula
G2:G3G2=IF(AND(MONTH(C2)=MONTH(E2),C2<=E2),E2+30,E2)

Replies
6
Views
181
Replies
15
Views
164
Replies
3
Views
299
Replies
1
Views
127
Replies
6
Views
376

1,148,367
Messages
5,746,283
Members
424,006
Latest member
Metal_warrior

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back