Results 1 to 14 of 14

20040901, 16:20 #1
 Join Date
 Mar 2004
 Location
 Mechanicsville, Virginia, USA
 Posts
 130
 Thanks
 0
 Thanked 0 Times in 0 Posts
Counting (mon)days between 2 dates (Excel 2000)
Hans,
You revised a function (thread#402239), to count the number of Sundays between a certain date range. Below is the function that you wrote for me:
You can add an extra test to the function (indicated in bold):
Function CountDaysBetween(BegDate As Date, EndDate As Date, aDay As Integer) As Integer
Dim d As Integer
If BegDate > EndDate Then Exit Function
If aDay < 1 Or aDay > 7 Then Exit Function
d = (EndDate  BegDate) 7
If WeekDay(EndDate, aDay) < WeekDay(BegDate, aDay) Or WeekDay(BegDate, aDay) = 1 Then
d = d + 1
End If
If WeekDay(BegDate) = 1 Then
d = d + 1
End If
CountDaysBetween = d
End Function
Is there a formula in Excel that could do the same thing?

20040901, 16:50 #2
 Join Date
 Jan 2001
 Location
 South Carolina, USA
 Posts
 7,295
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Counting (mon)days between 2 dates (Excel 2000)
There is nothing in bold. What do you want the function to do?
Legare Coleman

20040901, 16:52 #3
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Counting (mon)days between 2 dates (Excel 2000)
Hans is on vacation.
There is no builtin excel function to do this, so Hans wrote you a custom function.
You can add this to a workbook, or if you want it available to all workbooks, you can add it to your personal.xls file (see <post#=118382>post 118382</post#> for <!profile=LegareColeman>LegareColeman<!/profile>'s personal.xls tutorial).
In excel you would use this like a "builtin" excel formula.
Steve

20040901, 16:54 #4
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Counting (mon)days between 2 dates (Excel 2000)
If you follow the thread referenced <post#=402239>post 402239</post#>, it lead to the code which Hans had bolded the final "if .. End if " portion as a "modification" to the original code.
Steve

20040901, 17:04 #5
 Join Date
 Jan 2001
 Location
 South Carolina, USA
 Posts
 7,295
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Counting (mon)days between 2 dates (Excel 2000)
It sounds like the NetWorkDays function (in the Analysis Toolpak addin) is what you are looking for:
<hr>
NETWORKDAYS
See Also
Returns the number of whole working days between start_date and end_date. Working days exclude weekends and any dates identified in holidays. Use NETWORKDAYS to calculate employee benefits that accrue based on the number of days worked during a specific term.
If this function is not available, run the Setup program to install the Analysis ToolPak. After you install the Analysis ToolPak, you must enable it by using the AddIns command on the Tools menu.
How?
Syntax
NETWORKDAYS(start_date,end_date,holidays)
Start_date is a date that represents the start date. Dates may be entered as text strings within quotation marks (for example, "1/30/1998" or "1998/01/30"), as serial numbers (for example, 35825, which represents January 30, 1998, if you're using the 1900 date system), or as results of other formulas or functions (for example, DATEVALUE("1/30/1998")).
End_date is a date that represents the end date.
Holidays is an optional range of one or more dates to exclude from the working calendar, such as state and federal holidays and floating holidays. The list can be either a range of cells that contain the dates or an array constant of the serial numbers that represent the dates. Learn about array constants. For more information about how Microsoft Excel uses serial numbers for dates, see the Remarks section.
Remark
Excel stores dates as sequential serial numbers so that it can perform calculations on them. Excel stores January 1, 1900, as serial number 1 if your workbook uses the 1900 date system. If your workbook uses the 1904 date system, Excel stores January 1, 1904, as serial number 0 (January 2, 1904, is serial number 1). For example, in the 1900 date system, Excel stores January 1, 1998, as serial number 35796 because it is 35,795 days after January 1, 1900. Learn more about how Microsoft Excel stores dates and times.
If any argument is not a valid date, NETWORKDAYS returns the #NUM! error value.
Examples
Given a project that begins October 1, 1998, and ends December 1, 1998, the following example calculates the number of days in the project. November 26, 1998, is a holiday and is excluded in the calculation.
NETWORKDAYS("10/01/1998","12/01/1998","11/26/1998") equals 43.
The following example returns the number of workdays for a project that begins on October 1, 1999, and ends on February 15, 2000; it excludes December 24, 1999, and January 3, 2000.
NETWORKDAYS("10/01/1999",
"2/15/2000",{"12/24/1999","1/3/2000"}) equals 96
The following example calculates the number of workdays between May 1, 2002 and May 31, 2002, inclusive; it excludes May 28, 2002.
NETWORKDAYS("2002/05/01","2002/05/31","2002/05/28") equals 22
<hr>Legare Coleman

20040902, 00:30 #6
 Join Date
 Mar 2004
 Location
 Mechanicsville, Virginia, USA
 Posts
 130
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Counting (mon)days between 2 dates (Excel 2000)
Sorry for the delayed response....
We actually ended up taking the function that Hans modified for me and pasting it into a module with the excel spreadsheet. It is calculating the number of Sundays, as we needed it to.
Thanks again!

20040902, 10:33 #7
 Join Date
 Aug 2004
 Posts
 123
 Thanks
 0
 Thanked 1 Time in 1 Post
Re: Counting (mon)days between 2 dates (Excel 2000)
Hi macropod,
I can't obtain the same result as compare your formula with Cpearson's array formula
Here is the Cpearson's array formula :
{=SUM(IF(WEEKDAY(A21+ROW(INDIRECT("1:"&TRUNC(B2A2)+1)))=C2,1,0))}
A2 Starting date of the interval
B2 Ending date of the interval
C2 contains the dayofweek number (1=Sunday, 2=Monday,3=Wednesday, 4=Thrusday,5=Friday, 6=Satriday, 7=Saturday)
Regards

20040902, 11:05 #8
 Join Date
 May 2002
 Location
 Canberra, Australian Capital Territory, Australia
 Posts
 5,055
 Thanks
 2
 Thanked 417 Times in 346 Posts
Re: Counting (mon)days between 2 dates (Excel 2000)
If your dates are in A1 & B1, a worksheet formula to do this could be expressed as:
=ABS((IF(INT(B1/7)*7+1<=B1,INT(B1/7)*7+1,INT(B1/7)*76)IF(INT(A1/7)*7+1>=A1,INT(A1/7)*7+1,INT(A1/7)*7+8))/7)
Cheers
Edited  should be:
=ABS((IF(INT(B1/7)*7+1<=B1,INT(B1/7)*7+1,INT(B1/7)*76)IF(INT(A1/7)*7+1>=A1,INT(A1/7)*7+1,INT(A1/7)*7+8)+7)/7)Cheers,
Paul Edstein
[MS MVP  Word]

20040902, 11:56 #9
 Join Date
 May 2002
 Location
 Canberra, Australian Capital Territory, Australia
 Posts
 5,055
 Thanks
 2
 Thanked 417 Times in 346 Posts
Re: Counting (mon)days between 2 dates (Excel 2000)
Glad to see someone was watching!
I omitted a '+7'. My (nonarray) equivalent to Chip Pearson's formula would be:
=ABS((IF(INT(B2/7)*7+C2<=B1,INT(B2/7)*7+C2,INT(B2/7)*77+C2)IF(INT(A2/7)*7+C2>=A2,INT(A2/7)*7+C2,INT(A2/7)*7+7+C2)+7)/7)
Mine has the advantage of not being sensitive to the date order <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
CheersCheers,
Paul Edstein
[MS MVP  Word]

20040902, 12:18 #10
 Join Date
 Aug 2004
 Posts
 123
 Thanks
 0
 Thanked 1 Time in 1 Post
Re: Counting (mon)days between 2 dates (Excel 2000)
Hi macropod,
I think your formula :
=ABS((IF(INT(B2/7)*7+C2<=B1,INT(B2/7)*7+C2,INT(B2/7)*77+C2)IF(INT(A2/7)*7+C2>=A2,INT(A2/7)*7+C2,INT(A2/7)*7+7+C2)+7)/7)
Should read as :
=ABS((IF(INT(B2/7)*7+C2<=B2,INT(B2/7)*7+C2,INT(B2/7)*77+C2)IF(INT(A2/7)*7+C2>=A2,INT(A2/7)*7+C2,INT(A2/7)*7+7+C2)+7)/7)
However, here is the simplified formula,
Can achieve the same results with Cpearson's array formula :
=INT(((B2A2)+7MOD(C2A2,7))/7)
A2 Starting date of the interval
B2 Ending date of the interval
C2 contains the dayofweek number (1=Sunday, 2=Monday,3=Wednesday, 4=Thrusday,5=Friday, 6=Satriday, 7=Saturday)
Regards

20040903, 08:55 #11
 Join Date
 Sep 2004
 Location
 Portsmouth, Hampshire, England
 Posts
 200
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Counting (mon)days between 2 dates (Excel 2000)
> Can achieve the same results with Cpearson's array formula :
>
> =INT(((B2A2)+7MOD(C2A2,7))/7)
Surely this isn't an array formula. (and there is no Tuesday in your list)
But, if you don't mind using slightly different numbers for your dates, I can achieve this with an even simpler formula:
=INT((B2A2+C2)/7)
A2 Starting date of the interval
B2 Ending date of the interval
C2 contains the dayofweek number (6=Monday,7=Tuesday,1=Wednesday, 2=Thursday,3=Friday, 4=Saturday, 5=Sunday)

20040903, 10:04 #12
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Counting (mon)days between 2 dates (Excel 2000)
Your formula does not always work.
1 example
A2 = Thu Jan 1, 2004
B2 = Mon Jan 5, 2004
C2 = 7 (Tuesday) = 1 should be 0
C2 = 2 (Thursday) = 0 should be 1
Steve

20040903, 10:28 #13
 Join Date
 Sep 2004
 Location
 Portsmouth, Hampshire, England
 Posts
 200
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Counting (mon)days between 2 dates (Excel 2000)
You're right, it doesn't. I used to have this formula off pat!
It doesn't need much tweaking I'm sure. I'll have another bash!

20040903, 11:42 #14
 Join Date
 Aug 2004
 Posts
 123
 Thanks
 0
 Thanked 1 Time in 1 Post
Re: Counting (mon)days between 2 dates (Excel 2000)
Sorry for the posting mistake in the list,
Here is the revised :
Formula