Small business owner needs help with excel formula

roqalexander

New Member
Joined
Jun 18, 2013
Messages
8
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

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

Sulprobil

Board Regular
Joined
May 12, 2020
Messages
154
I suggest to google for
monitor customer payments filetype:xls
or for
accounts payable register filetype:xls
You might want to aim for something which helps you to download your bank statements and to link this to the above.
 

kweaver

Well-known Member
Joined
May 8, 2018
Messages
1,984
Office Version
  1. 365
  2. 2010
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)
 

Watch MrExcel Video

Forum statistics

Threads
1,132,646
Messages
5,654,565
Members
418,140
Latest member
ahepple86

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top