maybe by VBE users.....

[Guest]

In an excel project scheduling under one sheet (WYSIWYG : amount, qty, days :
back-up with Auditing tools and Circular Pop-ups ) and trying to avoid bounty
windows of MS Project (very hard to re-audit) and due to records
availability.....I hope excel users can help me with my job task now....

Here's the 1st phase question..A DATE PROBLEM...

PROBLEM :
what formula to fill-in for my 100 activity list.
FOR EACH ACTIVITY
UPDATE START DATE: on A7
UPDATE FINISH DATE: on B7
UPDATE DURATION: on C7 : as effective days

I have data available for one item

ORIG. START DATE : on A5 = 02-Oct-06
ORIG. FINISH DATE : on B5 = 02-Nov-02
ORIG DURATION : on C5 = B5-A5+1 = 397 days.

OUR DEFINED NAMES WITH DATA AVAILABLE TO COVER THE WHOLE SCHEDULING:
MO2 = M1:CV1 (contains the 1st date of each month ;e.g. 1-Sep-06, 1-Nov-10)
......M1= 01-Jan-06
MF2 = M2:CV2 (contains the 1st date of each month ;e.g. 30-Sep-06, 30-Nov-10)
......M2= 31-Jan-06
DPM2 = M3:CV3 (contains the number of calendar days in each month above)
......M3= 31
RDPM2 = M4:CV4 (contains the project actual number of effective days in each
month above)
......effective days means Calendar days minus actual non-effective days......
......this is updated once after each month....value will be encoded....
.....when the month has not yet been consumed....DPM2=RDPM2......

for a sample solution, diregarding other feasible constraints,
::::to solve for the Update Start Date
IF....
sum(M3:U3) = sum of calendar days from Jan to Sep.2006 = 273
sum(M4:U4) = sum of effective days from Jan to Sep.2006 = 245
(average of 3.days per month is deducted as non-effective duration)
AND
orig start date : A5 = 02-OCT-06
THEN
update start date : A7 = A5 + (273-245) = A5 + 28 = 29-OCT-06

::::to solve for the Update Start Date
IF....
sum(M3:V3) = sum of calendar days from Jan to Oct.2006 = 304
sum(M4:V4) = sum of effective days from Jan to Oct.2006 = 273
(average of 3.days per month is deducted as non-effective duration)
AND
orig end date : B5 = 02-NOV-07
THEN
update end date : B7 = B5 + (304-273) = A5 + 31 = 02-DEC-06
::::::
Please take note that we do not have the official daily records, only one
figure in every consumed month.

Thanks in advance for everyone..cheers with excel.....

 

[PY & Associates]

Very untimely that we are going to a retreat for two weeks. By 26th Nov, if
you still need assistance, please email us direct.

 

[Guest]

thanks....is your email address "the same" from your profile....?

 

[PY & Associates]

yes please

thanks....is your email address "the same" from your profile....?

 

[PY & Associates]

We must have misread you message and therefore cannot imagine what you are
trying to achieve.

If you are trying to move the start date along month to month
Maybe you can have a sum of NON effective days (=sum(M4:CV4))
As you input these non effective days month to month, the sum will be
updated automatically
And so is the Update Start Date=Orig Start Date + sum

If the 100 activities have their own individual start dates
To update en masse, then copy sum
highlight A7:A107
edit/pastespecial/add

We are not reading the news group now.
Please send mails to us direct if you can catch us before we go to the
retreat.

Cheers