Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting calendar Days - not including Sundays | Excel Discussion (Misc queries) | |||
Number of sundays between two dates | Excel Worksheet Functions | |||
how do i sum day of the week without counting sats and sundays? | Excel Worksheet Functions | |||
calculation sundays between two dates | Excel Discussion (Misc queries) | |||
calculate how many sundays between two dates in excel | Excel Discussion (Misc queries) |