How to determine the what quarters are between 2 dates

[mjw0823]

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

 

[Don Guillett]

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))

 

[T. Valko]

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

 

[T. Valko]

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

 

[mjw0823]

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]

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

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

Biff