| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
maybe by VBE users.....
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..... |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
maybe by VBE users.....
Very untimely that we are going to a retreat for two weeks. By 26th Nov, if
you still need assistance, please email us direct. "4pinoy" wrote in message ... 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..... |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
maybe by VBE users.....
thanks....is your email address "the same" from your profile....?
"PY & Associates" wrote: Very untimely that we are going to a retreat for two weeks. By 26th Nov, if you still need assistance, please email us direct. "4pinoy" wrote in message ... 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..... |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
maybe by VBE users.....
yes please
"4pinoy" wrote in message ... thanks....is your email address "the same" from your profile....? "PY & Associates" wrote: Very untimely that we are going to a retreat for two weeks. By 26th Nov, if you still need assistance, please email us direct. "4pinoy" wrote in message ... 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..... |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
maybe by VBE users.....
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 "4pinoy" wrote in message ... 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 |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| sharing excel sheet with multiple users, but other users cant see | Excel Discussion (Misc queries) | |||
| SAS users please help | Excel Discussion (Misc queries) | |||
| Getting Users Name | Excel Programming | |||
| prevent users from overwriting other users data | Excel Worksheet Functions | |||
| Help - Automating a file.. Adding Users , Deleting users, Changing | Excel Worksheet Functions | |||
Linear Mode
