example:
cell F1 is the start date 07-01-06
cell E1 is the end date 07-29-06
cell F3 is the answer 4
I just need to count the fridays between two dates
example:
cell F1 is the start date 07-01-06
cell E1 is the end date 07-29-06
cell F3 is the answer 4
I just need to count the fridays between two dates
Try
=SUM(IF(WEEKDAY(F2-1+ROW(INDIRECT("1:"&TRUNC(F3-F2)+1)))=6,1,0))
It's an array so need to press Crtl + Shitf + enter to work
VBA Noob
=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(F1&":"&E1)))=6))
--
HTH
Bob Phillips
(replace somewhere in email address with gmail if mailing direct)
"tiq" <[email protected]> wrote in message
news:[email protected]...
> example:
> cell F1 is the start date 07-01-06
> cell E1 is the end date 07-29-06
> cell F3 is the answer 4
>
> I just need to count the fridays between two dates
Here is an exerpt from Chip Pearson's Site...
*****************************************
Number Of Mondays In Period
If you need to return the number of Monday's (or any other day) that occur
within an interval between two dates, use the following Array Formula:
=SUM(IF(WEEKDAY(A2-1+ROW(INDIRECT("1:"&TRUNC(B2-A2)+1)))=C2,1,0))
This formula assumes the following:
A2 contains the beginning date of the interval
B2 contains the ending date of the interval
C2 contains the day-of-week number (1=Sunday, 2=Monday,...,7=Saturday)
*****************************************************
Being an Array formula it must be commited with Ctrl+Shift+Enter when it is
typed in...
Here is a link to Chip's site.
http://www.cpearson.com/excel/DateTimeWS.htm
--
HTH...
Jim Thomlinson
"tiq" wrote:
> example:
> cell F1 is the start date 07-01-06
> cell E1 is the end date 07-29-06
> cell F3 is the answer 4
>
> I just need to count the fridays between two dates
Mine's a bit more obvious Jim <vbg>
--
HTH
Bob Phillips
(replace somewhere in email address with gmail if mailing direct)
"Jim Thomlinson" <[email protected]> wrote in message
news:[email protected]...
> Here is an exerpt from Chip Pearson's Site...
> *****************************************
> Number Of Mondays In Period
>
> If you need to return the number of Monday's (or any other day) that occur
> within an interval between two dates, use the following Array Formula:
>
> =SUM(IF(WEEKDAY(A2-1+ROW(INDIRECT("1:"&TRUNC(B2-A2)+1)))=C2,1,0))
>
> This formula assumes the following:
> A2 contains the beginning date of the interval
> B2 contains the ending date of the interval
> C2 contains the day-of-week number (1=Sunday, 2=Monday,...,7=Saturday)
> *****************************************************
> Being an Array formula it must be commited with Ctrl+Shift+Enter when it
is
> typed in...
>
> Here is a link to Chip's site.
>
> http://www.cpearson.com/excel/DateTimeWS.htm
> --
> HTH...
>
> Jim Thomlinson
>
>
> "tiq" wrote:
>
> > example:
> > cell F1 is the start date 07-01-06
> > cell E1 is the end date 07-29-06
> > cell F3 is the answer 4
> >
> > I just need to count the fridays between two dates
Just because I like to make things difficult =)
=IF(6-WEEKDAY(F1)>-1, QUOTIENT((E1-F1-(6-WEEKDAY(F1))),7)+1, QUOTIENT((E1-F1-6), 7)+1)
Google is your best friend!
Almost intuitive. We should let Chip know...<vbg>
--
HTH...
Jim Thomlinson
"Bob Phillips" wrote:
> Mine's a bit more obvious Jim <vbg>
>
> --
> HTH
>
> Bob Phillips
>
> (replace somewhere in email address with gmail if mailing direct)
>
> "Jim Thomlinson" <[email protected]> wrote in message
> news:[email protected]...
> > Here is an exerpt from Chip Pearson's Site...
> > *****************************************
> > Number Of Mondays In Period
> >
> > If you need to return the number of Monday's (or any other day) that occur
> > within an interval between two dates, use the following Array Formula:
> >
> > =SUM(IF(WEEKDAY(A2-1+ROW(INDIRECT("1:"&TRUNC(B2-A2)+1)))=C2,1,0))
> >
> > This formula assumes the following:
> > A2 contains the beginning date of the interval
> > B2 contains the ending date of the interval
> > C2 contains the day-of-week number (1=Sunday, 2=Monday,...,7=Saturday)
> > *****************************************************
> > Being an Array formula it must be commited with Ctrl+Shift+Enter when it
> is
> > typed in...
> >
> > Here is a link to Chip's site.
> >
> > http://www.cpearson.com/excel/DateTimeWS.htm
> > --
> > HTH...
> >
> > Jim Thomlinson
> >
> >
> > "tiq" wrote:
> >
> > > example:
> > > cell F1 is the start date 07-01-06
> > > cell E1 is the end date 07-29-06
> > > cell F3 is the answer 4
> > >
> > > I just need to count the fridays between two dates
>
>
>
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks