Count the numbers of a particular day
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
|