General discussion

Locked

Excel 2000 question

By Simon@SRN ·
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 ??

This conversation is currently closed to new comments.

5 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

Excel 2000 question

by brian In reply to Excel 2000 question

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!

Collapse -

Excel 2000 question

by Simon@SRN In reply to Excel 2000 question

Poster rated this answer

Collapse -

Excel 2000 question

by DKlippert In reply to Excel 2000 question

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"))))

Collapse -

Excel 2000 question

by Simon@SRN In reply to Excel 2000 question

Poster rated this answer

Collapse -

Excel 2000 question

by Simon@SRN In reply to Excel 2000 question

This question was closed by the author

Back to Software Forum
5 total posts (Page 1 of 1)  

Related Discussions

Related Forums