Excel - Automatic Alert for Bill Non Payment

  ihbuk1 15:01 29 Apr 2004
Locked

I have a spreadsheet with names of people, another column that states when the bill was sent out, another column of when the bill was paid. However I want an automatic alert that will highlight the people who have not paid say a month after the first bill was issued. i.e if the bill paid column is blank 1 month after the bill date of issue it will flag up the relevant person.
Thanks for any help.

  pj123 15:44 29 Apr 2004

An IF statement in the non payment column something like =if(c3<>0,"","Not Paid") could work, you can put whatever text you like between the second set of " marks. But surely if there is no date in Date Paid column that should be a sign? Or have I got what you are trying to do wrong?

  ihbuk1 15:52 29 Apr 2004

I was wondering if there was any way that you can Excel to show all non payments at one go either through a linked workbook or some kind of alert.

The spreadshhet is quite lengthy and there are workshhets for each month so it is kind of difficult to track.

  pj123 16:27 29 Apr 2004

Yes, I can see what you are aiming for now. I have just made up a small spreadsheet on your lines and although an IF statement would work it is not the best answer. I have just tried writing a macro but that is not much good either because macros tend to cover the whole thing which means that the invoice date has to be the same in every case. Hang on in there, someone will come up the answer. Sorry. Maybe a LOOKUP function would work.

  ihbuk1 16:32 29 Apr 2004

for all your help anyway mate - much appreciated.

  daba 18:41 29 Apr 2004

=if(now()-A1>30,"Overdue","")

where cell A1 is the Invoice Date.

  Simsy 06:52 30 Apr 2004

I suspect would suit you....

I constantly have trouble writing formulaes for these.... I'm working on it for you now! When I've got it sorted, I'll post back.

Regards,

Simsy

  Forum Editor 07:09 30 Apr 2004

to see this when you have it cracked, Simsy. I'm looking for something like it myself, although for a slightly different purpose.

I manage large numbers of Internet domain names and hosting packages for clients - all via an Excel file. I want to be warned when one of the expiry dates is imminent, so I can bill the client at the right moment. I'm working with two columns in this case - one for a name expiry date and another one for site hosting. Can I get a warning at a specified period in advance of the expiry date I've entered in a cell?

  Tog 07:22 30 Apr 2004

If a comparison of the current date and the date the bill was sent out is greater than your time limit AND the Bill Paid cell is blank then set the cell value in a new "Not Paid" column to "Not Paid" in red. Set an autofilter on the Not Paid column and when you want to look at the overdue accounts, set the filter to show non-blanks.

  Simsy 07:41 30 Apr 2004

Note there may be a better way of doing this... as I said, I always have trouble using formulae in conditional formats....

For this to work you need to have a cell containing "todays" date. Lets designate cell A1 for this... enter into cell A1 the formula,

=Today()

I am assuming that the following columns exist;

A = Name

B = Invoice date

C = Due date, (with the formula = B + 30)

D = Paid date

Select the first cell in the "Paid date" column, (I'm assuming row 2), then via the menu bar choose Format>conditional formatting...

In the dialogue that appears, on the left hand side click the dropdown and choose "formula is...", and enter the following formula;

=AND($A$1>C2,D2=0) (dont forget the = sign. If you do, it puts one in automatically, but includes spurious "" which mucks it up!!)

Click the format button, choose the format you want when payment has not been made, and OK your way out.

Because the $ symbol has been used it fixes the position of the cell with todays date in. You can now copy the formatting down that column.



A breakdown of the formula is;

(You need the formula to give a "True" from only a True or False possibility...)

AND... all the conditions in the brackets, (which are seperated by a comma), must be met for a TRUE response to be the answer. If any of the conditions are not met then the answer is FALSE

$A$1>C2 The value of cell A1, (Fixed), i.e. Todays date, is greater than the date in the "Due date" cell... i.e. the due date has passed.

D2=0 The "Paid date" cell is empty


I hope this helps. You may need to further tinker with formatting of cells, and perhaps use cell validation, to prevent errors.

(I hope I've got this all right!),

Regards,

Simsy

  Simsy 07:43 30 Apr 2004

So I can't check on exactly what I've done at home. I use this principle to highlight when the interest free period ends on a number of Credit cards I'm manipulating!

Good luck all!

Regards,

Simsy

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

OnePlus 5 review

See the work of famous artists playing with toys

iPad Pro 10.5in (2017) review

Comment faire une capture d’écran sur un Mac ?