Calculating number of days per quarter for two dates

Question

So, I have a project where if given two dates I need to calculate the number of days between them that is in each quarter. In theory I could use NETWORKDAYS, but I'm having difficulty because many times the duration between the two dates is 1+ years.

It will be easier to show visually. I need the format to look like this:

Anyone have thoughts on how to achieve this?

Answer 1

OK, helper rows that contain the start and end dates of each quarter would make this a lot easier but here is a formula only solution.

UPDATED

I realized I made this too complicated. Here is the streamlined version.

In cell J2:

=MAX(MIN(VALUE(CHOOSE(MID(J1,2,1),"3/31/","6/30/","9/30/","12/31/") & RIGHT(J$1,4)),$H2)-MAX(VALUE(CHOOSE(MID(J1,2,1),"1/1/","4/1/","7/1/","10/1/") & RIGHT(J$1,4)),$G2) + 1,0)

Explanation:

VALUE(CHOOSE(MID(J1,2,1),"3/31/","6/30/","9/30/","12/31/") & RIGHT(J$1,4)

This gets the number of the quarter, MID(J1,2,1), and uses that to choose the end date for that quarter. This is then concatenated with the year, RIGHT(J$1,4). The resulting string is then converted to a number value.

This same approach is used to get the start date, with the appropriate quarter start dates instead.

Note: If you have different quarters defined, such as Fiscal Years, then change to match.

Resulting simplified formula with descriptive names instead of cell references:

=MAX(MIN(QtrEnd, EndDate) - MAX(QtrStart, StartDate) + 1, 0)

The MIN(QtrEnd, EndDate) - MAX(QtrStart, StartDate) subtracts the latest (largest) start date from the earliest (smallest) end date and we add 1 to get the count of days instead of the difference.

The Max(CountOfDays, 0) traps any negative values, returning 0 instead.

Note: I did not get the same values that you have in your graphic. I don't think your values are correct. Unless you have a different start date for Quarter 2 (I'm using 4/1), there is no way you could have 45 days in Q2 2018.

Here are the values I get: