Calculate first and last day of a certain quarter

[TruffleOil]

Hi everyone

I'm trying to calculate the first and last day of a specific quarter (Q3 2022). In the picture attached:
-I have a fixed delivery date (16 Oct 2022)
-I managed to calculate delivery quarter (Q4 2022): ="Q" &INT((MONTH(B3)+2)/3) & "-" & YEAR(B3)
-I also need the quarter prior to delivery (Q3 2022): ="Q" &INT((MONTH(EDATE(B3,-1))+2)/3) & "-" & YEAR(EDATE(B3,-1))

But in the end what I really need is to be able to show the first and last day of the quarter prior to delivery, so for Q3 2022, it would be 1 July 2022 and 30 Sept 2022.
Can anyone please help with a formula for that? I don't want to subtract 90 days from the first day of delivery quarter because it doesn't always show accurate dates (in this case if I do 1st Oct-90 days = 3 July).
Thank you!

 

[GraH]

A lookup and a math way (How to calculate quarter start date or end date based on a given date in Excel?).

Book1
	B	C	D
2	Example
3	Delivery Date	16/10/2022
4	Delivery Quarter	Q4-2022
5	Quarter Prior to Delivery	Q3-2022
6	What I need:	1/07/2022	30/09/2022
7		1/07/2022	30/09/2022
8		1/07/2022	30/09/2022
Sheet1

Cell Formulas
Range	Formula
C7:C8	C7	=EOMONTH(D7,-3)+1
D7	D7	=SWITCH(CEILING(MONTH(C3)/3,1),1,DATE(YEAR(C3)-1,12,31),2,DATE(YEAR(C3),3,31),3,DATE(YEAR(C3),6,30),4,DATE(YEAR(C3),9,30))
D8	D8	=EOMONTH(DATE(YEAR(C3),((INT((MONTH(C3)-1)/3)+1)*3)+1,1)-1,-3)

 

[TruffleOil]

A lookup and a math way (How to calculate quarter start date or end date based on a given date in Excel?).

Book1
	B	C	D
2	Example
3	Delivery Date	16/10/2022
4	Delivery Quarter	Q4-2022
5	Quarter Prior to Delivery	Q3-2022
6	What I need:	1/07/2022	30/09/2022
7		1/07/2022	30/09/2022
8		1/07/2022	30/09/2022
Sheet1

Cell Formulas
Range	Formula
C7:C8	C7	=EOMONTH(D7,-3)+1
D7	D7	=SWITCH(CEILING(MONTH(C3)/3,1),1,DATE(YEAR(C3)-1,12,31),2,DATE(YEAR(C3),3,31),3,DATE(YEAR(C3),6,30),4,DATE(YEAR(C3),9,30))
D8	D8	=EOMONTH(DATE(YEAR(C3),((INT((MONTH(C3)-1)/3)+1)*3)+1,1)-1,-3)

bless, it worked!! thank you!

 

[Dave Patton]

another alternative
I used XLookup; you can try Vlookup.

T202211a.xlsm
	A	B	C	D	E	F	G	H
1								info
2		Example				1-Jan-22	31-Mar-22	Q1 2022
3		Delivery Date	16-Oct-22			1-Apr-22	30-Jun-22	Q2 2022
4		Delivery Quarter	Q4 2022			1-Jul-22	30-Sep-22	Q3 2022
5						1-Oct-22	31-Dec-22	Q4 2022
6		Previous quarter	1-Jul-22	30-Sep-22		1-Jan-23	31-Mar-23	Q1 2023
7		or	1-Jul-22	30-Sep-22		1-Apr-23	30-Jun-23	Q2 2023
8						1-Jul-23	30-Sep-23	Q3 2023
9						1-Oct-23	31-Dec-23	Q4 2023
4c

Cell Formulas
Range	Formula
C4	C4	=XLOOKUP(C3,F2:F9,H2:H9,,-1)
C6	C6	=XLOOKUP(EDATE(C3,-3),F2:F9,F2:F9,,-1)
D6	D6	=XLOOKUP(EDATE(C3,-3),F2:F9,G2:G9,,-1)
C7	C7	=XLOOKUP(EDATE(C3,-3),{44562;44652;44743;44835;44927;45017;45108;45200},{44562;44652;44743;44835;44927;45017;45108;45200},,-1)
D7	D7	=XLOOKUP(EDATE(C3,-3),{44562;44652;44743;44835;44927;45017;45108;45200},{44651;44742;44834;44926;45016;45107;45199;45291},,-1)

 

[Dave Patton]

T202211a.xlsm
	C	D
10	1-Jul-22	30-Sep-22
4c

Cell Formulas
Range	Formula
C10	C10	=VLOOKUP(EDATE(C3,-3),F2:H9,1)
D10	D10	=XLOOKUP(EDATE(C3,-3),F2:F9,G2:G9,,-1)

 

[TruffleOil]

T202211a.xlsm
	C	D
10	1-Jul-22	30-Sep-22
4c

Cell Formulas
Range	Formula
C10	C10	=VLOOKUP(EDATE(C3,-3),F2:H9,1)
D10	D10	=XLOOKUP(EDATE(C3,-3),F2:F9,G2:G9,,-1)

this also worked, thank you so much!!

 

[Dave Patton]

Thanks for the feedback