Excel If statement

[roqalexander]

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 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?

Example: customer Invoice due date is 4/15/2021 and then if they paid their monthly installment on 4/14/2021 their due date changes to the next due date 5/15/2021 if the balance is above 0 dollars.

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.

 

[JimM]

Is the payment due date always the 15th of the month?
If not you might struggle unless you have an additional column for the day of month due
You'll also need an additional column for 'last payment received date'

 

[etaf]

i dont know quickbooks, is this an excel question ?
or a quickbooks question
In excel it would be possible
are the customers invoices always on the same date 15th
Other wise
you could add a day of month due, if you had an invoice day for example

maybe something like
=DATE(YEAR(B2),MONTH(B2)+1,DAY(A2))
Which looks at the date paid and adds a month tp that , and uses the day due
issue will be if paid on end of previous month - so if due on 15th of april
Paid on 30th march - will not work

Book3
	A	B	C
1	Invoice date	Date Paid	Date Due
2	15	4/14/21	5/15/21
Sheet1

Cell Formulas
Range	Formula
C2	C2	=DATE(YEAR(B2),MONTH(B2)+1,DAY(A2))

 

[roqalexander]

Is the payment due date always the 15th of the month?
If not you might struggle unless you have an additional column for the day of month due
You'll also need an additional column for 'last payment received date'

So no they change, it depends on when the service we provide is over

we perform the service which is a 4 week program and then invoice them. so the day may change but if I put any due date down can the next due date be calculated only if the customer pays for the month.

quick books feeds into an excel sheet and will automatically show a customer made a payment in the excel worksheet and their last payment date

 

[roqalexander]

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 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?

Example: customer Invoice due date is 4/15/2021 and then if they paid their monthly installment on 4/14/2021 their due date changes to the next due date 5/15/2021 if the balance is above 0 dollars.

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.

I forgot to add that this is for EXCEL. Quickbooks just feeds data to my excel worksheet.

additionally my quickbooks will automatically update their last payment date as well.

example: customer A paid today a column would show that their last payment is 4/22/2021

 

[roqalexander]

i dont know quickbooks, is this an excel question ?
or a quickbooks question
In excel it would be possible
are the customers invoices always on the same date 15th
Other wise
you could add a day of month due, if you had an invoice day for example

maybe something like
=DATE(YEAR(B2),MONTH(B2)+1,DAY(A2))
Which looks at the date paid and adds a month tp that , and uses the day due
issue will be if paid on end of previous month - so if due on 15th of april
Paid on 30th march - will not work

Book3
	A	B	C
1	Invoice date	Date Paid	Date Due
2	15	4/14/21	5/15/21
Sheet1

Cell Formulas
Range	Formula
C2	C2	=DATE(YEAR(B2),MONTH(B2)+1,DAY(A2))

I’m going to try this out and see how it goes

 

[etaf]

we perform the service which is a 4 week program and then invoice them. so the day may change but if I put any due date down can the next due date be calculated only if the customer pays for the month.

Yes,

I can see a few issues on just automating to next month based on last paid date

So all you get from quickbooks is the last payment date
Lets say a payment was Due on 2nd month
But they did not pay until 27th of a month
Do you know the next due date is in fact the 2nd of the next month
If so how does excel see that DAY

you can always add 4 weeks to a date = using 28days
Date( YEAR(), MONTH() , DAY() )

so if the date paid is in cell A1
then in b1
Date( YEAR(A1), MONTH(A1) , DAY(A1)+28 )
will add 28 days
Date( YEAR(A1), MONTH(A1)+1 , DAY(A1) )
will add 1 calendar month
Date( YEAR(A1), MONTH(A1)+1 , DAY(2) )
will make the date , 2nd of the next month

so you can do a lot of things automatically

what you dont want is
Paid on 28th , Due on 2nd and then the next due date be 28th - then the next month they pay on 5th of the next month , so can be a month in arrears