excel date scheduling not msProject
 

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.....