counting Sundays between two dates
 

Given A1= 1/5/05 and A2=1/20/05
and range C1:C5 is a list of holiday dates, what can I do to calculate the
duration of the two dates, minus sundays and the holidays?

I've gotten as far as

=A2-A1+1-(COUNTIF(C1:C5,"="&A1)-COUNTIF(C1:C5,""&A2))

A2-A1+1 calculates the duration between the two dates inclusive;
COUNTIF(C1:C5,"="&A1)-COUNTIF(C1:C5,""&A2) calculates the number of
holidays which fall within the dates

I have not figured out a way to count sundays.. anyone with any suggestions?

counting Sundays between two dates
 

Option Explicit
Sub date_count()
Dim StartDate As Date, EndDate As Date, CurrentDate As Date
Dim DaySum As Integer
Dim Holidays As String
DaySum = 0
Holidays = Range("C1") & Range("C2") & Range("C3") & Range("C4") &
Range("C5")
StartDate = Range("A1")
EndDate = Range("A2")
For CurrentDate = StartDate To EndDate Step 1
If InStr(1, Holidays, CurrentDate, vbTextCompare) = 0 Then
If Weekday(CurrentDate) < 1 Then
DaySum = DaySum + 1
End If
End If
Next
MsgBox DaySum
End Sub

When you paste this in, account for the syntex being jacked up because
of the width limit of this window.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

counting Sundays between two dates
 

Hi
try the formula:
=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&A2)))<1),--(COUNTIF(C1:C5,ROW(INDIRECT(A1&":"&A2)))=0))*(1-2*(A1A2))

--
Regards
Frank Kabel
Frankfurt, Germany
"kippi3000" schrieb im Newsbeitrag
...
Given A1= 1/5/05 and A2=1/20/05
and range C1:C5 is a list of holiday dates, what can I do to calculate the
duration of the two dates, minus sundays and the holidays?

I've gotten as far as

=A2-A1+1-(COUNTIF(C1:C5,"="&A1)-COUNTIF(C1:C5,""&A2))

A2-A1+1 calculates the duration between the two dates inclusive;
COUNTIF(C1:C5,"="&A1)-COUNTIF(C1:C5,""&A2) calculates the number of
holidays which fall within the dates

I have not figured out a way to count sundays.. anyone with any
suggestions?

counting Sundays between two dates
 

On Fri, 31 Dec 2004 09:16:35 +0100, "Frank Kabel"
wrote:

ROW(INDIRECT(A1&":"&A2))


And don't forget about the dreaded 5 Jun 2079 problem, after which this
solution won't work :-)


--ron

counting Sundays between two dates
 

Hi Ron
lol
hopefully at that time I don't have to do Excel anymore :-)))

or maybe in 2070 Microsoft will give us an Excel version with more than
65536 rows....

--
Regards
Frank Kabel
Frankfurt, Germany

Ron Rosenfeld wrote:
On Fri, 31 Dec 2004 09:16:35 +0100, "Frank Kabel"
wrote:

ROW(INDIRECT(A1&":"&A2))


And don't forget about the dreaded 5 Jun 2079 problem, after which
this solution won't work :-)


--ron

counting Sundays between two dates
 

On Fri, 31 Dec 2004 12:50:28 +0100, "Frank Kabel"
wrote:

Hi Ron
lol
hopefully at that time I don't have to do Excel anymore :-)))

or maybe in 2070 Microsoft will give us an Excel version with more than
65536 rows....

Just remember, those who devised the two digit year code never expected their
solutions would continue to be used in this century, either!! :-))


--ron