# Thread: Counting (mon)days between 2 dates (Excel 2000)

1. ## 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
d = (EndDate - BegDate) 7
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?

2. ## Re: Counting (mon)days between 2 dates (Excel 2000)

There is nothing in bold. What do you want the function to do?

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

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

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

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 Add-Ins 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>

6. ## 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!

7. ## 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(A2-1+ROW(INDIRECT("1:"&TRUNC(B2-A2)+1)))=C2,1,0))}

A2 Starting date of the interval
B2 Ending date of the interval
C2 contains the day-of-week number (1=Sunday, 2=Monday,3=Wednesday, 4=Thrusday,5=Friday, 6=Satriday, 7=Saturday)

Regards

8. ## 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)*7-6)-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)*7-6)-IF(INT(A1/7)*7+1>=A1,INT(A1/7)*7+1,INT(A1/7)*7+8)+7)/7)

9. ## Re: Counting (mon)days between 2 dates (Excel 2000)

Glad to see someone was watching!

I omitted a '+7'. My (non-array) equivalent to Chip Pearson's formula would be:
=ABS((IF(INT(B2/7)*7+C2<=B1,INT(B2/7)*7+C2,INT(B2/7)*7-7+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>

Cheers

10. ## Re: Counting (mon)days between 2 dates (Excel 2000)

Hi macropod,

=ABS((IF(INT(B2/7)*7+C2<=B1,INT(B2/7)*7+C2,INT(B2/7)*7-7+C2)-IF(INT(A2/7)*7+C2>=A2,INT(A2/7)*7+C2,INT(A2/7)*7+7+C2)+7)/7)

=ABS((IF(INT(B2/7)*7+C2<=B2,INT(B2/7)*7+C2,INT(B2/7)*7-7+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(((B2-A2)+7-MOD(C2-A2,7))/7)

A2 Starting date of the interval
B2 Ending date of the interval
C2 contains the day-of-week number (1=Sunday, 2=Monday,3=Wednesday, 4=Thrusday,5=Friday, 6=Satriday, 7=Saturday)

Regards

11. ## Re: Counting (mon)days between 2 dates (Excel 2000)

> Can achieve the same results with Cpearson's array formula :
>
> =INT(((B2-A2)+7-MOD(C2-A2,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((B2-A2+C2)/7)

A2 Starting date of the interval
B2 Ending date of the interval
C2 contains the day-of-week number (6=Monday,7=Tuesday,1=Wednesday, 2=Thursday,3=Friday, 4=Saturday, 5=Sunday)

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

13. ## 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!

14. ## Re: Counting (mon)days between 2 dates (Excel 2000)

Sorry for the posting mistake in the list,

Here is the revised :

Formula

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•