Calculation Project Date skip Sun and Sat

[moonhk]

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

 

[Niek Otten]

Make sure the Analysis Toolpak is loaded (In the worksheet: Tools>Add-ins, check Analysis Toolpak)
In the VBE: Tools>reference, 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

| 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

 

[PaulW]

=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]

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







- $Bp}<(Ho0zMQJ8;z(B -

Thank a lot