Number of Days in First, Mid, Last Quarter of Contract

[Dmat]

So I'm running into a snag and need some help.

I'm trying to write a dynamic formula that gives me the number of days in my contract every quarter. I'm trying to formulate my support documentation to match IT logic.

My known, I have a start date, end data, and current quarter/year that i'm in.

I can figure out the first quarter, mid term, and last quarter by the contract dates but manually input the end date of the first quarter or beginning date of the last quarter. Can that be done automatically?

I need to manually spread the mid term amount for each quarter in between but need to ignore that spread for the first and last quarter. The mid term spread will be a decimal between 90-92 days depending on the quarter.

The formula I'm looking for:
Row 2, I would take my contract value/29 days (first quarter of deal)
Row 3, I would take my contract value/91.45 days (mid term)
Row 4, I would take my contract value/89 days (last quarter of deal)

I know this is a unique ask but it's what I'm working with. I appreciate any help I can get. Thanks in advance.

 

[Eric W]

Here's what I came up with, but I have a few questions:

Book1
	A	B	C	D	E	F
1	Current Quarter	Contract Start Date	Contract End Date	First Quarter	Mid Term	Last Quarter
2	Q3 2021	9/1/2021	8/30/2026	29	1735	60
3	Q3 2021	6/1/2021	5/30/2024	29	1006	59
4	Q3 2021	9/29/2020	9/28/2021	1	274	89
Sheet2

Cell Formulas
Range	Formula
D2:D4	D2	=EOMONTH(B2,2-MOD(MONTH(B2)-1,3))-B2
E2:E4	E2	=C2-B2-D2-F2
F2:F4	F2	=C2-EOMONTH(C2,-MOD(MONTH(C2)-1,3)-1)-1
A2:A4	A2	="Q"&INT((MONTH(TODAY())-1)/3)+1&" "&YEAR(TODAY())

For the D column, shouldn't it be 30? 9/1-9/30? Same type of question for column F?

 

[Dmat]

For the D column, shouldn't it be 30? 9/1-9/30? Same type of question for column F?

Yes. It should be 30 to account for the full month.

 

[Eric W]

In that case, try:

Book1
	A	B	C	D	E	F
1	Current Quarter	Contract Start Date	Contract End Date	First Quarter	Mid Term	Last Quarter
2	Q3 2021	9/1/2021	8/30/2026	30	1733	61
3	Q3 2021	6/1/2021	5/30/2024	30	1004	60
4	Q3 2021	9/29/2020	9/28/2021	2	272	90
Sheet2

Cell Formulas
Range	Formula
D2:D4	D2	=EOMONTH(B2,2-MOD(MONTH(B2)-1,3))-B2+1
E2:E4	E2	=C2-B2-D2-F2
F2:F4	F2	=C2-EOMONTH(C2,-MOD(MONTH(C2)-1,3)-1)
A2:A4	A2	="Q"&MROUND(MONTH(TODAY()),3)/3&" "&YEAR(TODAY())

 

[James Clear]

Yes. It should be 30 to account for the full month.

Another way

D2 =DATE(YEAR(B2)+INT(MONTH(B2)/10),INDEX({4,4,4,7,7,7,10,10,10,1,1,1},MONTH(B2)),1)-B2