Hi,

I need to determine how many days fall between a start date and an end
date. I am actually able to find that information, but how do I take
that total number of days and determine how many days (of the total)
fall within Q1, how many fall within Q2, how many fall within Q3, etc.


For example if the total number of days between 2 dates is 243, then 66
working days fall in Q1, 66 days in Q2, 66 days in Q3 and 45 days in
Q4. I need a function that can capture this information.

Please advise.

Thanks,

MJ

A bit unclear but perhaps sumproduct can help

=sumproduct((a2:a200>startdate in cell b1)*(a2:a200<enddate))
=sumproduct((a2:a200>=b1)*(a2:a200<b2))

--
Don Guillett
SalesAid Software
(E-Mail Removed)
"mjw0823" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Hi,
>
> I need to determine how many days fall between a start date and an end
> date. I am actually able to find that information, but how do I take
> that total number of days and determine how many days (of the total)
> fall within Q1, how many fall within Q2, how many fall within Q3, etc.
>
>
> For example if the total number of days between 2 dates is 243, then 66
> working days fall in Q1, 66 days in Q2, 66 days in Q3 and 45 days in
> Q4. I need a function that can capture this information.
>
> Please advise.
>
> Thanks,
>
> MJ
>

Based on a calendar year:

A1 = start date
B1 = end date

D14 = Q1,Q2, Q3, Q4

Enther this formula in E1 and copy down to E4:

=SUMPRODUCT(--(CEILING(MONTH(ROW(INDIRECT(A$1&":"&B$1)))/3,1)=ROWS($1:1)))

Biff

"mjw0823" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Hi,
>
> I need to determine how many days fall between a start date and an end
> date. I am actually able to find that information, but how do I take
> that total number of days and determine how many days (of the total)
> fall within Q1, how many fall within Q2, how many fall within Q3, etc.
>
>
> For example if the total number of days between 2 dates is 243, then 66
> working days fall in Q1, 66 days in Q2, 66 days in Q3 and 45 days in
> Q4. I need a function that can capture this information.
>
> Please advise.
>
> Thanks,
>
> MJ
>

Hmmm....

Well, I just read your post again. In the first paragraph you want "total
days". In the second paragraph you want "working days".

The formula I posted will find "total days".

If you do want working days, what are the work days? Monday thru Friday? If
that's the case:

=SUMPRODUCT(--(CEILING(MONTH(ROW(INDIRECT(A$1&":"&B$1)))/3,1)=ROWS($1:1)),--(WEEKDAY(ROW(INDIRECT(A$1&":"&B$1)),2)<6))

And what about holidays?

Yoi! I gotta start choosing more carefully what posts I reply to! <BG>

Biff

"T. Valko" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Based on a calendar year:
>
> A1 = start date
> B1 = end date
>
> D14 = Q1,Q2, Q3, Q4
>
> Enther this formula in E1 and copy down to E4:
>
> =SUMPRODUCT(--(CEILING(MONTH(ROW(INDIRECT(A$1&":"&B$1)))/3,1)=ROWS($1:1)))
>
> Biff
>
> "mjw0823" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> Hi,
>>
>> I need to determine how many days fall between a start date and an end
>> date. I am actually able to find that information, but how do I take
>> that total number of days and determine how many days (of the total)
>> fall within Q1, how many fall within Q2, how many fall within Q3, etc.
>>
>>
>> For example if the total number of days between 2 dates is 243, then 66
>> working days fall in Q1, 66 days in Q2, 66 days in Q3 and 45 days in
>> Q4. I need a function that can capture this information.
>>
>> Please advise.
>>
>> Thanks,
>>
>> MJ
>>
>
>

Hi Biff,

Thanks...this does work!!! The only problem is (and I know this might
sound dumb) I do not want to drag the formula down a column, but across
a row. How would I rewrite the function to display that?

Thanks,

MJ


