calculating number of days (e.g., Mondays) between two dates

=IF(MOD(TRUNC(K1-J1)+1,7)>MOD(MATCH(G1,{"Saturday","Sunday","Monday","Tuesday","Wednesday","Thursday","Friday"},FALSE)-1-INT(MOD(J1,7))+7,7),1,0)+INT((INT(K1-J1)+1)/7)+IF(MOD(TRUNC(K1-J1)+1,7)>MOD(MATCH(H1,{"Saturday","Sunday","Monday","Tuesday","Wednesday","Thursday","Friday"},FALSE)-1-INT(MOD(J1,7))+7,7),1,0)+INT((INT(K1-J1)+1)/7)+IF(MOD(TRUNC(K1-J1)+1,7)>MOD(MATCH(I1,{"Saturday","Sunday","Monday","Tuesday","Wednesday","Thursday","Friday"},FALSE)-1-INT(MOD(J1,7))+7,7),1,0)+INT((INT(K1-J1)+1)/7)

All in one cell - watch the line breaks. Written for row 1, but can be copied down. I'm sure
Harlan will come up with a much shorter formula - the array formula that I had that was one third
shorter didn't work with blanks, so I gave up on that approach.

HTH,
Bernie
MS Excel MVP


"girlfriend in school" <girlfriendinschool@discussions.microsoft.com> wrote in message
news:C5271A39-23D4-46D5-B3B4-72BD40B4F9E2@microsoft.com...
>I need to count the number of days between a start date and end date and the
> questions about this I found here don't seem to help (boggle me!) I need a
> universal formula that will cover variable days which are listed in a
> separate column:
> G (rows) = names of days; J (rows) = start date; K (rows) = end date. On
> top of this, I am sometimes having to count more than one day (up to three)
> within a week (e.g., Mondays AND Fridays) which I'd be willing to put in
> separate columns (i.e., columns G,H,I) if needed, but if only one column has
> a day listed, the formula needs to ignore the empty columns.
> A knotty problem?

Hi!

Actually, I've thought about the June 5 2079 row limitation many times but I
personally have never had to come up a formula that projects that far into
the future so I never bothered looking into a fix.

Maybe in one of the future versions of Excel MS will increase the number of
rows and solve that limitation for us!

Cheers

Biff

<Robert_Steel@nospam.com> wrote in message
news:OqIz9jFiFHA.2072@TK2MSFTNGP14.phx.gbl...
> Biff
> I like the use of the Row() function as you have used it in array
> formulas.
> However, and risking looking a bit picky...<g>
> when used in dates we have an iminent Y2K situation on the 5 June 2079.
> (the date value of the last row)
>
> A small fix you could employ
> SUMPRODUCT(--(WEEKDAY(A1-1+ROW(INDIRECT("1:"&B1-A1)),2)={1,2}))
> or in your final formula
>
> =IF(COUNT(N3:O3)<>2,"",SUMPRODUCT(--(WEEKDAY(N3-1+ROW(INDIRECT("1:"&O3-N3)),2)=MATCH(G3:I3,{"M","T","W","TH","F","SA","SU",0},0))))
>
> just using the Row() to increment the date not be the date.
>
> This is still not perfect as you can only have dates approx 179 years
> apart.
>
> hope it's of interest
> RES

You're welcome! Thanks for the feedback.

Biff

"girlfriend in school" <girlfriendinschool@discussions.microsoft.com> wrote
in message news:9A257400-F866-4EA4-92B3-3413071D7DE4@microsoft.com...
> Brilliant!
>
> I don't understand it, but it works! It looks like you maxed out the
> formulae for a cell so I will simply have to teach a clerk to fill down on
> the column when entering data.
>
> Thanks for your help.
>
>
>
> "Biff" wrote:
>
>> Hi!
>>
>> Let's see if we understand you.....
>>
>> Columns G, H and I, may or may not contain the letter abbreviations for
>> the
>> days of the week.
>>
>> >G3 H3 I3 N3 O3 Q3
>> >M F (blank) 10/9/05 12/15/05 (answer)
>>
>> So, based on your example above, you want to count the Mondays and
>> Fridays
>> between 10/9/2005 and 15/15/2005.
>>
>> Is that what you want?
>>
>> Try this.....
>>
>> Just make sure that the abbreviations you use match what are in this
>> formua:
>>
>> =IF(COUNT(N3:O3)<>2,"",SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(N3&":"&O3)),2)=MATCH(G3:I3,{"M","T","W","TH","F","SA","SU",0},0))))
>>
>> Biff
>>
>> "girlfriend in school" <girlfriendinschool@discussions.microsoft.com>
>> wrote
>> in message news:226F2179-C5DF-4133-B379-DE3653DF6D74@microsoft.com...
>> > This is great, but it doesn't factor the variability of the days tried
>> > to
>> > explain in my initial query. Data in columns G, H, and I will be days
>> > of
>> > week, but will change from row to row (as will the respective dates in
>> > columns N and O). Sometimes there will be no data in H and/or I. I am
>> > hoping to create something where a clerk can put in the days of the
>> > week
>> > and
>> > the spreadsheet formula will count up the days. Note: the number of
>> > days
>> > will then be used in another formula to calculated $$.
>> >
>> > Perhaps if I show you what I have so far will help:
>> >
>> > G3 H3 I3 N3 O3 Q3
>> > M F (blank) 10/9/05 12/15/05 (answer)
>> >
>> > I tried Bernie's formula but that didn't work. I had to change cell
>> > references, and I don't understand the formula (and cannot decipher
>> > Excel
>> > Help's explanation) so that might be why.
>> > I am currently using IF formulae to reference columns G, H and I into
>> > the
>> > weekday_number, but think I have exceeded the quantity of formulae one
>> > can
>> > put in a single cell.
>> >
>> > If you have an answer, WHAT A RELIEF!!!
>> >
>> > THX.
>> >
>> >
>> > "Biff" wrote:
>> >
>> >> Hi!
>> >>
>> >> After seeing Bernie's post I'm wondering if I understand the question!
>> >>
>> >> To count specific weekdays between 2 dates (inclusive):
>> >>
>> >> Where Monday = weekday 1 and Sunday = weekday 7
>> >>
>> >> Start date in A1
>> >> End date in B1
>> >>
>> >> =SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&B1)),2)=weekday_number))
>> >>
>> >> To count more than 1 weekday like Mondays and Tuesdays:
>> >>
>> >> =SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&B1)),2)={1,2}))
>> >>
>> >> Biff
>> >>
>> >> "girlfriend in school" <girlfriendinschool@discussions.microsoft.com>
>> >> wrote
>> >> in message news:C5271A39-23D4-46D5-B3B4-72BD40B4F9E2@microsoft.com...
>> >> >I need to count the number of days between a start date and end date
>> >> >and
>> >> >the
>> >> > questions about this I found here don't seem to help (boggle me!) I
>> >> > need
>> >> > a
>> >> > universal formula that will cover variable days which are listed in
>> >> > a
>> >> > separate column:
>> >> > G (rows) = names of days; J (rows) = start date; K (rows) = end
>> >> > date.
>> >> > On
>> >> > top of this, I am sometimes having to count more than one day (up to
>> >> > three)
>> >> > within a week (e.g., Mondays AND Fridays) which I'd be willing to
>> >> > put
>> >> > in
>> >> > separate columns (i.e., columns G,H,I) if needed, but if only one
>> >> > column
>> >> > has
>> >> > a day listed, the formula needs to ignore the empty columns.
>> >> > A knotty problem?
>> >>
>> >>
>> >>
>>
>>
>>

Brilliant!

I don't understand it, but it works! It looks like you maxed out the
formulae for a cell so I will simply have to teach a clerk to fill down on
the column when entering data.

Thanks for your help.



"Biff" wrote:

