Count Number of Certain day between two dates

[eoinymc]

Hi,

I want to count the number of Wednesdays between 02-Apr-14 & 22-Oct-14.

If I have the first date in A1 and the second date in B1, does anyone know if it's possible to do this in a formula?

thanks,

Eoin

 

[Rick Rothstein]

I want to count the number of Wednesdays between 02-Apr-14 & 22-Oct-14.

If I have the first date in A1 and the second date in B1, does anyone know if it's possible to do this in a formula?

Give this array-entered** formula a try...

=SUM(0+(WEEKDAY(ROW(INDIRECT("A"&A1&":A"&B1)))=4))

**Commit this formula using CTRL+SHIFT+ENTER and not just Enter by itself.

Note: The 4 in my formula is the weekday number for Wednesday.

 

[aaronbens]

The following will work also includes start and end date in the calculation:

=(WEEKNUM(B1)+IF(WEEKDAY(B1)<4,0,1))-(WEEKNUM(A1)+(IF(WEEKDAY(A1)<5,0,1)))

 

[Rick Rothstein]

Give this array-entered** formula a try...

=SUM(0+(WEEKDAY(ROW(INDIRECT("A"&A1&":A"&B1)))=4))

**Commit this formula using CTRL+SHIFT+ENTER and not just Enter by itself.

Note: The 4 in my formula is the weekday number for Wednesday.

Actually, we can simplify that formula slightly (still array-entered)...

=SUM(0+(WEEKDAY(ROW(INDIRECT(A1&":"&B1)))=4))

 

[eoinymc]

Thanks guys...that works perfectly!

 

[barry houdini]

There are some non array alternatives, e.g.

=INT((WEEKDAY(A1-4)+B1-A1)/7)

or if you are using Excel 2010 or later you can use NETWORKDAYS.INTL function like this

=NETWORKDAYS.INTL(A1,B1,"1101111")