activity duration

[-jawad]

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

 

[Bob Phillips]

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]

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)










- 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

 

[-jawad]

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