Date calculations

[BrettPotts]

Hi,

I have a 5 colum spreadsheet i need some advice with.

Colum A - Is the date account sent
Colum B - Is the date account due by
Colum c - Is the date account payment is recieved
Colum d - Is the difference between A & C

In Colum E i need a rule to highligh a cell once it has past its overdue
date which is C .. I am more than happy to email the file over for you to
have a play as i am really stumpted! My email addy is (e-mail address removed)

Hope you can help!

 

[Joel]

This is the formula I used

=IF(AND(NOW()>B8,C8=""),"Over Due","")

I assume if the payment is made late You don't want want anything displayed.
You just asked if it is overdue.

The formula below displays Overdue if the paymentt wasn't made and Late if
the payment was made Late

=IF(AND(NOW()>B8,C8=""),"Overdue",IF(C8>B8,"Late",""))

 

[BrettPotts]

Thanks Joel - the only issue i have now is when the payment is made on the
day of invoice that is doesnt show as paid or overdue??

Brett

 

[Joel]

This should work. If leaves a blank if nothing is paid before the due date.
what do you want done if it is only a partial payment?

=IF(AND(NOW()>B8,C8=""),"Overdue",IF(C8>B8,"Late",if(C8<>"","Paid",""))

 

[BrettPotts]

This formula worked a treat - Thanks Joel. How are you with SORTINg of data.
Basically want to add a button that when you click it will sort data in data
order as i often add a row here or there and sometimes my spreadsheet doesnt
flow in date order and i than need to manually sort it - is there a way it
can happen with one click?

 

[Joel]

Try this code. It will sort every column on the worksheet

SortCol = "A"
ActiveSheet.Cells.Sort _
Key1:=Range(SortCol & 1), _
Order1:=xlAscending, _
Header:=xlGuess