> Hi!
>
> Let's see if we understand you.....
>
> Columns G, H and I, may or may not contain the letter abbreviations for the
> days of the week.
>
> >G3 H3 I3 N3 O3 Q3
> >M F (blank) 10/9/05 12/15/05 (answer)
>
> So, based on your example above, you want to count the Mondays and Fridays
> between 10/9/2005 and 15/15/2005.
>
> Is that what you want?
>
> Try this.....
>
> Just make sure that the abbreviations you use match what are in this formua:
>
> =IF(COUNT(N3:O3)<>2,"",SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(N3&":"&O3)),2)=MATCH(G3:I3,{"M","T","W","TH","F","SA","SU",0},0))))
>
> Biff
>
> "girlfriend in school" <girlfriendinschool@discussions.microsoft.com> wrote
> in message news:226F2179-C5DF-4133-B379-DE3653DF6D74@microsoft.com...
> > This is great, but it doesn't factor the variability of the days tried to
> > explain in my initial query. Data in columns G, H, and I will be days of
> > week, but will change from row to row (as will the respective dates in
> > columns N and O). Sometimes there will be no data in H and/or I. I am
> > hoping to create something where a clerk can put in the days of the week
> > and
> > the spreadsheet formula will count up the days. Note: the number of days
> > will then be used in another formula to calculated $$.
> >
> > Perhaps if I show you what I have so far will help:
> >
> > G3 H3 I3 N3 O3 Q3
> > M F (blank) 10/9/05 12/15/05 (answer)
> >
> > I tried Bernie's formula but that didn't work. I had to change cell
> > references, and I don't understand the formula (and cannot decipher Excel
> > Help's explanation) so that might be why.
> > I am currently using IF formulae to reference columns G, H and I into the
> > weekday_number, but think I have exceeded the quantity of formulae one can
> > put in a single cell.
> >
> > If you have an answer, WHAT A RELIEF!!!
> >
> > THX.
> >
> >
> > "Biff" wrote:
> >
> >> Hi!
> >>
> >> After seeing Bernie's post I'm wondering if I understand the question!
> >>
> >> To count specific weekdays between 2 dates (inclusive):
> >>
> >> Where Monday = weekday 1 and Sunday = weekday 7
> >>
> >> Start date in A1
> >> End date in B1
> >>
> >> =SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&B1)),2)=weekday_number))
> >>
> >> To count more than 1 weekday like Mondays and Tuesdays:
> >>
> >> =SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&B1)),2)={1,2}))
> >>
> >> Biff
> >>
> >> "girlfriend in school" <girlfriendinschool@discussions.microsoft.com>
> >> wrote
> >> in message news:C5271A39-23D4-46D5-B3B4-72BD40B4F9E2@microsoft.com...
> >> >I need to count the number of days between a start date and end date and
> >> >the
> >> > questions about this I found here don't seem to help (boggle me!) I
> >> > need
> >> > a
> >> > universal formula that will cover variable days which are listed in a
> >> > separate column:
> >> > G (rows) = names of days; J (rows) = start date; K (rows) = end date.
> >> > On
> >> > top of this, I am sometimes having to count more than one day (up to
> >> > three)
> >> > within a week (e.g., Mondays AND Fridays) which I'd be willing to put
> >> > in
> >> > separate columns (i.e., columns G,H,I) if needed, but if only one
> >> > column
> >> > has
> >> > a day listed, the formula needs to ignore the empty columns.
> >> > A knotty problem?
> >>
> >>
> >>
>
>
>

Biff
I like the use of the Row() function as you have used it in array
formulas.
However, and risking looking a bit picky...<g>
when used in dates we have an iminent Y2K situation on the 5 June 2079.
(the date value of the last row)

A small fix you could employ
SUMPRODUCT(--(WEEKDAY(A1-1+ROW(INDIRECT("1:"&B1-A1)),2)={1,2}))
or in your final formula

=IF(COUNT(N3:O3)<>2,"",SUMPRODUCT(--(WEEKDAY(N3-1+ROW(INDIRECT("1:"&O3-N3)),2)=MATCH(G3:I3,{"M","T","W","TH","F","SA","SU",0},0))))

just using the Row() to increment the date not be the date.

This is still not perfect as you can only have dates approx 179 years
apart.

hope it's of interest
RES

Hi!

Let's see if we understand you.....

Columns G, H and I, may or may not contain the letter abbreviations for the
days of the week.

>G3 H3 I3 N3 O3 Q3
>M F (blank) 10/9/05 12/15/05 (answer)

So, based on your example above, you want to count the Mondays and Fridays
between 10/9/2005 and 15/15/2005.

Is that what you want?

Try this.....

Just make sure that the abbreviations you use match what are in this formua:

=IF(COUNT(N3:O3)<>2,"",SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(N3&":"&O3)),2)=MATCH(G3:I3,{"M","T","W","TH","F","SA","SU",0},0))))

Biff

"girlfriend in school" <girlfriendinschool@discussions.microsoft.com> wrote
in message news:226F2179-C5DF-4133-B379-DE3653DF6D74@microsoft.com...
> This is great, but it doesn't factor the variability of the days tried to
> explain in my initial query. Data in columns G, H, and I will be days of
> week, but will change from row to row (as will the respective dates in
> columns N and O). Sometimes there will be no data in H and/or I. I am
> hoping to create something where a clerk can put in the days of the week
> and
> the spreadsheet formula will count up the days. Note: the number of days
> will then be used in another formula to calculated $$.
>
> Perhaps if I show you what I have so far will help:
>
> G3 H3 I3 N3 O3 Q3
> M F (blank) 10/9/05 12/15/05 (answer)
>
> I tried Bernie's formula but that didn't work. I had to change cell
> references, and I don't understand the formula (and cannot decipher Excel
> Help's explanation) so that might be why.
> I am currently using IF formulae to reference columns G, H and I into the
> weekday_number, but think I have exceeded the quantity of formulae one can
> put in a single cell.
>
> If you have an answer, WHAT A RELIEF!!!
>
> THX.
>
>
> "Biff" wrote:
>
>> Hi!
>>
>> After seeing Bernie's post I'm wondering if I understand the question!
>>
>> To count specific weekdays between 2 dates (inclusive):
>>
>> Where Monday = weekday 1 and Sunday = weekday 7
>>
>> Start date in A1
>> End date in B1
>>
>> =SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&B1)),2)=weekday_number))
>>
>> To count more than 1 weekday like Mondays and Tuesdays:
>>
>> =SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&B1)),2)={1,2}))
>>
>> Biff
>>
>> "girlfriend in school" <girlfriendinschool@discussions.microsoft.com>
>> wrote
>> in message news:C5271A39-23D4-46D5-B3B4-72BD40B4F9E2@microsoft.com...
>> >I need to count the number of days between a start date and end date and
>> >the
>> > questions about this I found here don't seem to help (boggle me!) I
>> > need
>> > a
>> > universal formula that will cover variable days which are listed in a
>> > separate column:
>> > G (rows) = names of days; J (rows) = start date; K (rows) = end date.
>> > On
>> > top of this, I am sometimes having to count more than one day (up to
>> > three)
>> > within a week (e.g., Mondays AND Fridays) which I'd be willing to put
>> > in
>> > separate columns (i.e., columns G,H,I) if needed, but if only one
>> > column
>> > has
>> > a day listed, the formula needs to ignore the empty columns.
>> > A knotty problem?
>>
>>
>>

This is great, but it doesn’t factor the variability of the days tried to
explain in my initial query. Data in columns G, H, and I will be days of
week, but will change from row to row (as will the respective dates in
columns N and O). Sometimes there will be no data in H and/or I. I am
hoping to create something where a clerk can put in the days of the week and
the spreadsheet formula will count up the days. Note: the number of days
will then be used in another formula to calculated $$.

Perhaps if I show you what I have so far will help:

G3 H3 I3 N3 O3 Q3
M F (blank) 10/9/05 12/15/05 (answer)

I tried Bernie’s formula but that didn’t work. I had to change cell
references, and I don’t understand the formula (and cannot decipher Excel
Help’s explanation) so that might be why.
I am currently using IF formulae to reference columns G, H and I into the
weekday_number, but think I have exceeded the quantity of formulae one can
put in a single cell.

If you have an answer, WHAT A RELIEF!!!

THX.


"Biff" wrote:

