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

 

[Mike H]

Hi,

A little bit late but you asked for an explanation of how the formula works.
The main part of the formula counts the Thursdays in the period between a1 &
A2

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

to make it simpler we can get rid of the TRUNC bit on the assumption there
is no time part to the date because all that does is get rid of the time bit

=SUM(IF(WEEKDAY(A1-1+ROW(INDIRECT("1:"&(A2-A1)+1)))=5,1,0))

This bit of that formula produces an array of weekday numbers from the first
date to the last so if we take a shorter periof of (say) 10 days it looks
like this
WEEKDAY(A1-1+ROW(INDIRECT("1:"&(A2-A1)+1)))
{4;5;6;7;1;2;3;4;5;6}

Thursday in Excel is 5 and if you look at the formula we say
Sum(if(the formula bit)=5,1,0

so for every time it encounters a 5 it adds a 1 and a zero for every other
day and we get our answer.

The last bit of the formula simply tests if Christmas day is Thursday. If it
is then so is NYD and 2 are subtracted
-IF(WEEKDAY(DATE(YEAR(A1),12,25))=5,2,0)

Mike

 

[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.