| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
formula HELP
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.misc
|
|||
|
|||
|
formula HELP
"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 Try this: =IF(E2="","",E2-C2) If you enter dates before the formula, Excel will format cell E2 as a date when you enter the formula. Just format it as a number. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
formula HELP
Use conditional formatting with a formula of
=AND($C2<"",$E2<"",$E2-$C2) -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "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.misc
|
|||
|
|||
|
formula HELP
Sorry didn't finish that. I meant conditions of
=AND($C2<"",$E2<"",$E2-$C2)60 =AND($C2<"",$E2<"",$E2-$C2)50 =AND($C2<"",$E2<"",$E2-$C2)40 in that order -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "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.misc
|
|||
|
|||
|
formula HELP
Hi thank you very much for the reply. I entered exact as you tyeped and i get
TRUE Im sorry but I think I need the info for dummies. I formated Column C to Date D to $ E to Date then in cell F2 I entered All one after the other What dont I under stand :( =IF(E2="","",E2-C2) =AND($C2<"",$E2<"",$E2-$C2)60 =AND($C2<"",$E2<"",$E2-$C2)50 =AND($C2<"",$E2<"",$E2-$C2)40 "Bob Phillips" wrote: Sorry didn't finish that. I meant conditions of =AND($C2<"",$E2<"",$E2-$C2)60 =AND($C2<"",$E2<"",$E2-$C2)50 =AND($C2<"",$E2<"",$E2-$C2)40 in that order -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "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.misc
|
|||
|
|||
|
formula HELP
I think Bob intended you to use the formula
=IF(E2="","",E2-C2) in cell F2, and the other three in conditional formatting for that cell. "krackerjack" wrote in message ... Hi thank you very much for the reply. I entered exact as you tyeped and i get TRUE Im sorry but I think I need the info for dummies. I formated Column C to Date D to $ E to Date then in cell F2 I entered All one after the other What dont I under stand :( =IF(E2="","",E2-C2) =AND($C2<"",$E2<"",$E2-$C2)60 =AND($C2<"",$E2<"",$E2-$C2)50 =AND($C2<"",$E2<"",$E2-$C2)40 "Bob Phillips" wrote: Sorry didn't finish that. I meant conditions of =AND($C2<"",$E2<"",$E2-$C2)60 =AND($C2<"",$E2<"",$E2-$C2)50 =AND($C2<"",$E2<"",$E2-$C2)40 in that order -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "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 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
formula HELP
No I didn't, as I read it he just wanted to know which were aged 60 days,
50, etc. so I thought he just wanted CF, and that would suffice. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Stephen" <none wrote in message ... I think Bob intended you to use the formula =IF(E2="","",E2-C2) in cell F2, and the other three in conditional formatting for that cell. "krackerjack" wrote in message ... Hi thank you very much for the reply. I entered exact as you tyeped and i get TRUE Im sorry but I think I need the info for dummies. I formated Column C to Date D to $ E to Date then in cell F2 I entered All one after the other What dont I under stand :( =IF(E2="","",E2-C2) =AND($C2<"",$E2<"",$E2-$C2)60 =AND($C2<"",$E2<"",$E2-$C2)50 =AND($C2<"",$E2<"",$E2-$C2)40 "Bob Phillips" wrote: Sorry didn't finish that. I meant conditions of =AND($C2<"",$E2<"",$E2-$C2)60 =AND($C2<"",$E2<"",$E2-$C2)50 =AND($C2<"",$E2<"",$E2-$C2)40 in that order -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "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 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
formula HELP
I meant these as Conditional Format formulae, see
http://www.contextures.com/xlCondFormat01.html -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "krackerjack" wrote in message ... Hi thank you very much for the reply. I entered exact as you tyeped and i get TRUE Im sorry but I think I need the info for dummies. I formated Column C to Date D to $ E to Date then in cell F2 I entered All one after the other What dont I under stand :( =IF(E2="","",E2-C2) =AND($C2<"",$E2<"",$E2-$C2)60 =AND($C2<"",$E2<"",$E2-$C2)50 =AND($C2<"",$E2<"",$E2-$C2)40 "Bob Phillips" wrote: Sorry didn't finish that. I meant conditions of =AND($C2<"",$E2<"",$E2-$C2)60 =AND($C2<"",$E2<"",$E2-$C2)50 =AND($C2<"",$E2<"",$E2-$C2)40 in that order -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "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 | |
|
|
Linear Mode