> Hi!
>
> After seeing Bernie's post I'm wondering if I understand the question!
>
> To count specific weekdays between 2 dates (inclusive):
>
> Where Monday = weekday 1 and Sunday = weekday 7
>
> Start date in A1
> End date in B1
>
> =SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&B1)),2)=weekday_number))
>
> To count more than 1 weekday like Mondays and Tuesdays:
>
> =SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&B1)),2)={1,2}))
>
> Biff
>
> "girlfriend in school" <girlfriendinschool@discussions.microsoft.com> wrote
> in message news:C5271A39-23D4-46D5-B3B4-72BD40B4F9E2@microsoft.com...
> >I need to count the number of days between a start date and end date and
> >the
> > questions about this I found here don't seem to help (boggle me!) I need
> > a
> > universal formula that will cover variable days which are listed in a
> > separate column:
> > G (rows) = names of days; J (rows) = start date; K (rows) = end date. On
> > top of this, I am sometimes having to count more than one day (up to
> > three)
> > within a week (e.g., Mondays AND Fridays) which I'd be willing to put in
> > separate columns (i.e., columns G,H,I) if needed, but if only one column
> > has
> > a day listed, the formula needs to ignore the empty columns.
> > A knotty problem?
>
>
>

Hi!

After seeing Bernie's post I'm wondering if I understand the question!

To count specific weekdays between 2 dates (inclusive):

Where Monday = weekday 1 and Sunday = weekday 7

Start date in A1
End date in B1

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&B1)),2)=weekday_number))

To count more than 1 weekday like Mondays and Tuesdays:

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&B1)),2)={1,2}))

Biff

"girlfriend in school" <girlfriendinschool@discussions.microsoft.com> wrote
in message news:C5271A39-23D4-46D5-B3B4-72BD40B4F9E2@microsoft.com...
>I need to count the number of days between a start date and end date and
>the
> questions about this I found here don't seem to help (boggle me!) I need
> a
> universal formula that will cover variable days which are listed in a
> separate column:
> G (rows) = names of days; J (rows) = start date; K (rows) = end date. On
> top of this, I am sometimes having to count more than one day (up to
> three)
> within a week (e.g., Mondays AND Fridays) which I'd be willing to put in
> separate columns (i.e., columns G,H,I) if needed, but if only one column
> has
> a day listed, the formula needs to ignore the empty columns.
> A knotty problem?

That one only worked if all three days were needed. Try this version instead:

=IF(G1<>"",IF(MOD(TRUNC(K1-J1)+1,7)>MOD(MATCH(G1,{"Saturday","Sunday","Monday","Tuesday","Wednesday","Thursday","Friday"},FALSE)-1-INT(MOD(J1,7))+7,7),1,0)+INT((INT(K1-J1)+1)/7))+IF(H1<>"",IF(MOD(TRUNC(K1-J1)+1,7)>MOD(MATCH(H1,{"Saturday","Sunday","Monday","Tuesday","Wednesday","Thursday","Friday"},FALSE)-1-INT(MOD(J1,7))+7,7),1,0)+INT((INT(K1-J1)+1)/7))+IF(I1<>"",IF(MOD(TRUNC(K1-J1)+1,7)>MOD(MATCH(I1,{"Saturday","Sunday","Monday","Tuesday","Wednesday","Thursday","Friday"},FALSE)-1-INT(MOD(J1,7))+7,7),1,0)+INT((INT(K1-J1)+1)/7))

--
HTH,
Bernie
MS Excel MVP


"Bernie Deitrick" <deitbe @ consumer dot org> wrote in message
news:eiT7KE%23hFHA.1232@TK2MSFTNGP15.phx.gbl...
> =IF(MOD(TRUNC(K1-J1)+1,7)>MOD(MATCH(G1,{"Saturday","Sunday","Monday","Tuesday","Wednesday","Thursday","Friday"},FALSE)-1-INT(MOD(J1,7))+7,7),1,0)+INT((INT(K1-J1)+1)/7)+IF(MOD(TRUNC(K1-J1)+1,7)>MOD(MATCH(H1,{"Saturday","Sunday","Monday","Tuesday","Wednesday","Thursday","Friday"},FALSE)-1-INT(MOD(J1,7))+7,7),1,0)+INT((INT(K1-J1)+1)/7)+IF(MOD(TRUNC(K1-J1)+1,7)>MOD(MATCH(I1,{"Saturday","Sunday","Monday","Tuesday","Wednesday","Thursday","Friday"},FALSE)-1-INT(MOD(J1,7))+7,7),1,0)+INT((INT(K1-J1)+1)/7)
>
> All in one cell - watch the line breaks. Written for row 1, but can be copied down. I'm sure
> Harlan will come up with a much shorter formula - the array formula that I had that was one third
> shorter didn't work with blanks, so I gave up on that approach.
>
> HTH,
> Bernie
> MS Excel MVP
>
>
> "girlfriend in school" <girlfriendinschool@discussions.microsoft.com> wrote in message
> news:C5271A39-23D4-46D5-B3B4-72BD40B4F9E2@microsoft.com...
>>I need to count the number of days between a start date and end date and the
>> questions about this I found here don't seem to help (boggle me!) I need a
>> universal formula that will cover variable days which are listed in a
>> separate column:
>> G (rows) = names of days; J (rows) = start date; K (rows) = end date. On
>> top of this, I am sometimes having to count more than one day (up to three)
>> within a week (e.g., Mondays AND Fridays) which I'd be willing to put in
>> separate columns (i.e., columns G,H,I) if needed, but if only one column has
>> a day listed, the formula needs to ignore the empty columns.
>> A knotty problem?
>
>

Biff,

Neat formula! - converting the dates to row numbers was very insightful.

Bernie

> After seeing Bernie's post I'm wondering if I understand the question!

Hi!

Actually, I've thought about the June 5 2079 row limitation many times but I
personally have never had to come up a formula that projects that far into
the future so I never bothered looking into a fix.

Maybe in one of the future versions of Excel MS will increase the number of
rows and solve that limitation for us!

Cheers

Biff

<Robert_Steel@nospam.com> wrote in message
news:OqIz9jFiFHA.2072@TK2MSFTNGP14.phx.gbl...
> Biff
> I like the use of the Row() function as you have used it in array
> formulas.
> However, and risking looking a bit picky...<g>
> when used in dates we have an iminent Y2K situation on the 5 June 2079.
> (the date value of the last row)
>
> A small fix you could employ
> SUMPRODUCT(--(WEEKDAY(A1-1+ROW(INDIRECT("1:"&B1-A1)),2)={1,2}))
> or in your final formula
>
> =IF(COUNT(N3:O3)<>2,"",SUMPRODUCT(--(WEEKDAY(N3-1+ROW(INDIRECT("1:"&O3-N3)),2)=MATCH(G3:I3,{"M","T","W","TH","F","SA","SU",0},0))))
>
> just using the Row() to increment the date not be the date.
>
> This is still not perfect as you can only have dates approx 179 years
> apart.
>
> hope it's of interest
> RES

You're welcome! Thanks for the feedback.

Biff

