formulas
 

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

formulas
 

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 a0 then c=c+1
if c=10 then
ActiveCell.Offset(-1,0).Font=Red
endif
next

or something like that

scooper
"krackerjack" wrote in message
...
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

formulas
 

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


Replace @mailinator.com with @tiscali.co.uk


"krackerjack" wrote in message
...
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

formulas
 

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

"scooper" wrote:

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 a0 then c=c+1
if c=10 then
ActiveCell.Offset(-1,0).Font=Red
endif
next

or something like that

scooper
"krackerjack" wrote in message
...
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

formulas
 

Hi thanks Im ok with basic but need more instruction

"Sandy Mann" wrote:

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


Replace @mailinator.com with @tiscali.co.uk


"krackerjack" wrote in message
...
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

formulas
 

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


Replace @mailinator.com with @tiscali.co.uk


"krackerjack" wrote in message
...
Hi thanks Im ok with basic but need more instruction

"Sandy Mann" wrote:

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


Replace @mailinator.com with @tiscali.co.uk


"krackerjack" wrote in message
...
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