Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 55
Default 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.....




Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Calendar Control in Excel 2000 can't display date in Excel 2003? Lewis Excel Discussion (Misc queries) 0 April 21st 06 05:07 PM
TRYING TO SET UP EXCEL SPREADSHEET ON MY COMPUTER MEGTOM New Users to Excel 5 October 27th 05 03:06 AM
Excel Range Value issue (Excel 97 Vs Excel 2003) Keeno Excel Discussion (Misc queries) 2 June 13th 05 02:01 PM
Excel file modification date GROSNER Excel Discussion (Misc queries) 5 March 4th 05 02:19 AM
Excel formula with date constraints Warrior Pope Excel Discussion (Misc queries) 3 January 28th 05 04:08 PM


All times are GMT +1. The time now is 11:09 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"