"girlfriend in school" <girlfriendinschool@discussions.microsoft.com> wrote
in message news:9A257400-F866-4EA4-92B3-3413071D7DE4@microsoft.com...
> Brilliant!
>
> I don't understand it, but it works! It looks like you maxed out the
> formulae for a cell so I will simply have to teach a clerk to fill down on
> the column when entering data.
>
> Thanks for your help.
>
>
>
> "Biff" wrote:
>
>> Hi!
>>
>> Let's see if we understand you.....
>>
>> Columns G, H and I, may or may not contain the letter abbreviations for
>> the
>> days of the week.
>>
>> >G3 H3 I3 N3 O3 Q3
>> >M F (blank) 10/9/05 12/15/05 (answer)
>>
>> So, based on your example above, you want to count the Mondays and
>> Fridays
>> between 10/9/2005 and 15/15/2005.
>>
>> Is that what you want?
>>
>> Try this.....
>>
>> Just make sure that the abbreviations you use match what are in this
>> formua:
>>
>> =IF(COUNT(N3:O3)<>2,"",SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(N3&":"&O3)),2)=MATCH(G3:I3,{"M","T","W","TH","F","SA","SU",0},0))))
>>
>> Biff
>>
>> "girlfriend in school" <girlfriendinschool@discussions.microsoft.com>
>> wrote
>> in message news:226F2179-C5DF-4133-B379-DE3653DF6D74@microsoft.com...
>> > This is great, but it doesn't factor the variability of the days tried
>> > to
>> > explain in my initial query. Data in columns G, H, and I will be days
>> > of
>> > week, but will change from row to row (as will the respective dates in
>> > columns N and O). Sometimes there will be no data in H and/or I. I am
>> > hoping to create something where a clerk can put in the days of the
>> > week
>> > and
>> > the spreadsheet formula will count up the days. Note: the number of
>> > days
>> > will then be used in another formula to calculated $$.
>> >
>> > Perhaps if I show you what I have so far will help:
>> >
>> > G3 H3 I3 N3 O3 Q3
>> > M F (blank) 10/9/05 12/15/05 (answer)
>> >
>> > I tried Bernie's formula but that didn't work. I had to change cell
>> > references, and I don't understand the formula (and cannot decipher
>> > Excel
>> > Help's explanation) so that might be why.
>> > I am currently using IF formulae to reference columns G, H and I into
>> > the
>> > weekday_number, but think I have exceeded the quantity of formulae one
>> > can
>> > put in a single cell.
>> >
>> > If you have an answer, WHAT A RELIEF!!!
>> >
>> > THX.
>> >
>> >
>> > "Biff" wrote:
>> >
>> >> Hi!
>> >>
>> >> After seeing Bernie's post I'm wondering if I understand the question!
>> >>
>> >> To count specific weekdays between 2 dates (inclusive):
>> >>
>> >> Where Monday = weekday 1 and Sunday = weekday 7
>> >>
>> >> Start date in A1
>> >> End date in B1
>> >>
>> >> =SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&B1)),2)=weekday_number))
>> >>
>> >> To count more than 1 weekday like Mondays and Tuesdays:
>> >>
>> >> =SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&B1)),2)={1,2}))
>> >>
>> >> Biff
>> >>
>> >> "girlfriend in school" <girlfriendinschool@discussions.microsoft.com>
>> >> wrote
>> >> in message news:C5271A39-23D4-46D5-B3B4-72BD40B4F9E2@microsoft.com...
>> >> >I need to count the number of days between a start date and end date
>> >> >and
>> >> >the
>> >> > questions about this I found here don't seem to help (boggle me!) I
>> >> > need
>> >> > a
>> >> > universal formula that will cover variable days which are listed in
>> >> > a
>> >> > separate column:
>> >> > G (rows) = names of days; J (rows) = start date; K (rows) = end
>> >> > date.
>> >> > On
>> >> > top of this, I am sometimes having to count more than one day (up to
>> >> > three)
>> >> > within a week (e.g., Mondays AND Fridays) which I'd be willing to
>> >> > put
>> >> > in
>> >> > separate columns (i.e., columns G,H,I) if needed, but if only one
>> >> > column
>> >> > has
>> >> > a day listed, the formula needs to ignore the empty columns.
>> >> > A knotty problem?
>> >>
>> >>
>> >>
>>
>>
>>

Brilliant!

I don't understand it, but it works! It looks like you maxed out the
formulae for a cell so I will simply have to teach a clerk to fill down on
the column when entering data.

Thanks for your help.



"Biff" wrote:

> Hi!
>
> Let's see if we understand you.....
>
> Columns G, H and I, may or may not contain the letter abbreviations for the
> days of the week.
>
> >G3 H3 I3 N3 O3 Q3
> >M F (blank) 10/9/05 12/15/05 (answer)
>
> So, based on your example above, you want to count the Mondays and Fridays
> between 10/9/2005 and 15/15/2005.
>
> Is that what you want?
>
> Try this.....
>
> Just make sure that the abbreviations you use match what are in this formua:
>
> =IF(COUNT(N3:O3)<>2,"",SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(N3&":"&O3)),2)=MATCH(G3:I3,{"M","T","W","TH","F","SA","SU",0},0))))
>
> Biff
>
> "girlfriend in school" <girlfriendinschool@discussions.microsoft.com> wrote
> in message news:226F2179-C5DF-4133-B379-DE3653DF6D74@microsoft.com...
> > This is great, but it doesn't factor the variability of the days tried to
> > explain in my initial query. Data in columns G, H, and I will be days of
> > week, but will change from row to row (as will the respective dates in
> > columns N and O). Sometimes there will be no data in H and/or I. I am
> > hoping to create something where a clerk can put in the days of the week
> > and
> > the spreadsheet formula will count up the days. Note: the number of days
> > will then be used in another formula to calculated $$.
> >
> > Perhaps if I show you what I have so far will help:
> >
> > G3 H3 I3 N3 O3 Q3
> > M F (blank) 10/9/05 12/15/05 (answer)
> >
> > I tried Bernie's formula but that didn't work. I had to change cell
> > references, and I don't understand the formula (and cannot decipher Excel
> > Help's explanation) so that might be why.
> > I am currently using IF formulae to reference columns G, H and I into the
> > weekday_number, but think I have exceeded the quantity of formulae one can
> > put in a single cell.
> >
> > If you have an answer, WHAT A RELIEF!!!
> >
> > THX.
> >
> >
> > "Biff" wrote:
> >
> >> Hi!
> >>
> >> After seeing Bernie's post I'm wondering if I understand the question!
> >>
> >> To count specific weekdays between 2 dates (inclusive):
> >>
> >> Where Monday = weekday 1 and Sunday = weekday 7
> >>
> >> Start date in A1
> >> End date in B1
> >>
> >> =SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&B1)),2)=weekday_number))
> >>
> >> To count more than 1 weekday like Mondays and Tuesdays:
> >>
> >> =SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&B1)),2)={1,2}))
> >>
> >> Biff
> >>
> >> "girlfriend in school" <girlfriendinschool@discussions.microsoft.com>
> >> wrote
> >> in message news:C5271A39-23D4-46D5-B3B4-72BD40B4F9E2@microsoft.com...
> >> >I need to count the number of days between a start date and end date and
> >> >the
> >> > questions about this I found here don't seem to help (boggle me!) I
> >> > need
> >> > a
> >> > universal formula that will cover variable days which are listed in a
> >> > separate column:
> >> > G (rows) = names of days; J (rows) = start date; K (rows) = end date.
> >> > On
> >> > top of this, I am sometimes having to count more than one day (up to
> >> > three)
> >> > within a week (e.g., Mondays AND Fridays) which I'd be willing to put
> >> > in
> >> > separate columns (i.e., columns G,H,I) if needed, but if only one
> >> > column
> >> > has
> >> > a day listed, the formula needs to ignore the empty columns.
> >> > A knotty problem?
> >>
> >>
> >>
>
>
>

Biff
I like the use of the Row() function as you have used it in array
formulas.
However, and risking looking a bit picky...<g>
when used in dates we have an iminent Y2K situation on the 5 June 2079.
(the date value of the last row)

A small fix you could employ
SUMPRODUCT(--(WEEKDAY(A1-1+ROW(INDIRECT("1:"&B1-A1)),2)={1,2}))
or in your final formula

=IF(COUNT(N3:O3)<>2,"",SUMPRODUCT(--(WEEKDAY(N3-1+ROW(INDIRECT("1:"&O3-N3)),2)=MATCH(G3:I3,{"M","T","W","TH","F","SA","SU",0},0))))

just using the Row() to increment the date not be the date.

This is still not perfect as you can only have dates approx 179 years
apart.

hope it's of interest
RES

Hi!

Let's see if we understand you.....

Columns G, H and I, may or may not contain the letter abbreviations for the
days of the week.

>G3 H3 I3 N3 O3 Q3
>M F (blank) 10/9/05 12/15/05 (answer)

So, based on your example above, you want to count the Mondays and Fridays
between 10/9/2005 and 15/15/2005.

Is that what you want?

Try this.....

Just make sure that the abbreviations you use match what are in this formua:

=IF(COUNT(N3:O3)<>2,"",SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(N3&":"&O3)),2)=MATCH(G3:I3,{"M","T","W","TH","F","SA","SU",0},0))))

Biff

