Results 1 to 14 of 14
  1. #1
    2 Star Lounger
    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?

  2. #2
    Uranium Lounger
    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

  3. #3
    WS Lounge VIP sdckapr's Avatar
    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

  4. #4
    WS Lounge VIP sdckapr's Avatar
    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

  5. #5
    Uranium Lounger
    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 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>
    Legare Coleman

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

  7. #7
    2 Star Lounger
    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(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. #8
    Super Moderator
    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)*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)
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  9. #9
    Super Moderator
    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 (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
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  10. #10
    2 Star Lounger
    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)*7-7+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)*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. #11
    3 Star Lounger
    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(((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. #12
    WS Lounge VIP sdckapr's Avatar
    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

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

  14. #14
    2 Star Lounger
    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

Posting Permissions

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