Count the numbers of a particular day

[Francis]

Hi

What would be formula for calculating the number of Thursdays, excluding
Christmas and New Year days when they fall on Thursday, in a fiscal year
starting from - 1st Oct 2008 through 30th Sept 2009?

TIA

--
Hope this is helpful

Thank You

cheers, francis

 

[Jacob Skaria]

Dear Francis

This works for a fiscal year..

A1 = Startdate
B1 = EndDate

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&B1)))=5))-IF(WEEKDAY("1/1/" &
YEAR(B1))=5,2,0)


If this post helps click Yes

 

[Mike H]

Hi,

Try this array formula, dates in a1 & a2

=SUM(IF(WEEKDAY(A1-1+ROW(INDIRECT("1:"&TRUNC(A2-A1)+1)))=5,1,0))-IF(WEEKDAY(DATE(YEAR(A1),12,25))=5,2,0)

This is an array formula which must be entered with CTRL+Shift+Enter and NOT
'just enter. If you do it correctly then Excel will put curly brackets around
'the formula{}. You can't type these yourself. If you Edit the ranges
'then you must re-enter as An array


Mike

 

[Ron Rosenfeld]

Hi

What would be formula for calculating the number of Thursdays, excluding
Christmas and New Year days when they fall on Thursday, in a fiscal year
starting from - 1st Oct 2008 through 30th Sept 2009?

TIA

With

A1: 1 Oct 2008
A2: 30 Sep 2009

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&A2)))=5))

--ron

 

[Jarek Kujawa]

Ron

this formula shows a result of 2555 on my Excel 2007

what am I doing wrong?

 

[Ron Rosenfeld]

Ron

this formula shows a result of 2555 on my Excel 2007

what am I doing wrong?

Perhaps you don't have the correct dates in A1 or A2.

Format those cells to show you the long date, so there is no ambiguity.
--ron

 

[Jarek Kujawa]

thks but the dates were inserted correctly
still the same result

 

[Francis]

Hi Jacob
Thanks, this did it
if it not too much to ask for, would you mind elaborate how the formula works

TIA

--
Hope this is helpful

Pls click the Yes button below if this post provide answer you have asked

Thank You

cheers, francis

Am not a greek but an ordinary user trying to assist another

 

[Francis]

Hi Mike,

Thanks, its work great.
I would very much appreciate if you can spare some time elaborate how this
formula works but it fine if you are busy.

Your help is very much appreciates.
--
Hope this is helpful

Pls click the Yes button below if this post provide answer you have asked
Am not a greek but an ordinary user trying to help

Thank You

cheers, francis

Am not a greek but an ordinary user trying to assist another

 

[Francis]

Hi Ron

Thanks for your time.
I believe you have missed the 2 holidays, New Year and X'mas day,
as your formula return 52 Thursdays.


--
Hope this is helpful

Pls click the Yes button below if this post provide answer you have asked

Thank You

cheers, francis

Am not a greek but an ordinary user trying to assist another

 

[Shane Devenshire]

Hi,

Try this

=SUMPRODUCT(--(MOD(ROW(INDIRECT(A1&":"&B1)),7)=5))-2*(MOD(A1,7)=4)

 

[Francis]

Hi Shane

Thanks, its works

--
Hope this is helpful

Pls click the Yes button below if this post provide answer you have asked

Thank You

cheers, francis

Am not a greek but an ordinary user trying to assist another

 

[T. Valko]

In Excel 2007...using U.S. English regional date settings m/d/yyyy

A1 = 10/1/2008
A2 = 9/30/2009

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&A2)))=5))

The formula returns 52 which is correct

 

[Roger Govier]

Hi Francis

Thursday = Day 5 so try entering 5 in cell A1
=52-(WEEKDAY("25/12/2008")=A1)*2+(WEEKDAY("30/09/09")=A1)

Changing the value in A1 will give you the count for any other day of the
week

 

[ron]

Hi Ron

Thanks for your time.
I believe you have missed the 2 holidays, New Year and X'mas day,
as your formula return 52 Thursdays.

--

yes, you are correct. I overlooked that part of your requirements.
<sigh>

But others have supplied you with useful solutions.