T. Valko wrote:
> Hmmm....
>
> Well, I just read your post again. In the first paragraph you want "total
> days". In the second paragraph you want "working days".
>
> The formula I posted will find "total days".
>
> If you do want working days, what are the work days? Monday thru Friday? If
> that's the case:
>
> =SUMPRODUCT(--(CEILING(MONTH(ROW(INDIRECT(A$1&":"&B$1)))/3,1)=ROWS($1:1)),--(WEEKDAY(ROW(INDIRECT(A$1&":"&B$1)),2)<6))
>
> And what about holidays?
>
> Yoi! I gotta start choosing more carefully what posts I reply to! <BG>
>
> Biff
>
> "T. Valko" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > Based on a calendar year:
> >
> > A1 = start date
> > B1 = end date
> >
> > D14 = Q1,Q2, Q3, Q4
> >
> > Enther this formula in E1 and copy down to E4:
> >
> > =SUMPRODUCT(--(CEILING(MONTH(ROW(INDIRECT(A$1&":"&B$1)))/3,1)=ROWS($1:1)))
> >
> > Biff
> >
> > "mjw0823" <(E-Mail Removed)> wrote in message
> > news:(E-Mail Removed)...
> >> Hi,
> >>
> >> I need to determine how many days fall between a start date and an end
> >> date. I am actually able to find that information, but how do I take
> >> that total number of days and determine how many days (of the total)
> >> fall within Q1, how many fall within Q2, how many fall within Q3, etc.
> >>
> >>
> >> For example if the total number of days between 2 dates is 243, then 66
> >> working days fall in Q1, 66 days in Q2, 66 days in Q3 and 45 days in
> >> Q4. I need a function that can capture this information.
> >>
> >> Please advise.
> >>
> >> Thanks,
> >>
> >> MJ
> >>
> >
> >

Change the relative references from A$1:B$1 to $A1:$B1.....

And change ROWS($1:1) to COLUMNS($A:A)

Biff

"mjw0823" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Hi Biff,
>
> Thanks...this does work!!! The only problem is (and I know this might
> sound dumb) I do not want to drag the formula down a column, but across
> a row. How would I rewrite the function to display that?
>
> Thanks,
>
> MJ
>
>
> T. Valko wrote:
>> Hmmm....
>>
>> Well, I just read your post again. In the first paragraph you want "total
>> days". In the second paragraph you want "working days".
>>
>> The formula I posted will find "total days".
>>
>> If you do want working days, what are the work days? Monday thru Friday?
>> If
>> that's the case:
>>
>> =SUMPRODUCT(--(CEILING(MONTH(ROW(INDIRECT(A$1&":"&B$1)))/3,1)=ROWS($1:1)),--(WEEKDAY(ROW(INDIRECT(A$1&":"&B$1)),2)<6))
>>
>> And what about holidays?
>>
>> Yoi! I gotta start choosing more carefully what posts I reply to! <BG>
>>
>> Biff
>>
>> "T. Valko" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>> > Based on a calendar year:
>> >
>> > A1 = start date
>> > B1 = end date
>> >
>> > D14 = Q1,Q2, Q3, Q4
>> >
>> > Enther this formula in E1 and copy down to E4:
>> >
>> > =SUMPRODUCT(--(CEILING(MONTH(ROW(INDIRECT(A$1&":"&B$1)))/3,1)=ROWS($1:1)))
>> >
>> > Biff
>> >
>> > "mjw0823" <(E-Mail Removed)> wrote in message
>> > news:(E-Mail Removed)...
>> >> Hi,
>> >>
>> >> I need to determine how many days fall between a start date and an end
>> >> date. I am actually able to find that information, but how do I take
>> >> that total number of days and determine how many days (of the total)
>> >> fall within Q1, how many fall within Q2, how many fall within Q3, etc.
>> >>
>> >>
>> >> For example if the total number of days between 2 dates is 243, then
>> >> 66
>> >> working days fall in Q1, 66 days in Q2, 66 days in Q3 and 45 days in
>> >> Q4. I need a function that can capture this information.
>> >>
>> >> Please advise.
>> >>
>> >> Thanks,
>> >>
>> >> MJ
>> >>
>> >
>> >
>