"girlfriend in school" <girlfriendinschool@discussions.microsoft.com> wrote
in message news:226F2179-C5DF-4133-B379-DE3653DF6D74@microsoft.com...
> This is great, but it doesn't factor the variability of the days tried to
> explain in my initial query. Data in columns G, H, and I will be days of
> week, but will change from row to row (as will the respective dates in
> columns N and O). Sometimes there will be no data in H and/or I. I am
> hoping to create something where a clerk can put in the days of the week
> and
> the spreadsheet formula will count up the days. Note: the number of days
> will then be used in another formula to calculated $$.
>
> Perhaps if I show you what I have so far will help:
>
> G3 H3 I3 N3 O3 Q3
> M F (blank) 10/9/05 12/15/05 (answer)
>
> I tried Bernie's formula but that didn't work. I had to change cell
> references, and I don't understand the formula (and cannot decipher Excel
> Help's explanation) so that might be why.
> I am currently using IF formulae to reference columns G, H and I into the
> weekday_number, but think I have exceeded the quantity of formulae one can
> put in a single cell.
>
> If you have an answer, WHAT A RELIEF!!!
>
> THX.
>
>
> "Biff" wrote:
>
>> Hi!
>>
>> After seeing Bernie's post I'm wondering if I understand the question!
>>
>> To count specific weekdays between 2 dates (inclusive):
>>
>> Where Monday = weekday 1 and Sunday = weekday 7
>>
>> Start date in A1
>> End date in B1
>>
>> =SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&B1)),2)=weekday_number))
>>
>> To count more than 1 weekday like Mondays and Tuesdays:
>>
>> =SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&B1)),2)={1,2}))
>>
>> Biff
>>
>> "girlfriend in school" <girlfriendinschool@discussions.microsoft.com>
>> wrote
>> in message news:C5271A39-23D4-46D5-B3B4-72BD40B4F9E2@microsoft.com...
>> >I need to count the number of days between a start date and end date and
>> >the
>> > questions about this I found here don't seem to help (boggle me!) I
>> > need
>> > a
>> > universal formula that will cover variable days which are listed in a
>> > separate column:
>> > G (rows) = names of days; J (rows) = start date; K (rows) = end date.
>> > On
>> > top of this, I am sometimes having to count more than one day (up to
>> > three)
>> > within a week (e.g., Mondays AND Fridays) which I'd be willing to put
>> > in
>> > separate columns (i.e., columns G,H,I) if needed, but if only one
>> > column
>> > has
>> > a day listed, the formula needs to ignore the empty columns.
>> > A knotty problem?
>>
>>
>>

This is great, but it doesn’t factor the variability of the days tried to
explain in my initial query. Data in columns G, H, and I will be days of
week, but will change from row to row (as will the respective dates in
columns N and O). Sometimes there will be no data in H and/or I. I am
hoping to create something where a clerk can put in the days of the week and
the spreadsheet formula will count up the days. Note: the number of days
will then be used in another formula to calculated $$.

Perhaps if I show you what I have so far will help:

G3 H3 I3 N3 O3 Q3
M F (blank) 10/9/05 12/15/05 (answer)

I tried Bernie’s formula but that didn’t work. I had to change cell
references, and I don’t understand the formula (and cannot decipher Excel
Help’s explanation) so that might be why.
I am currently using IF formulae to reference columns G, H and I into the
weekday_number, but think I have exceeded the quantity of formulae one can
put in a single cell.

If you have an answer, WHAT A RELIEF!!!

THX.


"Biff" wrote:

> Hi!
>
> After seeing Bernie's post I'm wondering if I understand the question!
>
> To count specific weekdays between 2 dates (inclusive):
>
> Where Monday = weekday 1 and Sunday = weekday 7
>
> Start date in A1
> End date in B1
>
> =SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&B1)),2)=weekday_number))
>
> To count more than 1 weekday like Mondays and Tuesdays:
>
> =SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&B1)),2)={1,2}))
>
> Biff
>
> "girlfriend in school" <girlfriendinschool@discussions.microsoft.com> wrote
> in message news:C5271A39-23D4-46D5-B3B4-72BD40B4F9E2@microsoft.com...
> >I need to count the number of days between a start date and end date and
> >the
> > questions about this I found here don't seem to help (boggle me!) I need
> > a
> > universal formula that will cover variable days which are listed in a
> > separate column:
> > G (rows) = names of days; J (rows) = start date; K (rows) = end date. On
> > top of this, I am sometimes having to count more than one day (up to
> > three)
> > within a week (e.g., Mondays AND Fridays) which I'd be willing to put in
> > separate columns (i.e., columns G,H,I) if needed, but if only one column
> > has
> > a day listed, the formula needs to ignore the empty columns.
> > A knotty problem?
>
>
>

Biff,

Neat formula! - converting the dates to row numbers was very insightful.

Bernie

> After seeing Bernie's post I'm wondering if I understand the question!

Hi!

After seeing Bernie's post I'm wondering if I understand the question!

To count specific weekdays between 2 dates (inclusive):

Where Monday = weekday 1 and Sunday = weekday 7

Start date in A1
End date in B1

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&B1)),2)=weekday_number))

To count more than 1 weekday like Mondays and Tuesdays:

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&B1)),2)={1,2}))

Biff

"girlfriend in school" <girlfriendinschool@discussions.microsoft.com> wrote
in message news:C5271A39-23D4-46D5-B3B4-72BD40B4F9E2@microsoft.com...
>I need to count the number of days between a start date and end date and
>the
> questions about this I found here don't seem to help (boggle me!) I need
> a
> universal formula that will cover variable days which are listed in a
> separate column:
> G (rows) = names of days; J (rows) = start date; K (rows) = end date. On
> top of this, I am sometimes having to count more than one day (up to
> three)
> within a week (e.g., Mondays AND Fridays) which I'd be willing to put in
> separate columns (i.e., columns G,H,I) if needed, but if only one column
> has
> a day listed, the formula needs to ignore the empty columns.
> A knotty problem?

That one only worked if all three days were needed. Try this version instead:

=IF(G1<>"",IF(MOD(TRUNC(K1-J1)+1,7)>MOD(MATCH(G1,{"Saturday","Sunday","Monday","Tuesday","Wednesday","Thursday","Friday"},FALSE)-1-INT(MOD(J1,7))+7,7),1,0)+INT((INT(K1-J1)+1)/7))+IF(H1<>"",IF(MOD(TRUNC(K1-J1)+1,7)>MOD(MATCH(H1,{"Saturday","Sunday","Monday","Tuesday","Wednesday","Thursday","Friday"},FALSE)-1-INT(MOD(J1,7))+7,7),1,0)+INT((INT(K1-J1)+1)/7))+IF(I1<>"",IF(MOD(TRUNC(K1-J1)+1,7)>MOD(MATCH(I1,{"Saturday","Sunday","Monday","Tuesday","Wednesday","Thursday","Friday"},FALSE)-1-INT(MOD(J1,7))+7,7),1,0)+INT((INT(K1-J1)+1)/7))

--
HTH,
Bernie
MS Excel MVP


"Bernie Deitrick" <deitbe @ consumer dot org> wrote in message
news:eiT7KE%23hFHA.1232@TK2MSFTNGP15.phx.gbl...
> =IF(MOD(TRUNC(K1-J1)+1,7)>MOD(MATCH(G1,{"Saturday","Sunday","Monday","Tuesday","Wednesday","Thursday","Friday"},FALSE)-1-INT(MOD(J1,7))+7,7),1,0)+INT((INT(K1-J1)+1)/7)+IF(MOD(TRUNC(K1-J1)+1,7)>MOD(MATCH(H1,{"Saturday","Sunday","Monday","Tuesday","Wednesday","Thursday","Friday"},FALSE)-1-INT(MOD(J1,7))+7,7),1,0)+INT((INT(K1-J1)+1)/7)+IF(MOD(TRUNC(K1-J1)+1,7)>MOD(MATCH(I1,{"Saturday","Sunday","Monday","Tuesday","Wednesday","Thursday","Friday"},FALSE)-1-INT(MOD(J1,7))+7,7),1,0)+INT((INT(K1-J1)+1)/7)
>
> All in one cell - watch the line breaks. Written for row 1, but can be copied down. I'm sure
> Harlan will come up with a much shorter formula - the array formula that I had that was one third
> shorter didn't work with blanks, so I gave up on that approach.
>
> HTH,
> Bernie
> MS Excel MVP
>
>
> "girlfriend in school" <girlfriendinschool@discussions.microsoft.com> wrote in message
> news:C5271A39-23D4-46D5-B3B4-72BD40B4F9E2@microsoft.com...
>>I need to count the number of days between a start date and end date and the
>> questions about this I found here don't seem to help (boggle me!) I need a
>> universal formula that will cover variable days which are listed in a
>> separate column:
>> G (rows) = names of days; J (rows) = start date; K (rows) = end date. On
>> top of this, I am sometimes having to count more than one day (up to three)
>> within a week (e.g., Mondays AND Fridays) which I'd be willing to put in
>> separate columns (i.e., columns G,H,I) if needed, but if only one column has
>> a day listed, the formula needs to ignore the empty columns.
>> A knotty problem?
>
>

