counting Sundays between two dates

[Guest]

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?

 

[Claud Balls]

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.

 

[Frank Kabel]

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

 

[Ron Rosenfeld]

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

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


--ron

 

[Frank Kabel]

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

 

[Ron Rosenfeld]

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