Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
CELLS NOT CALC FORMULAS - VALUES STAY SME FORMULAS CORRECT?? HELP | Excel Worksheet Functions | |||
lookup formulas dependent upon lookup formulas | Excel Worksheet Functions | |||
automatically copy formulas down columns or copy formulas all the | Excel Worksheet Functions | |||
Formulas not evaluated, Formulas treated as strings | Excel Discussion (Misc queries) | |||
formulas for changing formulas? | Excel Discussion (Misc queries) |