=IF(MOD(TRUNC(K1-J1)+1,7)>MOD(MATCH(G1,{"Saturday","Sunday","Monday","Tuesday","Wednesday","Thursday","Friday"},FALSE)-1-INT(MOD(J1,7))+7,7),1,0)+INT((INT(K1-J1)+1)/7)+IF(MOD(TRUNC(K1-J1)+1,7)>MOD(MATCH(H1,{"Saturday","Sunday","Monday","Tuesday","Wednesday","Thursday","Friday"},FALSE)-1-INT(MOD(J1,7))+7,7),1,0)+INT((INT(K1-J1)+1)/7)+IF(MOD(TRUNC(K1-J1)+1,7)>MOD(MATCH(I1,{"Saturday","Sunday","Monday","Tuesday","Wednesday","Thursday","Friday"},FALSE)-1-INT(MOD(J1,7))+7,7),1,0)+INT((INT(K1-J1)+1)/7)

All in one cell - watch the line breaks. Written for row 1, but can be copied down. I'm sure
Harlan will come up with a much shorter formula - the array formula that I had that was one third
shorter didn't work with blanks, so I gave up on that approach.

HTH,
Bernie
MS Excel MVP


"girlfriend in school" <girlfriendinschool@discussions.microsoft.com> wrote in message
news:C5271A39-23D4-46D5-B3B4-72BD40B4F9E2@microsoft.com...
>I need to count the number of days between a start date and end date and the
> questions about this I found here don't seem to help (boggle me!) I need a
> universal formula that will cover variable days which are listed in a
> separate column:
> G (rows) = names of days; J (rows) = start date; K (rows) = end date. On
> top of this, I am sometimes having to count more than one day (up to three)
> within a week (e.g., Mondays AND Fridays) which I'd be willing to put in
> separate columns (i.e., columns G,H,I) if needed, but if only one column has
> a day listed, the formula needs to ignore the empty columns.
> A knotty problem?

I need to count the number of days between a start date and end date and the
questions about this I found here don't seem to help (boggle me!) I need a
universal formula that will cover variable days which are listed in a
separate column:
G (rows) = names of days; J (rows) = start date; K (rows) = end date. On
top of this, I am sometimes having to count more than one day (up to three)
within a week (e.g., Mondays AND Fridays) which I'd be willing to put in
separate columns (i.e., columns G,H,I) if needed, but if only one column has
a day listed, the formula needs to ignore the empty columns.
A knotty problem?

Hi!

Actually, I've thought about the June 5 2079 row limitation many times but I
personally have never had to come up a formula that projects that far into
the future so I never bothered looking into a fix.

Maybe in one of the future versions of Excel MS will increase the number of
rows and solve that limitation for us!

Cheers

Biff

<Robert_Steel@nospam.com> wrote in message
news:OqIz9jFiFHA.2072@TK2MSFTNGP14.phx.gbl...
> Biff
> I like the use of the Row() function as you have used it in array
> formulas.
> However, and risking looking a bit picky...<g>
> when used in dates we have an iminent Y2K situation on the 5 June 2079.
> (the date value of the last row)
>
> A small fix you could employ
> SUMPRODUCT(--(WEEKDAY(A1-1+ROW(INDIRECT("1:"&B1-A1)),2)={1,2}))
> or in your final formula
>
> =IF(COUNT(N3:O3)<>2,"",SUMPRODUCT(--(WEEKDAY(N3-1+ROW(INDIRECT("1:"&O3-N3)),2)=MATCH(G3:I3,{"M","T","W","TH","F","SA","SU",0},0))))
>
> just using the Row() to increment the date not be the date.
>
> This is still not perfect as you can only have dates approx 179 years
> apart.
>
> hope it's of interest
> RES

=IF(MOD(TRUNC(K1-J1)+1,7)>MOD(MATCH(G1,{"Saturday","Sunday","Monday","Tuesday","Wednesday","Thursday","Friday"},FALSE)-1-INT(MOD(J1,7))+7,7),1,0)+INT((INT(K1-J1)+1)/7)+IF(MOD(TRUNC(K1-J1)+1,7)>MOD(MATCH(H1,{"Saturday","Sunday","Monday","Tuesday","Wednesday","Thursday","Friday"},FALSE)-1-INT(MOD(J1,7))+7,7),1,0)+INT((INT(K1-J1)+1)/7)+IF(MOD(TRUNC(K1-J1)+1,7)>MOD(MATCH(I1,{"Saturday","Sunday","Monday","Tuesday","Wednesday","Thursday","Friday"},FALSE)-1-INT(MOD(J1,7))+7,7),1,0)+INT((INT(K1-J1)+1)/7)

All in one cell - watch the line breaks. Written for row 1, but can be copied down. I'm sure
Harlan will come up with a much shorter formula - the array formula that I had that was one third
shorter didn't work with blanks, so I gave up on that approach.

HTH,
Bernie
MS Excel MVP


"girlfriend in school" <girlfriendinschool@discussions.microsoft.com> wrote in message
news:C5271A39-23D4-46D5-B3B4-72BD40B4F9E2@microsoft.com...
>I need to count the number of days between a start date and end date and the
> questions about this I found here don't seem to help (boggle me!) I need a
> universal formula that will cover variable days which are listed in a
> separate column:
> G (rows) = names of days; J (rows) = start date; K (rows) = end date. On
> top of this, I am sometimes having to count more than one day (up to three)
> within a week (e.g., Mondays AND Fridays) which I'd be willing to put in
> separate columns (i.e., columns G,H,I) if needed, but if only one column has
> a day listed, the formula needs to ignore the empty columns.
> A knotty problem?

You're welcome! Thanks for the feedback.

Biff

