Number of days per quarter between 2 dates

[dunDaan]

Can someone help me to calculate the number of days per quarter between 2 dates?

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Start date[/TD]
[TD]End date[/TD]
[TD]Q1[/TD]
[TD]Q2[/TD]
[TD]Q3[/TD]
[TD]Q4[/TD]
[/TR]
[TR]
[TD]01 jan 2019[/TD]
[TD]30 jun 2019[/TD]
[TD]89[/TD]
[TD]91[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]01 feb 2019[/TD]
[TD]01 mar 2019[/TD]
[TD]28[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]01 feb 2019[/TD]
[TD]01 aug 2019[/TD]
[TD]58[/TD]
[TD]91[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]01 nov 2018[/TD]
[TD]01 aug 2019[/TD]
[TD]150[/TD]
[TD]-150[/TD]
[TD]0[/TD]
[TD]60[/TD]
[/TR]
[TR]
[TD]01 jul 2019[/TD]
[TD]31 dec 2019[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]91[/TD]
[TD]92[/TD]
[/TR]
</tbody>[/TABLE]

I'm currently using following formula's:

For Q1: =MIN(MAX(DATE(YEAR(A2),4,0)-A2,0),B2-A2)
For Q2: =MIN(MAX(DATE(YEAR(A2),7,0)-A2,0),B2-A2)-C2
For Q3: =MIN(MAX(DATE(YEAR(A2),10,0)-A2,0),B2-A2)-SUM(C2:D2)
For Q4: =MIN(MAX(DATE(YEAR(A2),13,0)-A2,0),B2-A2)-SUM(C2:E2)

This returns the values as seen in table above so it works quite well, however as you can see this is not working OK when the dates are not within the same year. (see 5th row)

Any suggestions to make this work for all possible dates? It is expected that end date > start date, so no need to account for end date < start date

Thanks in advance.

/dunDaan

 

[FormR]

however as you can see this is not working OK when the dates are not within the same year. (see 5th row)

Hi, welcome to the forum!

You didn't tell us what results you expect for the 5th row - but assuming you want to disregard the year and apportion all of the days into their respective quarter here is an option you can try (formula in C2 copied down and across).

Excel 2013/2016
	A	B	C	D	E	F
1	Start date	End date	Q1	Q2	Q3	Q4
2	01/01/2019	30/06/2019	89	91	0	0
3	01/02/2019	01/03/2019	28	0	0	0
4	01/02/2019	01/08/2019	58	91	32	0
5	01/11/2018	01/08/2019	90	91	32	60
6	01/07/2019	31/12/2019	0	0	91	92
7	01/01/2018	31/12/2019	179	182	184	184
Sheet1

Cell Formulas
Range	Formula
C2	=SUMPRODUCT(0+("Q"&ROUNDUP(MONTH(ROW(INDIRECT($A2+1&":"&$B2)))/3,0)=C$1))

 

[dunDaan]

Thanks for the fast response FormR.

Indeed I wasn't completely clear in what the intended values should be.
The intention is that the cells in columns C through F show how many days each quarter has, based on the start date and end date indicated in columns A end B for a specific year (in this case 2019).

Knowing that the interval between start date and end date will not exceed 1 year, your solution works perfectly, thanks a lot!!
I will now dig in trying to understand what the formula actually does

 

[cnestg8r]

FormR, very elegant! The approach wasn't obvious, so it was a learning experience. Thanks

 

[FormR]

for a specific year

Hi, in that case, this approach might be better (more efficient). In this example the specific year is stored in cell C1.

Excel 2013/2016
	A	B	C	D	E	F
1			2019
2
3	Start date	End date	Q1	Q2	Q3	Q4
4	01/01/2019	30/06/2019	89	91	0	0
5	01/02/2019	01/03/2019	28	0	0	0
6	01/02/2019	01/08/2019	58	91	32	0
7	01/11/2018	01/08/2019	90	91	32	0
8	01/07/2019	31/12/2019	0	0	91	92
9	20/12/2018	30/04/2020	90	91	92	92
Sheet1

Cell Formulas
Range	Formula
C4	=MAX(0,MIN($B4,DATE($C$1,1+(COLUMNS($C4:C4)*3),0))-MAX($A4,DATE($C$1,1+((COLUMNS($C4:C4)-1)*3),0)))

 

[steve the fish]

Shouldnt the 89 in yellow read 90? Jan 31 days, Feb 28, March 31.

 

[FormR]

Shouldnt the 89 in yellow read 90? Jan 31 days, Feb 28, March 31.

Hi Steve - from the example data in post#1, I took it that the start date should not be included. Maybe the OP will clarify.

 

[dunDaan]

Technically you're right, but for my purpose it doesn't really matter whether or not the first day is included.