How to write a macro to count dates

[BEETAL]

Assume that I have two dates 1st Jan 2009 and 30th Apr 2009. I want to write
a VBA where in I will be left with a Calender of only no of working days,
excluding saturdays ,sundays and assigned holidays.

Like Calender can be a matrix with many rows and one column.

I am trying to find out if any worksheet function can be used in VBA for
this purpose

Any help on this topic will be gratefully appreciated. Help Please!

 

[BEETAL]

Here is the code that I am using to count the number of working days.

The simple task remains to dump the working days in an array called Calender
which should be variant. Like Calender(1) will have the first valid date
stored.
calender(2) will have the second one stored etc.

Can someone modify this code ,please.
Function GetWorkDays(StartDate As Long, EndDate As Long) As Long
' returns the count of days between StartDate - EndDate minus Saturdays and
Sundays
Dim d As Long, dCount As Long
For d = StartDate To EndDate
If WeekDay(d, vbMonday) < 6 Then
dCount = dCount + 1
End If
Next d
GetWorkDays = dCount
End Function

 

[BEETAL]

Function GetWorkDays(StartDate As Long, EndDate As Long) As Long
' returns the count of days between StartDate - EndDate minus Saturdays and
Sundays
Dim d As Long, dCount As Long
For d = StartDate To EndDate
If WeekDay(d, vbMonday) < 6 Then
dCount = dCount + 1
End If
Next d
GetWorkDays = dCount
End Function

Can this code be modified in a smart way to store the valid dates in an array.

 

[Jarek Kujawa]

define a named range "holidays" in the same worksheet

replace
If WeekDay(d, vbMonday) < 6 Then

with
If Weekday(d, vbMonday) < 6 And
Application.WorksheetFunction.CountIf(Range("holidays"), d) = 0 Then

and give it a try
works well on my Excel 2003

HIH

 

[Jarek Kujawa]

....define a named range "holidays" in the same worksheet and put your
holiday dates there...

then follow...

 

[BEETAL]

Jarek,

Many many thanks. I would request a few minutes more of yours.

Once we have the counting done, as I wrote in my earlier request, I need to
have a counter for any date between the two dates. Like given a date 16th Feb
2009, the programme should be able to move to another subroutine.

Like if the dcount falls on 16th Feb 2009, then the programme calls a
subroutine. Let me put it more clearly.

we have start date 1st January 2009. End date 1st June 2009. We get the
number of workdays in between. I want to store these days in a calender. I
would then call this calender and manipulate the calculations in another
routine. The routine will have different parameters for dates before 16th Feb
2009.

If you have some time to throw some light on how to create this array of
dates to access them, it shall be really kind of you.
thanks a lot.

 

[Jarek Kujawa]

may be missing sth. but would this be helpful?

Function GetWorkDays(StartDate As Long, EndDate As Long) As Long
' returns the count of days between StartDate - EndDate minus
Saturdays and
Sundays
Dim d As Long, dCount As Long
For d = StartDate To EndDate
If WeekDay(d, vbMonday) < 6 And _
Application.WorksheetFunction.CountIf(Range("holidays"), d) =
0 Then
dCount = dCount + 1
If dCount = 39860 Then
Call OtherSub
End If
End If
Next d
GetWorkDays = dCount
End Function

39860 is a numeric value of Feb. 16, 2009

 

[BEETAL]

Many thanks Jarek.

I will modify the stuff to get the rest done.

Thank you so much.

 

[BEETAL]

Jarek, one more request. I have put another post by the name
This request for the very Experts.Please reply at the earliest"

Could you please visit the thread and give few minutes to ponder over the
codes.

It will be very kind of you.