"girlfriend in school" <girlfriendinschool@discussions.microsoft.com> wrote
in message news:9A257400-F866-4EA4-92B3-3413071D7DE4@microsoft.com...
> Brilliant!
>
> I don't understand it, but it works! It looks like you maxed out the
> formulae for a cell so I will simply have to teach a clerk to fill down on
> the column when entering data.
>
> Thanks for your help.
>
>
>
> "Biff" wrote:
>
>> Hi!
>>
>> Let's see if we understand you.....
>>
>> Columns G, H and I, may or may not contain the letter abbreviations for
>> the
>> days of the week.
>>
>> >G3 H3 I3 N3 O3 Q3
>> >M F (blank) 10/9/05 12/15/05 (answer)
>>
>> So, based on your example above, you want to count the Mondays and
>> Fridays
>> between 10/9/2005 and 15/15/2005.
>>
>> Is that what you want?
>>
>> Try this.....
>>
>> Just make sure that the abbreviations you use match what are in this
>> formua:
>>
>> =IF(COUNT(N3:O3)<>2,"",SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(N3&":"&O3)),2)=MATCH(G3:I3,{"M","T","W","TH","F","SA","SU",0},0))))
>>
>> Biff
>>
>> "girlfriend in school" <girlfriendinschool@discussions.microsoft.com>
>> wrote
>> in message news:226F2179-C5DF-4133-B379-DE3653DF6D74@microsoft.com...
>> > This is great, but it doesn't factor the variability of the days tried
>> > to
>> > explain in my initial query. Data in columns G, H, and I will be days
>> > of
>> > week, but will change from row to row (as will the respective dates in
>> > columns N and O). Sometimes there will be no data in H and/or I. I am
>> > hoping to create something where a clerk can put in the days of the
>> > week
>> > and
>> > the spreadsheet formula will count up the days. Note: the number of
>> > days
>> > will then be used in another formula to calculated $$.
>> >
>> > Perhaps if I show you what I have so far will help:
>> >
>> > G3 H3 I3 N3 O3 Q3
>> > M F (blank) 10/9/05 12/15/05 (answer)
>> >
>> > I tried Bernie's formula but that didn't work. I had to change cell
>> > references, and I don't understand the formula (and cannot decipher
>> > Excel
>> > Help's explanation) so that might be why.
>> > I am currently using IF formulae to reference columns G, H and I into
>> > the
>> > weekday_number, but think I have exceeded the quantity of formulae one
>> > can
>> > put in a single cell.
>> >
>> > If you have an answer, WHAT A RELIEF!!!
>> >
>> > THX.
>> >
>> >
>> > "Biff" wrote:
>> >
>> >> Hi!
>> >>
>> >> After seeing Bernie's post I'm wondering if I understand the question!
>> >>
>> >> To count specific weekdays between 2 dates (inclusive):
>> >>
>> >> Where Monday = weekday 1 and Sunday = weekday 7
>> >>
>> >> Start date in A1
>> >> End date in B1
>> >>
>> >> =SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&B1)),2)=weekday_number))
>> >>
>> >> To count more than 1 weekday like Mondays and Tuesdays:
>> >>
>> >> =SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&B1)),2)={1,2}))
>> >>
>> >> Biff
>> >>
>> >> "girlfriend in school" <girlfriendinschool@discussions.microsoft.com>
>> >> wrote
>> >> in message news:C5271A39-23D4-46D5-B3B4-72BD40B4F9E2@microsoft.com...
>> >> >I need to count the number of days between a start date and end date
>> >> >and
>> >> >the
>> >> > questions about this I found here don't seem to help (boggle me!) I
>> >> > need
>> >> > a
>> >> > universal formula that will cover variable days which are listed in
>> >> > a
>> >> > separate column:
>> >> > G (rows) = names of days; J (rows) = start date; K (rows) = end
>> >> > date.
>> >> > On
>> >> > top of this, I am sometimes having to count more than one day (up to
>> >> > three)
>> >> > within a week (e.g., Mondays AND Fridays) which I'd be willing to
>> >> > put
>> >> > in
>> >> > separate columns (i.e., columns G,H,I) if needed, but if only one
>> >> > column
>> >> > has
>> >> > a day listed, the formula needs to ignore the empty columns.
>> >> > A knotty problem?
>> >>
>> >>
>> >>
>>
>>
>>

Brilliant!

I don't understand it, but it works! It looks like you maxed out the
formulae for a cell so I will simply have to teach a clerk to fill down on
the column when entering data.

Thanks for your help.



"Biff" wrote:

> Hi!
>
> Let's see if we understand you.....
>
> Columns G, H and I, may or may not contain the letter abbreviations for the
> days of the week.
>
> >G3 H3 I3 N3 O3 Q3
> >M F (blank) 10/9/05 12/15/05 (answer)
>
> So, based on your example above, you want to count the Mondays and Fridays
> between 10/9/2005 and 15/15/2005.
>
> Is that what you want?
>
> Try this.....
>
> Just make sure that the abbreviations you use match what are in this formua:
>
> =IF(COUNT(N3:O3)<>2,"",SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(N3&":"&O3)),2)=MATCH(G3:I3,{"M","T","W","TH","F","SA","SU",0},0))))
>
> Biff
>
> "girlfriend in school" <girlfriendinschool@discussions.microsoft.com> wrote
> in message news:226F2179-C5DF-4133-B379-DE3653DF6D74@microsoft.com...
> > This is great, but it doesn't factor the variability of the days tried to
> > explain in my initial query. Data in columns G, H, and I will be days of
> > week, but will change from row to row (as will the respective dates in
> > columns N and O). Sometimes there will be no data in H and/or I. I am
> > hoping to create something where a clerk can put in the days of the week
> > and
> > the spreadsheet formula will count up the days. Note: the number of days
> > will then be used in another formula to calculated $$.
> >
> > Perhaps if I show you what I have so far will help:
> >
> > G3 H3 I3 N3 O3 Q3
> > M F (blank) 10/9/05 12/15/05 (answer)
> >
> > I tried Bernie's formula but that didn't work. I had to change cell
> > references, and I don't understand the formula (and cannot decipher Excel
> > Help's explanation) so that might be why.
> > I am currently using IF formulae to reference columns G, H and I into the
> > weekday_number, but think I have exceeded the quantity of formulae one can
> > put in a single cell.
> >
> > If you have an answer, WHAT A RELIEF!!!
> >
> > THX.
> >
> >
> > "Biff" wrote:
> >
> >> Hi!
> >>
> >> After seeing Bernie's post I'm wondering if I understand the question!
> >>
> >> To count specific weekdays between 2 dates (inclusive):
> >>
> >> Where Monday = weekday 1 and Sunday = weekday 7
> >>
> >> Start date in A1
> >> End date in B1
> >>
> >> =SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&B1)),2)=weekday_number))
> >>
> >> To count more than 1 weekday like Mondays and Tuesdays:
> >>
> >> =SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&B1)),2)={1,2}))
> >>
> >> Biff
> >>
> >> "girlfriend in school" <girlfriendinschool@discussions.microsoft.com>
> >> wrote
> >> in message news:C5271A39-23D4-46D5-B3B4-72BD40B4F9E2@microsoft.com...
> >> >I need to count the number of days between a start date and end date and
> >> >the
> >> > questions about this I found here don't seem to help (boggle me!) I
> >> > need
> >> > a
> >> > universal formula that will cover variable days which are listed in a
> >> > separate column:
> >> > G (rows) = names of days; J (rows) = start date; K (rows) = end date.
> >> > On
> >> > top of this, I am sometimes having to count more than one day (up to
> >> > three)
> >> > within a week (e.g., Mondays AND Fridays) which I'd be willing to put
> >> > in
> >> > separate columns (i.e., columns G,H,I) if needed, but if only one
> >> > column
> >> > has
> >> > a day listed, the formula needs to ignore the empty columns.
> >> > A knotty problem?
> >>
> >>
> >>
>
>
>

That one only worked if all three days were needed. Try this version instead:

=IF(G1<>"",IF(MOD(TRUNC(K1-J1)+1,7)>MOD(MATCH(G1,{"Saturday","Sunday","Monday","Tuesday","Wednesday","Thursday","Friday"},FALSE)-1-INT(MOD(J1,7))+7,7),1,0)+INT((INT(K1-J1)+1)/7))+IF(H1<>"",IF(MOD(TRUNC(K1-J1)+1,7)>MOD(MATCH(H1,{"Saturday","Sunday","Monday","Tuesday","Wednesday","Thursday","Friday"},FALSE)-1-INT(MOD(J1,7))+7,7),1,0)+INT((INT(K1-J1)+1)/7))+IF(I1<>"",IF(MOD(TRUNC(K1-J1)+1,7)>MOD(MATCH(I1,{"Saturday","Sunday","Monday","Tuesday","Wednesday","Thursday","Friday"},FALSE)-1-INT(MOD(J1,7))+7,7),1,0)+INT((INT(K1-J1)+1)/7))

--
HTH,
Bernie
MS Excel MVP


"Bernie Deitrick" <deitbe @ consumer dot org> wrote in message
news:eiT7KE%23hFHA.1232@TK2MSFTNGP15.phx.gbl...
> =IF(MOD(TRUNC(K1-J1)+1,7)>MOD(MATCH(G1,{"Saturday","Sunday","Monday","Tuesday","Wednesday","Thursday","Friday"},FALSE)-1-INT(MOD(J1,7))+7,7),1,0)+INT((INT(K1-J1)+1)/7)+IF(MOD(TRUNC(K1-J1)+1,7)>MOD(MATCH(H1,{"Saturday","Sunday","Monday","Tuesday","Wednesday","Thursday","Friday"},FALSE)-1-INT(MOD(J1,7))+7,7),1,0)+INT((INT(K1-J1)+1)/7)+IF(MOD(TRUNC(K1-J1)+1,7)>MOD(MATCH(I1,{"Saturday","Sunday","Monday","Tuesday","Wednesday","Thursday","Friday"},FALSE)-1-INT(MOD(J1,7))+7,7),1,0)+INT((INT(K1-J1)+1)/7)
>
> All in one cell - watch the line breaks. Written for row 1, but can be copied down. I'm sure
> Harlan will come up with a much shorter formula - the array formula that I had that was one third
> shorter didn't work with blanks, so I gave up on that approach.
>
> HTH,
> Bernie
> MS Excel MVP
>
>
> "girlfriend in school" <girlfriendinschool@discussions.microsoft.com> wrote in message
> news:C5271A39-23D4-46D5-B3B4-72BD40B4F9E2@microsoft.com...
>>I need to count the number of days between a start date and end date and the
>> questions about this I found here don't seem to help (boggle me!) I need a
>> universal formula that will cover variable days which are listed in a
>> separate column:
>> G (rows) = names of days; J (rows) = start date; K (rows) = end date. On
>> top of this, I am sometimes having to count more than one day (up to three)
>> within a week (e.g., Mondays AND Fridays) which I'd be willing to put in
>> separate columns (i.e., columns G,H,I) if needed, but if only one column has
>> a day listed, the formula needs to ignore the empty columns.
>> A knotty problem?
>
>

