Count Specific Day Name Between 2 Dates

  • I found these 2 examples online on how to count Mondays between 2 dates but neither seems to work on Excel 2003, which I am using. Can anyone help me please?



    EXAMPLE 1


    Number Of Mondays In Period


    If you need to return the number of Mondays (or any other day) that occur within an interval between two dates, use the following Array Formula:


    =SUM(IF(WEEKDAY(A2-1+ROW(INDIRECT("1:"&TRUNC(B2-A2)+1)))=C2,1,0))


    This formula assumes the following:
    A2 contains the beginning date of the interval
    B2 contains the ending date of the interval
    C2 contains the day-of-week number (1=Sunday, 2=Monday,...,7=Saturday)




    EXAMPLE 2


    Copy the code below to a tab sheet module, the name must be unchanged.
    Then on your worksheet have a cell for the "Start date," another for the "End date" and a cell for the days of the week to be counted "WkDays" as a data cell.


    In the cell you want your day count to be listed put:
    =WkDays(StartDate, EndDate, WkDays)
    Like,
    =WkDays(B3,B4,B5)
    The key to WkDays is: 1=Monday...7=Sunday.


    To count Saturdays and Sundays, WkDays would be 67, to count Saterday WkDays would be 6, to count Monday through Friday WkDays would be 12345 as day data.






    Thank you.


    Erik

  • Re: Count Mondays (or Any Other Day Of Week) Between 2 Dates


    Hi Erik


    Start date in A1, end date in A2 then you could use:


    =INT((A2-A1+WEEKDAY(A1-2))/7)


    The -2 in the WEEKDAY() function determines the date of interest (as per Excel help ie 1 = Sunday, 2 = Monday, 3 = Tuesday etc).


    Richard

  • Re: Count Mondays (or Any Other Day Of Week) Between 2 Dates


    Based on the same assumptions as in your first example, try...


    =SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A2&":"&B2)))=C2))


    Hope this helps!

  • Re: Count Mondays (or Any Other Day Of Week) Between 2 Dates


    Quote

    =INT((A2-A1+WEEKDAY(A1-2))/7)


    Unless I'm missing something, it appears that the above formula can return an undesired result...



  • Re: Count Mondays (or Any Other Day Of Week) Between 2 Dates


    Hi Erik - there is no need to put code tags around your entire question - only around the bits where you are actually using Code... I have updated your thread for you.


    Cheers
    Ger

    Check out our new reputation system. Click on the Like button under the post!
    _______________________________________________

    [SIZE=8px]There are 10 types of people in the world. Those that understand Binary and those that dont.


    Why are Halloween and Christmas the same? Because Oct 31 = Dec 25...


    The BEST Lookup function of all time


    Dynamic Named Ranges are your bestest friend[/SIZE]
    _______________________________________________

  • Re: Count Mondays (or Any Other Day Of Week) Between 2 Dates


    Quote from Domenic

    Unless I'm missing something, it appears that the above formula can return an undesired result...


    Domenic, what exact formulas are you using because I can't recreate those results you quote. The integer modifier within the WEEKDAY() obviously needs to be changed for the different days of the week (as I specified)...

  • Re: Count Mondays (or Any Other Day Of Week) Between 2 Dates


    Quote from RichardSchollar

    Domenic, what exact formulas are you using because I can't recreate those results you quote. The integer modifier within the WEEKDAY() obviously needs to be changed for the different days of the week (as I specified)...


    Sorry Richard! My Mistake! I forgot to change the -2 bit to -1... :oops:

  • Re: Count Mondays (or Any Other Day Of Week) Between 2 Dates


    Quote from ejlupien

    =SUM(IF(WEEKDAY(A2-1+ROW(INDIRECT("1:"&TRUNC(B2-A2)+1)))=C2,1,0))


    Hello Erik,


    I favour Richard's suggestion for it's brevity and efficiency but there isn't any reason why the above shouldn't work, you need to confirm the formula with CTRL+SHIFT+ENTER, if you're doing that what results do you get?

  • Re: Count Mondays (or Any Other Day Of Week) Between 2 Dates


    Hi daddylonglegs,


    I like any solution to be honest, I wasn't going to be picky *smile*.


    You're right, when I "confirmed the formula" with CTRL+SHIRT+ENTER I got the answer.


    Why did I not get the answer when I just entered the formula in the cell and hit enter? Why would is confirming the formula necessary? When I confirmed the formula, Excel added {} before and after the formula.


    Erik

  • Re: Count Mondays (or Any Other Day Of Week) Between 2 Dates


    Erik


    Entering it with Ctrl+Shift+Enter makes it an array formula which in your case made this bit:


    ROW(INDIRECT("1:"&TRUNC(B2-A2)+1))


    return the whole list of row values from 1:number of days between a2 and b2. If you don't array enter the formula, you only get the value 1 returned by this statement (ie row(1:100) entered normally in a cell returns just 1).


    Richard

  • Re: Count Specific Day Name Between 2 Dates


    I arrived here after Googling for how to find how many Mondays there are in a month. My solution is below. No UDFs, no array formulas. Mine only counts the number of Mondays in a month. I haven't tested it thoroughly, but it works for the 7 months I did test it on. I'm posting it here because others may be able to adapt it to their needs.


    =ROUNDUP(((FirstDayOfNEXTMonth-FirstDayOfMonth)-(MOD(7-WEEKDAY(FirstDayOfMonth,3),7)))/7,0)

  • Re: Count Specific Day Name Between 2 Dates


    Thanks for adding to the solutions! :-D


    Best Regards
    Ger

    Check out our new reputation system. Click on the Like button under the post!
    _______________________________________________

    [SIZE=8px]There are 10 types of people in the world. Those that understand Binary and those that dont.


    Why are Halloween and Christmas the same? Because Oct 31 = Dec 25...


    The BEST Lookup function of all time


    Dynamic Named Ranges are your bestest friend[/SIZE]
    _______________________________________________

  • Re: Count Specific Day Name Between 2 Dates


    Quote from Baodad;644199

    I arrived here after Googling for how to find how many Mondays there are in a month. My solution is below. No UDFs, no array formulas. Mine only counts the number of Mondays in a month. I haven't tested it thoroughly, but it works for the 7 months I did test it on. I'm posting it here because others may be able to adapt it to their needs.


    =ROUNDUP(((FirstDayOfNEXTMonth-FirstDayOfMonth)-(MOD(7-WEEKDAY(FirstDayOfMonth,3),7)))/7,0)



    Hi,
    How many Mondey is in a specified month.
    Something like is this formula:


    = 4+(DAY(A1-DAY(A1)+35)< WEEKDAY(A1-DAY(A1)-1))


    (Monday=1,...Sunday=7)