# Calculate first and last day of a certain quarter

#### TruffleOil

##### New Member
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!

#### Attachments

• excel question 3.PNG
16.4 KB · Views: 4

### Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

#### GraH

##### Well-known Member
A lookup and a math way (How to calculate quarter start date or end date based on a given date in Excel?).
Book1
BCD
2Example
3Delivery Date16/10/2022
4Delivery QuarterQ4-2022
5Quarter Prior to DeliveryQ3-2022
6What I need:1/07/202230/09/2022
71/07/202230/09/2022
81/07/202230/09/2022
Sheet1
Cell Formulas
RangeFormula
C7:C8C7=EOMONTH(D7,-3)+1
D7D7=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))
D8D8=EOMONTH(DATE(YEAR(C3),((INT((MONTH(C3)-1)/3)+1)*3)+1,1)-1,-3)

#### TruffleOil

##### New Member
A lookup and a math way (How to calculate quarter start date or end date based on a given date in Excel?).
Book1
BCD
2Example
3Delivery Date16/10/2022
4Delivery QuarterQ4-2022
5Quarter Prior to DeliveryQ3-2022
6What I need:1/07/202230/09/2022
71/07/202230/09/2022
81/07/202230/09/2022
Sheet1
Cell Formulas
RangeFormula
C7:C8C7=EOMONTH(D7,-3)+1
D7D7=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))
D8D8=EOMONTH(DATE(YEAR(C3),((INT((MONTH(C3)-1)/3)+1)*3)+1,1)-1,-3)
bless, it worked!! thank you!

#### Dave Patton

##### Well-known Member
another alternative
I used XLookup; you can try Vlookup.

T202211a.xlsm
ABCDEFGH
1info
2Example1-Jan-2231-Mar-22Q1 2022
3Delivery Date16-Oct-221-Apr-2230-Jun-22Q2 2022
4Delivery QuarterQ4 20221-Jul-2230-Sep-22Q3 2022
51-Oct-2231-Dec-22Q4 2022
6Previous quarter1-Jul-2230-Sep-221-Jan-2331-Mar-23Q1 2023
7or1-Jul-2230-Sep-221-Apr-2330-Jun-23Q2 2023
81-Jul-2330-Sep-23Q3 2023
91-Oct-2331-Dec-23Q4 2023
4c
Cell Formulas
RangeFormula
C4C4=XLOOKUP(C3,F2:F9,H2:H9,,-1)
C6C6=XLOOKUP(EDATE(C3,-3),F2:F9,F2:F9,,-1)
D6D6=XLOOKUP(EDATE(C3,-3),F2:F9,G2:G9,,-1)
C7C7=XLOOKUP(EDATE(C3,-3),{44562;44652;44743;44835;44927;45017;45108;45200},{44562;44652;44743;44835;44927;45017;45108;45200},,-1)
D7D7=XLOOKUP(EDATE(C3,-3),{44562;44652;44743;44835;44927;45017;45108;45200},{44651;44742;44834;44926;45016;45107;45199;45291},,-1)

#### Dave Patton

##### Well-known Member
T202211a.xlsm
CD
101-Jul-2230-Sep-22
4c
Cell Formulas
RangeFormula
C10C10=VLOOKUP(EDATE(C3,-3),F2:H9,1)
D10D10=XLOOKUP(EDATE(C3,-3),F2:F9,G2:G9,,-1)

#### TruffleOil

##### New Member
T202211a.xlsm
CD
101-Jul-2230-Sep-22
4c
Cell Formulas
RangeFormula
C10C10=VLOOKUP(EDATE(C3,-3),F2:H9,1)
D10D10=XLOOKUP(EDATE(C3,-3),F2:F9,G2:G9,,-1)
this also worked, thank you so much!!

#### Dave Patton

##### Well-known Member
Thanks for the feedback

Replies
2
Views
100
Replies
7
Views
173
Replies
5
Views
156
Replies
0
Views
192
Replies
3
Views
266

1,187,202
Messages
5,962,183
Members
438,592
Latest member
vincethesun

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back