Biff
I like the use of the Row() function as you have used it in array
formulas.
However, and risking looking a bit picky...<g>
when used in dates we have an iminent Y2K situation on the 5 June 2079.
(the date value of the last row)

A small fix you could employ
SUMPRODUCT(--(WEEKDAY(A1-1+ROW(INDIRECT("1:"&B1-A1)),2)={1,2}))
or in your final formula

=IF(COUNT(N3:O3)<>2,"",SUMPRODUCT(--(WEEKDAY(N3-1+ROW(INDIRECT("1:"&O3-N3)),2)=MATCH(G3:I3,{"M","T","W","TH","F","SA","SU",0},0))))

just using the Row() to increment the date not be the date.

This is still not perfect as you can only have dates approx 179 years
apart.

hope it's of interest
RES

Hi!

Let's see if we understand you.....

Columns G, H and I, may or may not contain the letter abbreviations for the
days of the week.

>G3 H3 I3 N3 O3 Q3
>M F (blank) 10/9/05 12/15/05 (answer)

So, based on your example above, you want to count the Mondays and Fridays
between 10/9/2005 and 15/15/2005.

Is that what you want?

Try this.....

Just make sure that the abbreviations you use match what are in this formua:

=IF(COUNT(N3:O3)<>2,"",SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(N3&":"&O3)),2)=MATCH(G3:I3,{"M","T","W","TH","F","SA","SU",0},0))))

Biff

"girlfriend in school" <girlfriendinschool@discussions.microsoft.com> wrote
in message news:226F2179-C5DF-4133-B379-DE3653DF6D74@microsoft.com...
> This is great, but it doesn't factor the variability of the days tried to
> explain in my initial query. Data in columns G, H, and I will be days of
> week, but will change from row to row (as will the respective dates in
> columns N and O). Sometimes there will be no data in H and/or I. I am
> hoping to create something where a clerk can put in the days of the week
> and
> the spreadsheet formula will count up the days. Note: the number of days
> will then be used in another formula to calculated $$.
>
> Perhaps if I show you what I have so far will help:
>
> G3 H3 I3 N3 O3 Q3
> M F (blank) 10/9/05 12/15/05 (answer)
>
> I tried Bernie's formula but that didn't work. I had to change cell
> references, and I don't understand the formula (and cannot decipher Excel
> Help's explanation) so that might be why.
> I am currently using IF formulae to reference columns G, H and I into the
> weekday_number, but think I have exceeded the quantity of formulae one can
> put in a single cell.
>
> If you have an answer, WHAT A RELIEF!!!
>
> THX.
>
>
> "Biff" wrote:
>
>> Hi!
>>
>> After seeing Bernie's post I'm wondering if I understand the question!
>>
>> To count specific weekdays between 2 dates (inclusive):
>>
>> Where Monday = weekday 1 and Sunday = weekday 7
>>
>> Start date in A1
>> End date in B1
>>
>> =SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&B1)),2)=weekday_number))
>>
>> To count more than 1 weekday like Mondays and Tuesdays:
>>
>> =SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&B1)),2)={1,2}))
>>
>> Biff
>>
>> "girlfriend in school" <girlfriendinschool@discussions.microsoft.com>
>> wrote
>> in message news:C5271A39-23D4-46D5-B3B4-72BD40B4F9E2@microsoft.com...
>> >I need to count the number of days between a start date and end date and
>> >the
>> > questions about this I found here don't seem to help (boggle me!) I
>> > need
>> > a
>> > universal formula that will cover variable days which are listed in a
>> > separate column:
>> > G (rows) = names of days; J (rows) = start date; K (rows) = end date.
>> > On
>> > top of this, I am sometimes having to count more than one day (up to
>> > three)
>> > within a week (e.g., Mondays AND Fridays) which I'd be willing to put
>> > in
>> > separate columns (i.e., columns G,H,I) if needed, but if only one
>> > column
>> > has
>> > a day listed, the formula needs to ignore the empty columns.
>> > A knotty problem?
>>
>>
>>

This is great, but it doesn’t factor the variability of the days tried to
explain in my initial query. Data in columns G, H, and I will be days of
week, but will change from row to row (as will the respective dates in
columns N and O). Sometimes there will be no data in H and/or I. I am
hoping to create something where a clerk can put in the days of the week and
the spreadsheet formula will count up the days. Note: the number of days
will then be used in another formula to calculated $$.

Perhaps if I show you what I have so far will help:

G3 H3 I3 N3 O3 Q3
M F (blank) 10/9/05 12/15/05 (answer)

I tried Bernie’s formula but that didn’t work. I had to change cell
references, and I don’t understand the formula (and cannot decipher Excel
Help’s explanation) so that might be why.
I am currently using IF formulae to reference columns G, H and I into the
weekday_number, but think I have exceeded the quantity of formulae one can
put in a single cell.

If you have an answer, WHAT A RELIEF!!!

THX.


"Biff" wrote:

> Hi!
>
> After seeing Bernie's post I'm wondering if I understand the question!
>
> To count specific weekdays between 2 dates (inclusive):
>
> Where Monday = weekday 1 and Sunday = weekday 7
>
> Start date in A1
> End date in B1
>
> =SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&B1)),2)=weekday_number))
>
> To count more than 1 weekday like Mondays and Tuesdays:
>
> =SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&B1)),2)={1,2}))
>
> Biff
>
> "girlfriend in school" <girlfriendinschool@discussions.microsoft.com> wrote
> in message news:C5271A39-23D4-46D5-B3B4-72BD40B4F9E2@microsoft.com...
> >I need to count the number of days between a start date and end date and
> >the
> > questions about this I found here don't seem to help (boggle me!) I need
> > a
> > universal formula that will cover variable days which are listed in a
> > separate column:
> > G (rows) = names of days; J (rows) = start date; K (rows) = end date. On
> > top of this, I am sometimes having to count more than one day (up to
> > three)
> > within a week (e.g., Mondays AND Fridays) which I'd be willing to put in
> > separate columns (i.e., columns G,H,I) if needed, but if only one column
> > has
> > a day listed, the formula needs to ignore the empty columns.
> > A knotty problem?
>
>
>

Hi!

After seeing Bernie's post I'm wondering if I understand the question!

To count specific weekdays between 2 dates (inclusive):

Where Monday = weekday 1 and Sunday = weekday 7

Start date in A1
End date in B1

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&B1)),2)=weekday_number))

To count more than 1 weekday like Mondays and Tuesdays:

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&B1)),2)={1,2}))

Biff

"girlfriend in school" <girlfriendinschool@discussions.microsoft.com> wrote
in message news:C5271A39-23D4-46D5-B3B4-72BD40B4F9E2@microsoft.com...
>I need to count the number of days between a start date and end date and
>the
> questions about this I found here don't seem to help (boggle me!) I need
> a
> universal formula that will cover variable days which are listed in a
> separate column:
> G (rows) = names of days; J (rows) = start date; K (rows) = end date. On
> top of this, I am sometimes having to count more than one day (up to
> three)
> within a week (e.g., Mondays AND Fridays) which I'd be willing to put in
> separate columns (i.e., columns G,H,I) if needed, but if only one column
> has
> a day listed, the formula needs to ignore the empty columns.
> A knotty problem?

Biff,

Neat formula! - converting the dates to row numbers was very insightful.

Bernie

> After seeing Bernie's post I'm wondering if I understand the question!