Software

Ask something
Discussion Locked

Excel 2000 question

0 helpful
5 replies

by Member 33712138

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

Right, here is what i want to do.

Ive currently got a excel sheet that details all the invoices ive sent out and their status. Each invoice occupies one row. I need to add a column that details how overdue a particular invoice is overdue. If its overdue, that is.

I presume the way to do it is to compare today's date and the date given in the 'pay by date' column and then detail the difference in the new column. I also need the new column to show the number of days overdue in red, but show nothing if the invoice has been paid (possibly by setting the text color to white ?). There is also a column devoted to the status of the invoice which will show 'invoiced' if unpaid and 'paid' if already paid.

I guess i need a formulae to sort all this out, but im pulling my hair out trying to do it

Can you help ??

5 replies

by brian

Let go of your hair. Click on the format menu and choose conditional formatting. It sounds like you will be able to take it from there. If not please post comment. Good Luck!

by dklippert

This may give you a start.
Assuming that:
Invoice number is in Column A
Due date is in B
Amount Invoiced is in C
Amount Paid is in D.
In column E place a formula similar to this one:

=IF(C2-D2=0,"Paid",IF(ISERROR(DATEDIF(B2,NOW(),"d")),"Invoiced","Past due "&DATEDIF(B2,NOW(),"d")&" days"))

(DATEDIF is available when the Analysis Toolpak Add-in is selected under Tool>Add-ins)

Highlight the cells in E and go to Format>Conditional Formatting and in the criteria box use --

Formulais:

=AND(C2-D2<>0,NOT(ISERROR(DATEDIF(B2,TODAY(),"d"))))

This discussion is closed to new replies.