Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculation Project Date skip Sun and Sat
Hi All
I am prepare VBA program for calculate Project Stat and End date base on Process ID and Depend ID, I already complete the Calculation the Start and End Date. Now, the Start/End may be Sun or Sat, the During may be include Sun and Sat, I want to know how to skip Sun/Sat ? The Calculate date is max(End Date) base on Depend ID e.g. 40,60 on Depend ID field, the start Date will be base on Process 40 and 60. Calculation Date Planning Start Date Process Depend Start Date End date During 10 11/01/2007 11/02/2007 1 11/02/2007 20 10 11/03/2007 11/05/2007 2 11/02/2007 30 10 11/04/2007 11/07/2007 3 11/02/2007 40 10 11/05/2007 11/09/2007 4 11/09/2007 11/09/2007 50 40 11/13/2007 11/18/2007 5 11/09/2007 60 40 11/14/2007 11/20/2007 6 11/20/2007 70 40,60 11/26/2007 12/03/2007 7 11/20/2007 80 60 11/27/2007 12/05/2007 8 11/20/2007 90 60 11/28/2007 12/07/2007 9 11/20/2007 100 60 11/29/2007 12/04/2007 5 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculation Project Date skip Sun and Sat
Make sure the Analysis Toolpak is loaded (In the worksheet: ToolsAdd-ins, check Analysis Toolpak)
In the VBE: Toolsreference, check atpvbaen.xls Now you can use the WORKDAY() function the same way as in a workbook; see HELP -- Kind regards, Niek Otten Microsoft MVP - Excel "moonhk" wrote in message ... | Hi All | I am prepare VBA program for calculate Project Stat and End date base | on Process ID and Depend ID, I already complete the Calculation the | Start and End Date. Now, the Start/End may be Sun or Sat, the During | may be include Sun and Sat, I want to know how to skip Sun/Sat ? | | | The Calculate date is max(End Date) base on Depend ID | e.g. 40,60 on Depend ID field, the start Date will be base on Process | 40 and 60. | | | | Calculation Date Planning Start Date Process Depend Start Date End | date During | 10 11/01/2007 11/02/2007 1 | 11/02/2007 20 10 11/03/2007 11/05/2007 2 | 11/02/2007 30 10 11/04/2007 11/07/2007 3 | 11/02/2007 40 10 11/05/2007 11/09/2007 4 | 11/09/2007 11/09/2007 50 40 11/13/2007 11/18/2007 5 | 11/09/2007 60 40 11/14/2007 11/20/2007 6 | 11/20/2007 70 40,60 11/26/2007 12/03/2007 7 | 11/20/2007 80 60 11/27/2007 12/05/2007 8 | 11/20/2007 90 60 11/28/2007 12/07/2007 9 | 11/20/2007 100 60 11/29/2007 12/04/2007 5 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculation Project Date skip Sun and Sat
=WORKDAY(A2,1) will give a result 1 weekday after the date in A2 (IE, Monday
after Friday) But you need to have one of the Excel addons turned on to use this formula. Otherwise theres =IF(WEEKDAY(A2,2)5,A2+(8-WEEKDAY(A2,2)),A2) Which will see if the date is a Sat or Sun (6,7) and if so add on 2 or 1 days to make it a monday. "moonhk" wrote: Hi All I am prepare VBA program for calculate Project Stat and End date base on Process ID and Depend ID, I already complete the Calculation the Start and End Date. Now, the Start/End may be Sun or Sat, the During may be include Sun and Sat, I want to know how to skip Sun/Sat ? The Calculate date is max(End Date) base on Depend ID e.g. 40,60 on Depend ID field, the start Date will be base on Process 40 and 60. Calculation Date Planning Start Date Process Depend Start Date End date During 10 11/01/2007 11/02/2007 1 11/02/2007 20 10 11/03/2007 11/05/2007 2 11/02/2007 30 10 11/04/2007 11/07/2007 3 11/02/2007 40 10 11/05/2007 11/09/2007 4 11/09/2007 11/09/2007 50 40 11/13/2007 11/18/2007 5 11/09/2007 60 40 11/14/2007 11/20/2007 6 11/20/2007 70 40,60 11/26/2007 12/03/2007 7 11/20/2007 80 60 11/27/2007 12/05/2007 8 11/20/2007 90 60 11/28/2007 12/07/2007 9 11/20/2007 100 60 11/29/2007 12/04/2007 5 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculation Project Date skip Sun and Sat
On 11$B7n(B15$BF|(B, $B2<8a(B5$B;~(B14$BJ,(B, PaulW wrote:
=WORKDAY(A2,1) will give a result 1 weekday after the date in A2 (IE, Monday after Friday) But you need to have one of the Excel addons turned on to use this formula. Otherwise theres =IF(WEEKDAY(A2,2)5,A2+(8-WEEKDAY(A2,2)),A2) Which will see if the date is a Sat or Sun (6,7) and if so add on 2 or 1 days to make it a monday. "moonhk" wrote: Hi All I am prepare VBA program for calculate Project Stat and End date base on Process ID and Depend ID, I already complete the Calculation the Start and End Date. Now, the Start/End may be Sun or Sat, the During may be include Sun and Sat, I want to know how to skip Sun/Sat ? The Calculate date is max(End Date) base on Depend ID e.g. 40,60 on Depend ID field, the start Date will be base on Process 40 and 60. Calculation Date Planning Start Date Process Depend Start Date End date During 10 11/01/2007 11/02/2007 1 11/02/2007 20 10 11/03/2007 11/05/2007 2 11/02/2007 30 10 11/04/2007 11/07/2007 3 11/02/2007 40 10 11/05/2007 11/09/2007 4 11/09/2007 11/09/2007 50 40 11/13/2007 11/18/2007 5 11/09/2007 60 40 11/14/2007 11/20/2007 6 11/20/2007 70 40,60 11/26/2007 12/03/2007 7 11/20/2007 80 60 11/27/2007 12/05/2007 8 11/20/2007 90 60 11/28/2007 12/07/2007 9 11/20/2007 100 60 11/29/2007 12/04/2007 5- $Bp,i6Ho0zMQJ8;z(B - - $Bp}<(Ho0zMQJ8;z(B - Thank a lot |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Date - I would like to skip weekends in a date range | Excel Worksheet Functions | |||
Skip Weekends in date | Excel Worksheet Functions | |||
skip cell if blank go on to use another cell in calculation | Excel Programming | |||
Time calculation problem (URGENTProject due) | Excel Discussion (Misc queries) | |||
Coding to show first _weekday_ prior to a date, when date calculation happens to fall on weekend? | Excel Programming |