counting Sundays between two dates

  • Thread starter Thread starter Guest
  • Start date Start date
G

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?
 
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.
 
Hi
try the formula:
=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&A2)))<>1),--(COUNTIF(C1:C5,ROW(INDIRECT(A1&":"&A2)))=0))*(1-2*(A1>A2))
 
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....
 
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....
Click to expand...

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


--ron
 
Back
Top