|
activity duration
I have data in following format in excel
IDATE PNAME STATUS RL SDATE DIFF 13/08/2006 HOC AT Y 13/08/2006 13/08/2006 HOC AG Y 23/08/2006 13/08/2006 HOC AG H 24/08/2006 13/08/2006 HOC UP Y 27/08/2006 13/08/2006 HOC FILE H 30/08/2006 SUM HOC-STAT I m not good in macro therefore looking for help. Actually what i m trying to do that where the PNAME is same in above data it should subtract SDATE-IDATE and depict the value in DIFF col (days). secondly it should have running difference for PNAME for each row. and count the bigger value if it encountered STATUS and PNAME is same however SDATE is different. Last sum DIFF for each row and shows in next row called sum HOC-STAT. I have 1800 PNAME to do this plz help. jd |
activity duration
Why a macro? A formula should do it
DIFF: =E2-A2 Running Diff: =IF(A2=A1,F1+F2,"") Not too clear on the rest of what you want, an example might help. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "-jawad" wrote in message oups.com... I have data in following format in excel IDATE PNAME STATUS RL SDATE DIFF 13/08/2006 HOC AT Y 13/08/2006 13/08/2006 HOC AG Y 23/08/2006 13/08/2006 HOC AG H 24/08/2006 13/08/2006 HOC UP Y 27/08/2006 13/08/2006 HOC FILE H 30/08/2006 SUM HOC-STAT I m not good in macro therefore looking for help. Actually what i m trying to do that where the PNAME is same in above data it should subtract SDATE-IDATE and depict the value in DIFF col (days). secondly it should have running difference for PNAME for each row. and count the bigger value if it encountered STATUS and PNAME is same however SDATE is different. Last sum DIFF for each row and shows in next row called sum HOC-STAT. I have 1800 PNAME to do this plz help. jd |
activity duration
On Aug 26, 2:10 pm, "Bob Phillips" wrote:
Why a macro? A formula should do it DIFF: =E2-A2 Running Diff: =IF(A2=A1,F1+F2,"") Not too clear on the rest of what you want, an example might help. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "-jawad" wrote in message oups.com... I have data in following format in excel IDATE PNAME STATUS RL SDATE DIFF 13/08/2006 HOC AT Y 13/08/2006 13/08/2006 HOC AG Y 23/08/2006 13/08/2006 HOC AG H 24/08/2006 13/08/2006 HOC UP Y 27/08/2006 13/08/2006 HOC FILE H 30/08/2006 SUM HOC-STAT I m not good in macro therefore looking for help. Actually what i m trying to do that where the PNAME is same in above data it should subtract SDATE-IDATE and depict the value in DIFF col (days). secondly it should have running difference for PNAME for each row. and count the bigger value if it encountered STATUS and PNAME is same however SDATE is different. Last sum DIFF for each row and shows in next row called sum HOC-STAT. I have 1800 PNAME to do this plz help. jd- Hide quoted text - - Show quoted text - I have following attributes IDATE PNAME STATUS RL SDATE DIFF a little details data is as under IDATE PNAME STATUS RL SDATE DIFF 13/08/2006 HOC AT Y 14/08/2006 13/08/2006 HOC AG Y 23/08/2006 13/08/2006 HOC AG H 24/08/2006 13/08/2006 HOC UP Y 27/08/2006 13/08/2006 HOC FILE H 30/08/2006 14/08/2006 IASS AT H 15/08/2006 14/08/2006 IASS AG H 19/08/2006 14/08/2006 IASS UP H 22/08/2006 14/08/2006 IASS WDT H 23/08/2006 14/08/2006 IASS FILE H 24/08/2006 Now what it should do it select same PNAME in this case HOC have a difference horizontally like 13/08/2006 - 14/08/2006 then start vertical difference 14/08/2006 - 23/08/2006 (for HOC) and go on unless party name occur like in this case IASS. Then again it should have horizontal difference 14/08/2006 - 15/08/2006 and lately the vertical difference. There are two condition first where there is same status like HOC we have two occurance of AG in different dates (23/08/2006 and 24/08/2006) here it should take greater value for SDUR calucation Second it should not add the WDT status in SDUR Result of above data and conditions are expected to be as under IDATE PNAME STATUS RL SDATE DIFF SDUR 13/08/2006 HOC AT Y 14/08/2006 1 13/08/2006 HOC AG Y 23/08/2006 8 13/08/2006 HOC AG H 24/08/2006 2 13/08/2006 HOC UP Y 27/08/2006 2 13/08/2006 HOC FILE H 30/08/2006 3 14 14/08/2006 IASS AT H 15/08/2006 2 14/08/2006 IASS AG H 19/08/2006 4 14/08/2006 IASS UP H 22/08/2006 2 14/08/2006 IASS WDT H 23/08/2006 2 14/08/2006 IASS FILE H 24/08/2006 2 16 i am using networkdays() function for calculation of difference between dates jd |
activity duration
On Aug 26, 6:45 pm, -jawad wrote:
On Aug 26, 2:10 pm, "Bob Phillips" wrote: Why a macro? A formula should do it DIFF: =E2-A2 Running Diff: =IF(A2=A1,F1+F2,"") Not too clear on the rest of what you want, an example might help. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "-jawad" wrote in message roups.com... I have data in following format in excel IDATE PNAME STATUS RL SDATE DIFF 13/08/2006 HOC AT Y 13/08/2006 13/08/2006 HOC AG Y 23/08/2006 13/08/2006 HOC AG H 24/08/2006 13/08/2006 HOC UP Y 27/08/2006 13/08/2006 HOC FILE H 30/08/2006 SUM HOC-STAT I m not good in macro therefore looking for help. Actually what i m trying to do that where the PNAME is same in above data it should subtract SDATE-IDATE and depict the value in DIFF col (days). secondly it should have running difference for PNAME for each row. and count the bigger value if it encountered STATUS and PNAME is same however SDATE is different. Last sum DIFF for each row and shows in next row called sum HOC-STAT. I have 1800 PNAME to do this plz help. jd- Hide quoted text - - Show quoted text - I have following attributes IDATE PNAME STATUS RL SDATE DIFF a little details data is as under IDATE PNAME STATUS RL SDATE DIFF 13/08/2006 HOC AT Y 14/08/2006 13/08/2006 HOC AG Y 23/08/2006 13/08/2006 HOC AG H 24/08/2006 13/08/2006 HOC UP Y 27/08/2006 13/08/2006 HOC FILE H 30/08/2006 14/08/2006 IASS AT H 15/08/2006 14/08/2006 IASS AG H 19/08/2006 14/08/2006 IASS UP H 22/08/2006 14/08/2006 IASS WDT H 23/08/2006 14/08/2006 IASS FILE H 24/08/2006 Now what it should do it select same PNAME in this case HOC have a difference horizontally like 13/08/2006 - 14/08/2006 then start vertical difference 14/08/2006 - 23/08/2006 (for HOC) and go on unless party name occur like in this case IASS. Then again it should have horizontal difference 14/08/2006 - 15/08/2006 and lately the vertical difference. There are two condition first where there is same status like HOC we have two occurance of AG in different dates (23/08/2006 and 24/08/2006) here it should take greater value for SDUR calucation Second it should not add the WDT status in SDUR Result of above data and conditions are expected to be as under IDATE PNAME STATUS RL SDATE DIFF SDUR 13/08/2006 HOC AT Y 14/08/2006 1 13/08/2006 HOC AG Y 23/08/2006 8 13/08/2006 HOC AG H 24/08/2006 2 13/08/2006 HOC UP Y 27/08/2006 2 13/08/2006 HOC FILE H 30/08/2006 3 14 14/08/2006 IASS AT H 15/08/2006 2 14/08/2006 IASS AG H 19/08/2006 4 14/08/2006 IASS UP H 22/08/2006 2 14/08/2006 IASS WDT H 23/08/2006 2 14/08/2006 IASS FILE H 24/08/2006 2 16 i am using networkdays() function for calculation of difference between dates jd- Hide quoted text - - Show quoted text - have following attributes IDATE PNAME STATUS RL SDATE DIFF a little details data is as under IDATE PNAME STATUS RL SDATE DIFF 13/08/2006 HOC AT Y 14/08/2006 13/08/2006 HOC AG Y 23/08/2006 13/08/2006 HOC AG H 24/08/2006 13/08/2006 HOC UP Y 27/08/2006 13/08/2006 HOC FILE H 30/08/2006 14/08/2006 IASS AT H 15/08/2006 14/08/2006 IASS AG H 19/08/2006 14/08/2006 IASS UP H 22/08/2006 14/08/2006 IASS WDT H 23/08/2006 14/08/2006 IASS FILE H 24/08/2006 Now what it should do it select same PNAME in this case HOC have a difference horizontally like 13/08/2006 - 14/08/2006 then start vertical difference 14/08/2006 - 23/08/2006 (for HOC) and go on unless party name occur like in this case IASS. Then again it should have horizontal difference 14/08/2006 - 15/08/2006 and lately the vertical difference. There are two condition first where there is same status like HOC we have two occurance of AG in different dates (23/08/2006 and 24/08/2006) here it should take greater value for SDUR calucation Second it should not add the WDT status in SDUR Result of above data and conditions are expected to be as under IDATE PNAME STATUS RL SDATE DIFF SDUR 13/08/2006 HOC AT Y 14/08/2006 1 13/08/2006 HOC AG Y 23/08/2006 8 13/08/2006 HOC AG H 24/08/2006 2 13/08/2006 HOC UP Y 27/08/2006 2 13/08/2006 HOC FILE H 30/08/2006 3 14 14/08/2006 IASS AT H 15/08/2006 2 14/08/2006 IASS AG H 19/08/2006 4 14/08/2006 IASS UP H 22/08/2006 2 14/08/2006 IASS WDT H 23/08/2006 2 14/08/2006 IASS FILE H 24/08/2006 2 16 i am using networkdays() function for calculation of difference between dates jd |
| All times are GMT +1. The time now is 05:08 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com