On Aug 26, 6:45 pm, -jawad <Jawad.Moham...@gmail.com> wrote:
> On Aug 26, 2:10 pm, "Bob Phillips" <bob....@somewhere.com> 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" <Jawad.Moham...@gmail.com> wrote in message
>
> >news:(E-Mail Removed)...
>
> > >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
|
Similar Threads
