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

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
--------------
Jacob Skaria


"Francis" wrote:

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

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

"Francis" wrote:

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

On Sun, 22 Mar 2009 00:11:01 -0700, Francis
wrote:

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

Ron

this formula shows a result of 2555 on my Excel 2007

what am I doing wrong?


Uzytkownik "Ron Rosenfeld" napisal w wiadomosci
...
On Sun, 22 Mar 2009 00:11:01 -0700, Francis

wrote:

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

On Sun, 22 Mar 2009 11:38:45 +0100, "Jarek Kujawa" wrote:

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

thks but the dates were inserted correctly
still the same result

Uzytkownik "Ron Rosenfeld" napisal w wiadomosci
...
On Sun, 22 Mar 2009 11:38:45 +0100, "Jarek Kujawa"
wrote:

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

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



"Jacob Skaria" wrote:

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
--------------
Jacob Skaria


"Francis" wrote:

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

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



"Mike H" wrote:

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

"Francis" wrote:

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

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



"Ron Rosenfeld" wrote:

On Sun, 22 Mar 2009 00:11:01 -0700, Francis
wrote:

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

Hi,

Try this

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

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Francis" wrote:

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

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



"Shane Devenshire" wrote:

Hi,

Try this

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

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Francis" wrote:

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

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

--
Biff
Microsoft Excel MVP


"Jarek Kujawa" wrote in message
...
thks but the dates were inserted correctly
still the same result

Uzytkownik "Ron Rosenfeld" napisal w wiadomosci
...
On Sun, 22 Mar 2009 11:38:45 +0100, "Jarek Kujawa"
wrote:

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

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




"Francis" wrote in message
...
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



"Mike H" wrote:

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

"Francis" wrote:

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

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
--
Regards
Roger Govier

"Francis" wrote in message
...
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

On Mar 22, 10:36 am, Francis
wrote:
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.