formulas

[Guest]

Hi can any one help me with a formula that will do the following " basically
accounts that will have a running days from invoiced date to payed date and
stop counting when Payd and add colour or some thing to indicate 40days past
50days past 60days past".So action can be taken.
Columns A2 and B2 are comp name & info C2 is Invoiced Date, D2 is $
Amount, E2 is Date Payed, F2 is Days past Eg: C2 08/10/07, D2 $10.00, E2
12/10/07. F2 4
("PS" Australian Dating dd/mm/yy) When I used =DAYS360(C2,E2) I do get
the right answer. But if the account has no Pd date E2 entered I get dribble
in F2. this is the problem If I need another column thats ok can any one help

 

[scooper]

go
dim a as single
dim c as integer
a=0.0
c=0
for t=1 to 1000
a=ActiveCell.Offset(1,0).Value
if a>0 then c=c+1
if c=10 then
ActiveCell.Offset(-1,0).Font=Red
endif
next

or something like that

scooper

 

[Sandy Mann]

Check out Conditional Formatting: (Fomat > Conditional formatting) and
select formula is:

Reference the cells that hols the number of elapsed days and select the
colour format that you want for the three conditions possible.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Guest]

Thanks for the reply but I dont understand I need precise instructions

 

[Guest]

Hi thanks Im ok with basic but need more instruction

 

[Sandy Mann]

Well you might have understood it better if I could spell/type better, <g>

Step by step: Highlight all the cells in which you want the colour to
appear in Columns A, B, C, D & E, (or just C if you only want colour in one
column).

Select Format > Conditional Formatting then, in the Conditional Formatting
dialog box, click on the arrowhead next to: "Cells value is" and select
"Formula is"

In the formula box to the right of "Formula is" enter:

=AND($C2<>"",$E2="",=AND($C2<>"",$E2="",$C2<TODAY()-60))

If you mean 60 days or more then change the formula to
=AND($C2<>"",$E2="",$C2<=TODAY()-60)

Click on the "Format" button and then select "Patterns" then select the
colour that you want the cell to change to for more than 60 days. Click OK
then click on the "Add" button and repeat the above but use the formula:

=AND($C2<>"",$E2="",$C2<TODAY()-50)

then select a difference colour for that condition and finally "Add" again
and use the formula:

=AND($C2<>"",$E2="",$C2<TODAY()-40)

with your third choice of colour.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk